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
158 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
ID: 40443472
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
ID: 40447357
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
ID: 40450170
For ODBC 3146 error, you can refer below link
https://support.microsoft.com/kb/195225?wa=wsignin1.0
1
 

Author Comment

by:Marisa Stevenson
ID: 40450458
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
ID: 40450573
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
ID: 40450689
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
ID: 40451726
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
ID: 40452574
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 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 333 total points
ID: 40452606
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
ID: 40452733
Are you referring to using SQL Server Profiler to set a SQL Trace?
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40452737
Yes.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:Marisa Stevenson
ID: 40452761
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
ID: 40452914
<<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
ID: 40453306
Yes, very true, Jim.  In fact, the older I get, the longer my list gets!
0
 
LVL 46

Assisted Solution

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

Author Comment

by:Marisa Stevenson
ID: 40455625
Thank you!
0
 

Assisted Solution

by:Marisa Stevenson
Marisa Stevenson earned 0 total points
ID: 40455653
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
ID: 41470835
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
ID: 41466578
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 46

Expert Comment

by:Vitor Montalvão
ID: 41466589
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
ID: 41470836
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 46

Expert Comment

by:Vitor Montalvão
ID: 41471649
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
ID: 41478472
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

New My Cloud Pro Series - organize everything!

With space to keep virtually everything, the My Cloud Pro Series offers your team the network storage to edit, save and share production files from anywhere with an internet connection. Compatible with both Mac and PC, you're able to protect your content regardless of OS.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
I'm a big fan of Windows' offline folder caching and have used it on my laptops for over a decade.  One thing I don't like about it, however, is how difficult Microsoft has made it for the cache to be moved out of the Windows folder.  Here's how to …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

919 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

18 Experts available now in Live!

Get 1:1 Help Now