How should I troubleshoot an ODBC 3146 error generated when VBA code is running on a SQL Server backend?
Posted on 2014-11-14
Server: Windows Server 2008
MSS: SQL Server Standard 2008
Front-end: MS Access 2007
Connects to SQL Server tables via Linked Table Manager
Hardware: ESX server (virtual environment)
I have some simple VBA code on a Windows 2008 server that has been running unchanged since 2005. In the past several months, I have started intermittently receiving ODBC 3146 errors in the middle of its processing. It connects to the db, runs through the logic for a number of rows, then errors. The logic path where it fails is identical to the statements where it has successfully processed previous records. ODBC connectivity is not defaulted - connection information is in a table. I have done what I know to troubleshoot it and can use some additional ideas.
Code is triggered via Task Scheduler nightly at 11pm. Most nights it runs fine. But occasionally (although frequency seems to be increasing) I come in the next day and Access is sitting with an ODBC 3146 error. I manually kick off the task at that point, and it runs fine. Input is a text file and output is a single SQL Server table. It updates, inserts, deletes from the table based on a condition. Each time I've checked, it is right in the middle of processing where I can see some rows have been updated and can identify where it died.
Due to the reasons below (some simply summarized from my description above), it does not appear to be a code or odbc connectivity issue. I will thereby spare details, though I am open to provide them if necessary.
* The task has been running since 2005 without more than a very occasional ODBC error that I could attribute directly to the server disconnecting (this error code was different - I began receiving the 3146 around May 2014)
* Although I change nothing, I have never received the error when I manually kick off the task the next day
* It occurs in the middle of processing, so we know we have already connected successfully to the database
It seems to be something that is occurring between 11pm and 9am the next day. I am guessing either a network connectivity issue (but we only have problems at night?) or a service that is interfering. Summarized details:
* I see no clues that stand out in the standard SQL Server error logs or event viewer
* There is no obvious evidence of network connectivity problems
* The only thing that seems to have changed since the general timeframe when this started occurring was we transitioned from Trend to McAfee antivirus
* Backup processes (Backup Exec and MSS db) are unchanged, although there is a possibility that some server backups could be running longer
I say that there are no apparent mss or network connectivity problems, but this is based on limited knowledge. I will gladly continue searching if someone can provide direction on what I should search for. One final note, since I don't know if this means anything, but we moved the physical server yesterday so it's on a different switch (for unrelated reasons) - no difference. Got the error last night again.