Response time of remote servers | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Response time of remote servers

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).
Consider this: declare @t1 table(col1 varchar(1000))
insert into @t1
exec xp[_]cmdshell ‘ping’ 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.
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.
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.