taco
asked on
SQL Server error handling
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.
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.
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
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
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.
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.
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).
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).
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.
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.
Taco, I don't mind ... let it go to mrmick and sorry to involve you into this.
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.
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.
rantanen, sorry for the comment regarding the "guess". We all misinterpret questions on occasion.
ASKER
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?
MsgBox Error$
or MsgBox Error$(Err)
In both cases I get the 'ODBC--call failed' message (I already hate it. Really)
Why?
ASKER
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?
Maybe I need to add any more Reference in my VB5 App?
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$
taco, are you using:
On Error Goto ErrorTrap
and down below...
ErrorTrap:
Msgbox Error$
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.
I'm wondering if perhaps you're actually not even connecting with the remote server.
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.
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.
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?".
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!
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!
ASKER
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)
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)
ASKER
Adjusted points to 150.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
"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.