Solved

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

Posted on 2014-11-14
25
142 Views
Last Modified: 2016-02-24
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.
0
Comment
Question by:Marisa Stevenson
  • 12
  • 7
  • 2
  • +2
25 Comments
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 84 total points
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
Comment Utility
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
 
LVL 5

Assisted Solution

by:Mai Ali (MVP)
Mai Ali (MVP) earned 83 total points
Comment Utility
For ODBC 3146 error, you can refer below link
https://support.microsoft.com/kb/195225?wa=wsignin1.0
1
 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
Comment Utility
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
 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
Comment Utility
Good. You might also set a Profiler to run a little bit before the process kick off so you can have more auditing.
1
 

Author Comment

by:Marisa Stevenson
Comment Utility
Are you referring to using SQL Server Profiler to set a SQL Trace?
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
Yes.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<Maybe I shouldn't admit those kinds of things.>>

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

Jim.
0
 

Author Comment

by:Marisa Stevenson
Comment Utility
Yes, very true, Jim.  In fact, the older I get, the longer my list gets!
0
 
LVL 45

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
Comment Utility
Here's a MSDN article that explains how to use SQL Profiler.
1
 

Author Comment

by:Marisa Stevenson
Comment Utility
Thank you!
0
 

Assisted Solution

by:Marisa Stevenson
Marisa Stevenson earned 0 total points
Comment Utility
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
 
LVL 38

Expert Comment

by:younghv
Comment Utility
I've requested that this question be deleted for the following reason:

Not enough information to confirm an answer.
0
 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Comment

by:Marisa Stevenson
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 

Author Closing Comment

by:Marisa Stevenson
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now