November 20, 2008 at 3:36 pm
Hi,
I have an application on windows XP that is continuously connected to my DB server by using an OBDC connection (System DSN > SQL Server).
Occasionally, when there is a network issue or my DB server gets rebooted my application throws an error “ODBC error” – “An ODBC error was encountered” and remains hanging until I manually get to it and click on ‘OK’ in which case it usually re-establishes a connection and returns to business as usual.
I am trying to write a vbs script that alerts me on the ODBC error but I can’t find anything in the system’s event log.
Is there a way to prevent the ODBC error by changing the ODBC settings? Or is there a way to see the disconnection from the DB server? Or is there a way to enable logging the error to the system log?
I don’t have the source code of the application.
Thank you
Sam
November 22, 2008 at 9:55 am
First you need to post in the correct forum, one of the connecting or programming ones. I'll move this.
Are the settings being changed or is it just a disconnect? If there virtual network link breaks at the transport or lower layer, then the application breaks. Some types of connections let you trap the error and issue a reconnect, which is what happens in Windows Explorer.
Do you get the error when your app tries to issue a query? Or does this just pop up?
The only way to "discover" the disconnect is to try and send something through it. You could send a "ping" type request, something low level like select user_name() or something and see if it works, or change your app to try..catch and if the query errors, issue a reconnect right away.
November 22, 2008 at 8:05 pm
Thanks for re-directing the post SSChampion,
I’m not sure why I get the disconnection. I can’t point it to one consistent event for every time it happens so I’m assuming it’s a network related issue, outside my DB server and desktop. I don’t change anything on my machines and the error just pops up.
You said that: ”Some types of connections let you trap the error and issue a reconnect, which is what happens in Windows Explorer.” This would be very useful but this is not an application that I programmed, so I do not have the source code to make any changes of that type. All I do is setup the ODBC connection at the OS level on my desktop. Any script I create should be outside the application either on the desktop or on the SQL server.
You also said that “The only way to "discover" the disconnect is to try and send something through it.” Are you suggesting that I write a VBS script on the desktop to ping my DB server using the same ODBC connection of my application? Wouldn’t my ping script use a different instance of the connection? (Not sure how that would work.)
Is there a way to have a procedure on the DB to find out when my connection is lost? Can that work since my connection doesn’t continuously send information?
November 22, 2008 at 9:39 pm
Since you can't change the code and responding to the dialog allows things to continue on, then you might want to consider running the application in an AutoIT script to automate the answering of that dialog.
The Notepad automation example at the following link might give you a better idea if it's something that would work for you.
http://www.autoitscript.com/autoit3/docs/tutorials/notepad/notepad.htm
November 23, 2008 at 5:51 pm
The machine I am referring to already has an ahk script that runs on boot up. Once the boot up process ends, the script has a call to 'ExitApp'.
If I understand what you are saying, I need to remove the 'ExitApp' and continue the script indefinitely with something like:
"
IfWinExist, myTitle
sleep240000;give time to reconnect
WinActivate ;make it the active window
Send {Enter}
return
"
Is there a specific way to loop this ‘ifwinexist’ indefinitely? Do you think this script will delay any other processing?
November 23, 2008 at 6:19 pm
Sammy,
That's exactly what I was thinking. Don't know specifcally how to code that but I'd think the impact would be minimal if any. Best to test if there's any concern though.
Or, maybe run the script externally to your appliation and have that watch for the error dialog title and message and act on it.
November 23, 2008 at 9:00 pm
Sammy (11/20/2008)
Hi,I have an application on windows XP that is continuously connected to my DB server by using an OBDC connection (System DSN > SQL Server).
Occasionally, when there is a network issue or my DB server gets rebooted my application throws an error “ODBC error” – “An ODBC error was encountered” and remains hanging until I manually get to it and click on ‘OK’ in which case it usually re-establishes a connection and returns to business as usual.
I am trying to write a vbs script that alerts me on the ODBC error but I can’t find anything in the system’s event log.
Is there a way to prevent the ODBC error by changing the ODBC settings? Or is there a way to see the disconnection from the DB server? Or is there a way to enable logging the error to the system log?
I don’t have the source code of the application.
Thank you
Sam
Which version of SQL Server you are using ?
If you are using SQL 2000, just re-install MDAC 2.6 or latest version.
in SQL 2005: re-install SQL Native Client.
November 24, 2008 at 3:17 pm
I am using SQL SERVER 2000.
Will that enable me to refresh connections?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply