How can I test for SQL Server connectivity from DOS?

Question

I am trying to figure out how to "test for connectivity" to a SQL Server database from DOS. If the connection fails I need to detect the failure.

sqlcmd -Q "select 'a test'" -S .\SQLEXPRESS

You'll notice that that command doesn't return a true/false value. Also, if the instance doesn't exist, it times out. I want something like this that doesn't have a "timeout" issue that makes the user wait:

if ( connectivity exists to local )  (
  setup DSN for local
) else (
  setup DSN for local\SQLEXPRESS
)

Strangely, osql.exe -L doesn't work for me even though I do have a listening instance.

Answer 1

Timeout: I do not think you will be able to avoid a timeout, if you really want to "check for connectivity". But see below for an alternative.

Boolean response: It is enough if the command returns a valid errorlevel. For example, the following code should work:

osql -E -S SERVER\INSTANCE -Q ""
if ERRORLEVEL 1 (
  exit script
) else (
  setup DSN
)

Timeout-free alternative: If the SQL Server is always located on the local machine and you just want to check whether a specific instance is installed or not, try having a look in the registry (using the reg command). HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\InstalledInstances is what you're looking for.