Need a Stored procedure

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Need a Stored procedure

Need a Stored procedure

Rate Topic

Display Mode

Topic Options

Author

Message

oradbguru

Posted Monday, January 14, 2013 1:35 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:18 AM
Points: 63, Visits: 160

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

SGT_squeequal

Posted Monday, January 14, 2013 1:48 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, March 30, 2013 9:39 AM
Points: 261, Visits: 966

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 w00t

Post #1406934

oradbguru

Posted Monday, January 14, 2013 1:50 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, May 16, 2013 11:18 AM
Points: 63, Visits: 160

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

Sean Lange

Posted Monday, January 14, 2013 2:12 PM

SSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 1:17 PM
Points: 8,641, Visits: 8,273

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

Post #1406943

SGT_squeequal

Posted Monday, January 14, 2013 2:15 PM

SSC Veteran

Group: General Forum Members
Last Login: Saturday, March 30, 2013 9:39 AM
Points: 261, Visits: 966

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 w00t

Post #1406944

opc.three

Posted Monday, January 14, 2013 3:53 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1406971

dwain.c

Posted Monday, January 21, 2013 6:02 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1409758

opc.three

Posted Wednesday, January 23, 2013 8:07 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 8:26 AM
Points: 6,737, Visits: 11,791

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

Believe you can and you're halfway there. --Theodore Roosevelt

Everything Should Be Made as Simple as Possible, But Not Simpler --Albert Einstein

The significant problems we face cannot be solved at the same level of thinking we were at when we created them. --Albert Einstein

1 apple is not exactly 1/8 of 8 apples. Because there are no absolutely identical apples. --Giordy

Post #1410607

« Prev Topic | Next Topic »

Permissions