Database execute hangs on lack of server

Hi

The following JAL code hung and blocked a queue in our 24x7 windows 3.6.6 environment two days ago when it ran while the db server it connects to was powered down.

DatabaseConnect( "*profilename*" )
Dim rc, number

DatabaseExecute( "Exec *stored proc*", rc )

Surely it should handle that gracefully?

Thanks
Lee

Sure. The connection handshake should normally timeout after 60 seconds or so. In case of TCP based connection, socket connection should timeout, and the database driver should catch that and pass back this error to the client application (24x7 in this case) indicating that connection failed. If a different type of connection is used (Local Pipes, Shared Memory, etc…) the behavior of a failed connection is driver specific.

Do you see any sign of a failed connection in debug.log file?

it's a tcp based connection very standard to ms sql 2005 via ODBC.

The only thing in the debug file for this job is

29/12/2009 9:53:47.899 2 290 0 DEBUG [THREAD 1596] Invalid CVC Reporting: Process finished with exit code 4294967295

which is when it was killed manually via the web console

The schedule.log file shows

25/12/2009 07:06:25:932 0 290 0 Invalid CVC Reporting Semaphore file(s) *semaphore* found. Submitting job to the queue.
and a bunch of other found semaphores ignored already queued messages.

I can only say that normally 24x7 should write a message to debug.log on each database error including failed connections. If there is no message, it means it got stuck before the error reached 24x7.

So what can I do to prevent this? the code I posted first up is the only code in this job.

I suggest to ad PingPort before DatabaseConnect. If that command succeeds, then call DatabaseConnect, if not, fall back and wait for a minute or two before trying again and if the database server is still not responding (server or network could be down) send an alert to notify personnel about the issue.