SQL Server Performance

Response time of remote servers

Discussion in 'SQL Server DTS-Related Questions' started by nareshb, Mar 26, 2007.

  1. nareshb New Member

    I want to check the average response time of the remote servers I am using in DTS packages, which are scheduled. If this time is more than 100 ms, I dont want to execute my DTS packages at that time.

    Can someone please suggest a method ( SQl OR EXTENDED stored procedures) to find the average response time of remote servers programatically. ( I am manually checking it by executing 'ping server_name' command in cmd prompt).
  2. ranjitjain New Member

    Hi,
    Consider this:

    declare @t1 table(col1 varchar(1000))
    insert into @t1
    exec xp[_]cmdshell 'ping 193.37.173.186'

    select * from @t1

    After this you may have to look for specific rows containing text average=0ms.
    You also need to tackle situations where server is not available.
  3. manojvjti New Member

    I get an error after this -

    Server: Msg 197, Level 15, State 1, Line 5
    EXECUTE cannot be used as a source when inserting into a table variable.

    I was able to make use of the code by changing to temporary table over table variable.
  4. Adriaan New Member

    Yes, known limitation in SQL 2000 - with INSERT INTO ... EXEC ... you cannot use a table variable as the target. I think that limitation is gone in SQL 2005.

Share This Page