We'd like to run a periodic test against our SQL Server to make sure that the service is still running. We've had unfortunate crashes lately and need better notification.

I created a 24x7 job that connects through ODBC to our SQL Server and i issue a simple database command (one that returns a list of users logged in).

When the server is down (which is what i want to test for) i get a dialog box (maybe from ODBC, but the title is "Microsoft SQL Server Login") that tells me "Connection Failed SQL State 01000 SQL Server Error 109", etc.

Is there a reliable way to test SQL server? The above result will not work.

This message comes from the ODBC Manager. So don't use it. Instead you can use the MS SQL Server native driver provided the Scheduler. Make a script job that
calls
DatabaseConnectEx ...
DatabaseDisconnect

Add notification action that 24x7 Scheduler will execute in case if DatabaseConnectEx fails.

A better approach is to place 24x7 on the server and have it check for SQL Server executable files loaded into the memory. You can use "program is not running" schedule type for such job.

Another solution is to have a JAL script job on the server that will keep checking the status of SQL Server services.

You can also consider a job using the PING statement.

: We'd like to run a periodic test against our SQL Server to make sure that the
: service is still running. We've had unfortunate crashes lately and need
: better notification.

: I created a 24x7 job that connects through ODBC to our SQL Server and i issue
: a simple database command (one that returns a list of users logged in).

: When the server is down (which is what i want to test for) i get a dialog box
: (maybe from ODBC, but the title is "Microsoft SQL Server Login")
: that tells me "Connection Failed SQL State 01000 SQL Server Error
: 109", etc.

: Is there a reliable way to test SQL server? The above result will not work.

Hi folks,

Well, i took the suggestion to install the native drivers and use DatabaseConnectEx (actually used DatabaseConnect() as it allows me to use the profile). When the statement is called (and the SQL Server is not running, the issue we wish to test for) 24x7 seems to hang for almost 2 minutes, then it comes alive and issues a dialog box:

"An Error Occurred while executing 24x7 script.

Line 2: Unable to connect: SQL Server is unavailable or does not exist. Specified SQL Server not found.

Continue testing this job?"

When i press [YES] or [NO] i do not receive an e-mail (send e-mail on failed job enabled).

Since i am not testing the job, i don't understand the "Continue testing this job?" message.

Anyhow, this is not the intended behaviour.

Can anyone help?

Regards,

Mark Richards

: This message comes from the ODBC Manager. So don't use it. Instead you can
: use the MS SQL Server native driver provided the Scheduler. Make a script
: job that
: calls
: DatabaseConnectEx ...
: DatabaseDisconnect

: Add notification action that 24x7 Scheduler will execute in case if
: DatabaseConnectEx fails.

: A better approach is to place 24x7 on the server and have it check for SQL
: Server executable files loaded into the memory. You can use "program
: is not running" schedule type for such job.

: Another solution is to have a JAL script job on the server that will keep
: checking the status of SQL Server services.

: You can also consider a job using the PING statement.

There is a difference in scheduler behavior when you run the job by clicking on the Run Now button (which is for an interactive use/job testing) and the job running by the scheduler. In the later case it does not show message boxes and only writes to the job log (of course only if the job has this log option enabled). You possible set "ignore errors" option for the job and as a result of that you don't get your email.

2 minutes is the time it takes for the MS SQL Server DB Library to figure out that cannot make the connection. This timeout parameter probably can be configured in the SQL Server settings.

: Hi folks,

: Well, i took the suggestion to install the native drivers and use
: DatabaseConnectEx (actually used DatabaseConnect() as it allows me to use
: the profile). When the statement is called (and the SQL Server is not
: running, the issue we wish to test for) 24x7 seems to hang for almost 2
: minutes, then it comes alive and issues a dialog box: "An Error
: Occurred while executing 24x7 script.

: Line 2: Unable to connect: SQL Server is unavailable or does not exist.
: Specified SQL Server not found.

: Continue testing this job?"

: When i press [YES] or [NO] i do not receive an e-mail (send e-mail on failed
: job enabled).

: Since i am not testing the job, i don't understand the "Continue testing
: this job?" message.

: Anyhow, this is not the intended behaviour.

: Can anyone help?

: Regards,

: Mark Richards