How should I troubleshoot an ODBC 3146 error generated when VBA code is running on a SQL Server backend?

Server: Windows Server 2008
MSS:     SQL Server Standard 2008
VBA:     6.5
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.
Marisa StevensonAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Well 3146 is a generic error and there may be other errors behind it, so first thing is to change your error handler to see any additional errors:

How To Get More Information on the ODBC Call Failed Error
http://support.microsoft.com/kb/161288

Second thing is that more often than not, when you have a situation like this, it's something in the data....

Jim.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes, too much generic error so we can only guess.
* The only thing that seems to have changed since the general timeframe when this started occurring was we transitioned from Trend to McAfee antivirus
Check when the full scan runs and if all the exclusions are set for SQL Server.
1
Mai Ali (MVP)Senior Infrastructure ConsultantCommented:
For ODBC 3146 error, you can refer below link
https://support.microsoft.com/kb/195225?wa=wsignin1.0
1
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Marisa StevensonAuthor Commented:
Thanks for the links to resources - exactly the type of help I am looking for (certainly didn't expect anyone to be able to provide a direct resolution).  I will post again after I have checked out these possibilities.

(Note: I don't see how it can be the data, or the code, if I run the exact same code with the exact same data the next day, and it runs fine.  This part is consistent.  I have never had the process fail when I rerun it the same day.  It has only failed when it runs at night.  I know I already mentioned this, but want to clarify/emphasize, as well as ask if I am missing something in deduction).
0
Marisa StevensonAuthor Commented:
Mai, I confirmed the option is OFF.  The code does not turn it on.  

Jim and Vitor, Not sure when I'll check the McAfee config or add the code to trap the error (as suggested by your links).  I think both will be helpful in isolating/identifying the problem -  just a little swamped at this time with higher priority projects.  But, I will post again to provide additional info and accept solutions as soon as I get back to this!

Thanks all!!
0
Marisa StevensonAuthor Commented:
Oh, who was I kidding?  I have OCD...I can't postpone figuring this out.  So...

No full scans scheduled on the db server.   Added the exclusion on the .bak file and backup directory.  Doubt backup files are causing the problem.

Guess I'll be trapping the error.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you have any night jobs running at the same time? If so, those jobs may causing locks during their execution and that can explain the error you are getting.
0
Marisa StevensonAuthor Commented:
I checked previously, and the only other jobs running at night are backups, and they complete before this job kicks off.    I am double checking to make sure that there aren't any odd circumstances where they might not complete before then.  Almost certain this isn't it, but if it turns out to be the case after I double check, will certainly report back.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Good. You might also set a Profiler to run a little bit before the process kick off so you can have more auditing.
1
Marisa StevensonAuthor Commented:
Are you referring to using SQL Server Profiler to set a SQL Trace?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Yes.
0
Marisa StevensonAuthor Commented:
OK, thanks.  I may need help with that, as I think I have only set a trace a long time ago, and it wasn't scheduled.  In fact, I think I forgot to turn it off and it ended up eating up my disk space with log files. :)  Maybe I shouldn't admit those kinds of things.  Anyway, I'll research and try to figure it out - the internet (and EE) is a wonderful thing.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Maybe I shouldn't admit those kinds of things.>>

 We've all been there one way or another<g>

Jim.
0
Marisa StevensonAuthor Commented:
Yes, very true, Jim.  In fact, the older I get, the longer my list gets!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Here's a MSDN article that explains how to use SQL Profiler.
1
Marisa StevensonAuthor Commented:
Thank you!
0
Marisa StevensonAuthor Commented:
I double checked the backups.  Confirmed there is no overlap in execution time frames.

Next steps are to add the error handling and schedule a trace.  I am also going to begin documenting details when it occurs to see if I can identify any patterns.  Probably should have done that a long time ago, but guess I was hoping it was a fluke that would fix itself.
0
younghvCommented:
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
Marisa StevensonAuthor Commented:
Please do not close this question - it is not abandoned.  I am still troubleshooting this problem and am using some of the information provided in this stream to do so.  This is an intermittent problem that can't be duplicated on command, so troubleshooting  takes some time.  In addition, is there a procedure for ensuring questions are not deleted?  I seldom post in Experts Exchange, but I pay my monthly fee because I want to be able to use it as a reference, and be able to refer back to questions and answers I've posted.  Do I need to select a solution or multiple solutions in order to ensure my posts aren't deleted? In this case, I may not ever have an exact "solution", as you'll note the question was posed as a "how to troubleshoot".  I'd prefer to continue to accept any thoughts/suggestions on this topic, but have received valuable recommendations that I'm using, so if the key to keeping it open requires selecting a "solution", I can certainly do so.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Hi Marisa.
You never came back to give us a follow up on this question. Especially the part where you said: "Next steps are to add the error handling and schedule a trace."

What the trace returns? Anything useful that can be tracked?
0
Marisa StevensonAuthor Commented:
Hi Vitor,

Sorry if my previous post wasn't clear.  Let's see if I can explain a little better.  First, the question posed is "how does one troubleshoot this error" (as opposed to, "how does one solve this problem").  Since the error is generic, there is no single solution.  Several of the responses answered the question, each with a different method, or helpful information, with guidance on how to isolate the error and pinpoint the problem - and from there, the solution.  I am requesting this stream not be deleted as I will use these answers as reference in the future, and think others might benefit from the responses.  In the end, my solution may differ from others', but the information provided will be helpful to them in isolating the problem.  In addition, this is an intermittent problem that only occurs rarely, and because of this, I am unable to apply the techniques to immediately reach a solution.  Finally, this is not a high priority at work compared to higher priority projects, although I will have to get back to it at some point. If we need to close the stream so additional comments can't be added, or so it doesn't appear in moderator queue's (or however that's handled) I understand, but I hope it will not be deleted (as indicated) so I can no longer reference it?  I rarely use Experts Exchange, but I pay a monthly fee primarily so I can go back and reference the issues that challenged me in the past should they reoccur.  I can select multiple solutions if that helps close the loop without deleting the stream?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
You don't need to delete this question and people still can add comments in closed questions. Closed only means that a solution has been found.
You can close this question by choosing your own comment as solution. If you think some other comments helped you mark them as assisted solutions.
Cheers
1
Marisa StevensonAuthor Commented:
I selected multiple solutions.  I also included my own as it summarized the "next steps" in troubleshooting this problem, and also added my own thought that documenting details when these types of intermittent, complex problems occur is a good idea.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.