January 14, 2013 at 1:35 pm
Hello, I need help writing a stored procedure that will take a parameter in as @hostName VARCHAR(100) and using xp_cmdshell, ping the server specified as @hostName. Any examples would be greatly appreciated.
January 14, 2013 at 1:48 pm
do you really need to do this with SQL? what is the purpose of the ping? are you just checking to see if a server is pingable? perhaps a vbs script would be better?
***The first step is always the hardest *******
January 14, 2013 at 1:50 pm
We want to do it in sql and store the average of each ping in a table along with the host name and times tamp.
January 14, 2013 at 2:12 pm
Storing the results in sql is fine but the process you are describing is an application. Yes you can probably find a way to make this work but that doesn't mean it is a good idea. To paraphrase Joe Celko, "don't drive a screw in a piece of fine furniture by pounding it in with a rock".
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 14, 2013 at 2:15 pm
i agree with Sean, however, this bit of SQL will do what you ask the reults are not that cracking though.
create proc ping_test (@host varchar(100))
as begin
declare
@exec varchar(1000)
set @exec='EXECUTE master..xp_cmdshell '+''''+'ping '+@host+''''
exec (@exec)
end
***The first step is always the hardest *******
January 14, 2013 at 3:53 pm
I do not condone using xp_cmdshell at all, and especially not for something like this, but if you must here is someone who has detailed the technique from beginning to end:
http://www.sqlmag.com/content1/topic/ssis-package-pings-servers-143994/catpath/sql-server
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2013 at 6:02 pm
Another option is the paid version of the SQL# CLR library by Solomon Rutzky: http://www.sqlsharp.com/
There are two functions in it: INET_Ping and INET_PingTime that may be of interest to you.
As I said though, they're not available in the free version of the library (unfortunately).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2013 at 8:08 am
If I had to pick a poison I would implement a SQLCLR method (of my own) before I would use xp_cmdshell for this. That said, at the end of the day none of it belongs in T-SQL.
Rolling your own SQLCLR objects to confirm a ping was possible or get the ping round trip time would be trivial. The SQL# methods are thin wrappers over methods on the .NET Ping class built into .NET 2.0.
Ping.Send Method (String, Int32, Byte[], PingOptions)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply