Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need a Stored procedure Expand / Collapse
Author
Message
Posted Monday, January 14, 2013 1:35 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
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.
Post #1406932
Posted Monday, January 14, 2013 1:48 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
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 is always the hardest
Post #1406934
Posted Monday, January 14, 2013 1:50 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Sunday, September 7, 2014 6:49 AM
Points: 79, Visits: 217
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.
Post #1406936
Posted Monday, January 14, 2013 2:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1406943
Posted Monday, January 14, 2013 2:15 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Monday, September 8, 2014 2:40 PM
Points: 271, Visits: 1,020
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 is always the hardest
Post #1406944
Posted Monday, January 14, 2013 3:53 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 7,126, Visits: 12,727
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
Post #1406971
Posted Monday, January 21, 2013 6:02 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 10:53 PM
Points: 3,421, Visits: 5,359
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 mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1409758
Posted Wednesday, January 23, 2013 8:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 7,126, Visits: 12,727
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
Post #1410607
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse