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).

---

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.

---

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.

---

]]>