Solved

SQL Server error handling

Posted on 1998-01-16
18
306 Views
Last Modified: 2008-02-01
Hello!
      I'm working with a SQL Server Database, this way: I create in my VB5 App a local Jet DB, and link these tables to the SQL Server remote tables through ODBC.
      My problem is error handling from VB: When an error occurs I always get same error: '3146: ODBC call failed.'
      Before that I used to link my local tables to a remote Jet DB (Access), and when an error occurred it tells me what kind of error, like a duplicated index, or Field needed, or Can't delete because there is a related record in another table ... and so.
       But now I don't know how to get more information about what kind of error it happened. Can you help me?

Thanks in advance.
0
Comment
Question by:taco
  • 8
  • 4
  • 3
  • +2
18 Comments
 
LVL 4

Expert Comment

by:rantanen
ID: 1451392
The only additional information Microsoft documentation has for this error is

"ODBC call failed. (Error 3146)

See Also

Using an ODBC connection, you tried to perform an operation on data in an ODBC data source. This error may occur when the ODBC data source is on a network drive and you are not connected to the network. Make sure the network is available, and then try the operation again."

If this is not your case, just reject this.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451393
rantanen, taco askes how to find out more info on the error - not a guess as to what may be causing it.  Perhaps you should reject rantanens answer if you feel my answer is more helpful - the extended error information is in Error$ which has the correct error number appended to the end.

Check out Microsoft Knowledge Base Article ID: Q120763
Title:"How to Retrieve Info from RAISERROR Function in SQL Server DB"
http://premium.microsoft.com/support/kb/articles/q120/7/63.asp

0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451394
Oh, by the way, the recommended method (according to the Q article) is to check Error$ n your error trapping routine as follows:

If Err = 3146 then
 MsgBox Error$
End If

Note that this is different from performing a 'MsgBox Error$(Err)' command, which returns only the "ODBC Call Failed" message.

0
 
LVL 4

Expert Comment

by:rantanen
ID: 1451395
Yes, don't reject it but do following

If you are using DAO

 ' DAO Error Handler
      Dim MyError As Error
      MsgBox Errors.Count
      For Each MyError In DBEngine.Errors

        With MyError
          MsgBox .Number & " " & .Description
        End With
      Next MyError

or if you are using RDO

      ' RDO Error Handler
      Dim MyError As rdoError
      MsgBox rdoErrors.Count
      For Each MyError In rdoEngine.rdoErrors
        With MyError
          MsgBox .Number & " " & .Description
        End With
      Next MyError

This is from Microsoft Knowledge Base

HOWTO: Get More Information on the ODBC Call Failed Error

Article ID: Q161288

It gives you all the messages which might be involved here (usually there at least two).
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451396
aaah... Not a point monger huh?  So NOW you decided to research it...  Fact is... I provided the first useful information.  I never thought I'd see you stoop like this rantanen.
0
 
LVL 4

Expert Comment

by:rantanen
ID: 1451397
mrmick, this was just a test to see how do you react if somebody does what you do (and just what I expected happened).

Taco, I don't mind ... let it go to mrmick and sorry to involve you into this.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451398
rantanen, when someone else answers a question, I don't try to get credit for it.  I only ask for credit when a question hasn't been correctly answered and I have provided the answer.

I do however offer comments to improve or help clarify an answer when I think it might be helpful - but I don't suggest that I deserve credit when the question already has a reasonable answer.

0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451399
rantanen, sorry for the comment regarding the "guess".  We all misinterpret questions on occasion.
0
 

Author Comment

by:taco
ID: 1451400
I think mrmick's answer is closer, but at the moment I get the same message doing:

    MsgBox Error$
or  MsgBox Error$(Err)

  In both cases I get the 'ODBC--call failed' message (I already hate it. Really)
   Why?
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:taco
ID: 1451401
By the way, I have executed rantanen's function (DAO Error Handler), and I get 0 in Error.count !!
Maybe I need to add any more Reference in my VB5 App?
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451402
hmmm...  This may be a bug.  Are you using DAO 3.5 Object Library?

taco, are you using:

On Error Goto ErrorTrap

and down below...

ErrorTrap:

Msgbox Error$
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451403
taco, are you having success in any of your queries?

I'm wondering if perhaps you're actually not even connecting with the remote server.


0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1451404
Look, despite all the goodwill generated here, you really can't get good error information from SQL server if you are going through the JET engine, using linked tables.  If you just want to determine at development time what is causing your errors, then turn on the Trace for ODBC connections in your 32 bit ODBC driver manager.  It's one of the tabs.  If you are deploying a production application and want to effectively handle error conditions from SQL server in VB, then your best bet is to use RDO on a direct connection to the SQL server, rather than trying to go through JET as a middleman.  JET confuses the error trapping and autoatically does some things (such as wrapping large batch operations within a single transaction) that are problematical to multiuser applications and generate sometimes excessive loads on locking and logging and tempdb requirements on your server.

When you get an error connecting direct to the SQL server, you can examine the rdoerrors collection, and you will find that it is usually not the first error reported that is the problem, but the highest count error in the collection that contains information as to the real source of the error.
0
 
LVL 8

Expert Comment

by:mrmick
ID: 1451405
hmmm... cymbolic, this has already been offered in the statement: "This may be a bug." - yet you proposed it as the answer?... The question was not "What's the best way to connect..." It was "here is what I'm doing, what is the method of getting extended error info?".
0
 
LVL 9

Expert Comment

by:cymbolic
ID: 1451406
Fortunately for Microsoft, and unfortunately for developers, the JET engine and Access is designed and limited for file access (ISAM) server operation.  The recommended solution for developers using VB and SQL Server is either RDO or with VB5.0 ODBCDirect.

If you only have MS Access, then linking to SQL server tables is a necessary, but not really sufficient, method for getting to SQL Server.  But when using  JET engine (DAO) under VB, you are using DAO to JET asking JET to use ODBC to SQL Server.  You don't get the error detail that you need back through JET.

At design time, a quick way to get a better determination of your problem, if processing SQL to SQL server through linked tables, is to go into MS Access, and open a new query, copy and insert your SQL into the SQL View, and run it from MS Access.  

But the real solution and best solution for you is to connect directly to SLQ server and use The RDO Access method.  It performs much better, and the rdoErrors collection WILL give you the source error for your problem, but you must examine it.  The final error that you get direct from VB error handling is usually a derivative error, some iterations down the stack from the real source of your problem.  

Also, despite Microsoft claims, if you are seriously using ODBC, and MS Access, and RDO, you need to use RDO 2.0, which works with VB4.0 and is delivered with VB 5.0, but get SP2 for 5.0.  There are still irritating bugs and problems with ODBC 2.5, so you need the ODBC 3.0 Driver Manager and MS Access 8.0 (Access 97) to get a consistently useable ODBC access and the  JET engine version that is the cleanest.

Usually for Microsoft, the first really useable version doesn't happen until at least the third try!
0
 

Author Comment

by:taco
ID: 1451407
Well, thanks cymbolic. I would really prefer not to change to RDO, because I have made already 80% of my App using DAO and linked tables, and I don't known if changing to RDO will make me change much code ?. (I know quite well DAO but not RDO).
   I use linked tables only because I read is an efficient method to access a remote DB, instead accessing directly to the remote DB.
   
   Back to the error message, as you said, I usually make my queries in Access (local DB with the linked tables), and then paste the SQL query in my VB App. And when working with Access, if a query generates an error I get TWO MessageBoxes in the screen: First the famous 'ODBC--call failed', and when I click on the OK button, it comes a second messagebox with the SQL Server error (A 2 or 3 lines message)

   That is why I think maybe it's possible from VB to get extra info of the error.
   But when working with VB and the same DB, I don't get the second error message. (I even tried to clean first error (Err.Clear) and check again for errors, but I got nothing)

0
 

Author Comment

by:taco
ID: 1451408
Adjusted points to 150.
0
 

Accepted Solution

by:
Marce earned 150 total points
ID: 1451409
I see... I have had same problem, and rantanen is pointing to it, but not exactly. You must use this code in your error trapping, but only WHEN AN ERROR OCCURS:

Select Case Err.Number
Case 3146
   Dim MyError As Error
   For Each MyError In Errors
      MsgBox MyError.Number & " " & MyError.Description
   Next
   Err.Clear
end Select

It usually generates at least 3 errors, one 'ODBC call fail', another like 'Command aborted' and another one with the real error. Then you should make another select case... inside the for each ...next to really handle errors.

Hope this helps.
Marce.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

757 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

17 Experts available now in Live!

Get 1:1 Help Now