MS SQL ping external server

Question

Is there a 'nice' way to check whether an network host is up making use of 'palin' SQL? I thought of some MS-SQL specific command or maybe some getting the return value from a ping script on the server itself.

Does anyone has an idea on this?

Answer 1

I'll assume you mean "plain" SQL and not "palin" :-)

If you're trying to see if SQL is running on a host you could try and connect to the SQL Browser Service by using the SSRP (SQL Server Resolution Protocol) (but this needs to be installed and running on the server).

You could try a "best guess" approach and make a TCP connection to port 1433 (the default port SQL Server's Database Engine). If the port is open, it might be that a SQL Server is listening - you could then try and connect to SQL Server and execute a command (like SELECT @@VERSION or something specific to your application). The problems with this approach:

• SQL Server Database Engine can be configured to use a non-standard TCP port.
• SQL Server might not even be using TCP - you'd have to connect using named pipes (I'm not sure how that's done).
• Some other service could be configured to use TCP port 1433. It doesn't mean that SQL Server is listening.
• If port 1433 is open and used by SQL Server, you'll need to provide credentials to connect to it and execute commands.

Essentially, the best guess approach will only really work with SQL Servers under your control.

Answer 2

There's two possible ways to take your answer: 1. you want to know if a SQL server is running on an arbitrary host
2. You want to ping an arbitrary host from a SQL server using some query.

1. you could use dariom's answer. 1a. if you want to query one SQL server from another, you're better off using the "Linked Servers" functionality of SQL Server.

2. You can use the master.dbo.xp_cmdshell stored procedure to execute PING, which then returns the results as text rows and a status code.

EXEC master.dbo.xp_cmdshell 'ping 192.168.1.1'