Err.Description is blank

I've just ran some basic checks (using a very simple error.asp file), and the only time Err.Description is empty, is when the SQL database is causing (or reporting) the error?

Is there a checkbox in SQL somewhere that says "report all errors?"

Slight change of question.

I need to use the SQLOLEDB provider for returning XML from SQL, but it looks as though this provider does not provide error descriptions, just numbers.

I guess my situation is bleak, but can I have both a SQLOLEDB provider, and Err.Descriptions @ the same time?

Hope I can....

how are you connecting to the database?

have your live and test servers got the same versions/setup and i don't just mean SQL Server?

Matt.

Running through IIS (ASP pages)

Test and Live both running Win2K Server (same version, so I'm told). Both with the same version of SQL 2000, and both with pretty much everything else the same!

I'm trying to see if they have the same MDAC version, as I think this could be the only difference?

to check for the MDAC Version, do a search for msado15.dll and check the version of the DLL.

that was my thought as well alfa, you can download a MDAC version checker from microsoft, it'll tell you exactly what version instead of having to guess like b1s answer.

if you have MDAC 2.60 then that might have caused this problem.

Have you installed SP2 on SQL Server 2000, if so that installs MDAC 2.62 so that won't be your problem.

Matt.

Well, one more thought.  If you're using OleDB then you must be using ADO to talk to the database.  The connection object in ADO has an errors collection that should contain all of the errors.  Most of the time, the first error in the connection object's errors collection gets put into the VB Err object, but appearantly not all of the time.  Try something like this in your error routine to see if you get any more informative results:

ErrorRtn:
Dim i as long
dim sErrors as String

For i = 0 to oConn.Errors.Count - 1
   sErrors = sErrors & oConn.Errors(i).Number & vbTab & oConn.Errors(i).Description & vbCrlf
Next i

msgbox sErrors

I had the same thing happening.  an empty Err object, which makes sense.  the Err object is there to handle problems with your code, and if ADO properly handles the errors none should be raised or reported by the Err object

On Error GoTo Handle_Err

Handle_Err:
     ErrorCollection.Number = Err.Number
     ErrorCollection.Description = Err.Description    
     Err.Clear

otherwise when the call returns it would also hit your error handling routine right?

Your solution is to examine the Errors collection in the connection object, looping through it to see if there are any errors to report of significant magnitude.

Here is the code i use in my ASP database include:

     if i_g_objConnection.Errors.Count > 1 then
          ' check the error that occured, see if it's worth stopping for
          Dim i_objError

          for each i_objError in i_g_objConnection.Errors
               ' ignore the error if the value is not large enough
               if i_objError.Number <> 0 then
                    Response.Write "Error Number: " & i_objError.Number & "<p>"
                    Response.Write "Description: " & i_objError.Description & "<p>"
                    Response.Write "Source: " & i_objError.Source & "<p>"
                    Response.Write "SQLState: " & i_objError.SQLState & "<p>"
                    Response.Write "NativeError: " & i_objError.NativeError & "<p><hr><br>"
               end if
               
               i_blnCritialError     = true
          next
     end if


(I hope that formatted correctly on the page)

Happy coding!!

Sorry, I ment this to be posted as an answer

I had the same thing happening.  an empty Err object, which makes sense.  the Err object is there to
handle problems with your code, and if ADO properly handles the errors none should be raised or reported
by the Err object

On Error GoTo Handle_Err

Handle_Err:
    ErrorCollection.Number = Err.Number
    ErrorCollection.Description = Err.Description    
    Err.Clear

otherwise when the call returns it would also hit your error handling routine right?

Your solution is to examine the Errors collection in the connection object, looping through it to see
if there are any errors to report of significant magnitude.

Here is the code i use in my ASP database include:

    if i_g_objConnection.Errors.Count > 1 then
         ' check the error that occured, see if it's worth stopping for
         Dim i_objError

         for each i_objError in i_g_objConnection.Errors
              ' ignore the error if the value is not large enough
              if i_objError.Number <> 0 then
                   Response.Write "Error Number: " & i_objError.Number & "<p>"
                   Response.Write "Description: " & i_objError.Description & "<p>"
                   Response.Write "Source: " & i_objError.Source & "<p>"
                   Response.Write "SQLState: " & i_objError.SQLState & "<p>"
                   Response.Write "NativeError: " & i_objError.NativeError & "<p><hr><br>"
              end if
             
              i_blnCritialError     = true
         next
    end if


(I hope that formatted correctly on the page)

Happy coding!!

ebosscher,

>> Sorry, I ment this to be posted as an answer<<

Welcome to EE.  We usually post solutions here as comments, especially when they duplicate a previous solution (see mdougan's comment).  You may want to re-read the EE Guidelines at
https://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp

Anthony

Thanks Anthony!

Very new to this, sorry if I fouled up on the ettiquette (sp?)

Evan

No problem, we have all done it.

Anthony

Hi ebosscher,

you are right that ADO should be handling the errors, and not passing them to the Err object, BUT:

The Err object does contain the Err.number and the Err.Source;
The connection.Errors collection does not similarly contain a description property. I tried running a loop through the ADO errors collection (as you have described), but that similarly did not contain a description?

I'm going to check the MDAC version on both machines, except I'm not sure if I'll be allowed to download the MDAC version checker. The server guys are very funny about what I do on 'their' box!! :-)

VERY DISAPPOINTING...

Seems both servers are running MDAC version '2.6 RTM (2.60.6526.3)'

And they're both running Win2k professional Server, and SQL Server 2000!

Can't see what else remains to be compared as to why one version of ADO is returning an Err.description and the other isn't? I'm using the SQLOLEDB provider on both machines....

Any further ideas?

Certainly sounds like a fix simonsabin. I've passed it on to the server guy, and I'll let you know the progress when and if he get's 'round to doing this :-)

Thanks for now though...

This has been pointed out twice before here, but in case you missed it, here it goes again.  If you are just using the Err object, you are not taking full advantage of the ADO approach to detecting and resolving all errors. The Err object is a blunt tool in comparison.

>>The connection.Errors collection does not similarly contain a description property. I tried running
a loop through the ADO errors collection (as you have described), but that similarly did not contain
a description?<<

The ADO Errors collection is just a collection, so it is true it does not contain a Description property, nor would you expect it to.  What you are looking for is the ADO Error object.  This does contain a Description property, as well as other properties such as Source, NativeError, SQLState that give you the actual SQL Server error.

Having said that, one reason why the Err.Description is coming back empty is because you may inadvertently clearing the Err object.  This can be done by executing code prior to checking the values.  However, if the same code is being executed on the two machines, than this would not be likely the case.

Anthony

Anthony, fair point on raising this issue again, but I have tried to use the ADO error OBJECT (sorry for assuming it was a collection), by using the connection.errors(i).Description, but this too came back with nothing inside it.

This is what you (and others have suggested), but it is not the fix I'm afraid. Tried it, and it didn't work.

I think simon has the solution, just need to wait until my guys apply it, and I'll get back to see if this is indeed the fix...

Cheers.

Just read the MSDN KB (I know I should have done that in the first place <g>) and I think you are right, this looks like the same problem.  One thing you can do, while waiting for those IT guys to show up, is to change temporarily to ODBC, as from the article, it does not appear to be affected when using this provider.  I am not suggesting this as a solution, but rather as a way to confirm this really is the fix.

Anthony

If I switch to an ODBC move, will that enable me to use the FOR XML EXPLICIT function from SQL? I'm also trying to return a structured XML doc from SQL ;-)

Not sure!
One thing I will say is that changing from OLEDB to ODBC is not as simple as it sounds there are some under the hood issues that can cause problems. Text fields and parameters being two.

I think I'll wait for those server guys to turn up (I think they're planned to show up and do some work some time in May!!! LOL)...

Thanks for the help so far though every one. Ain't it great the way one thing will always effect something else? Just love those MS boys...

I was not suggesting ODBC as anything more than a "check to see if this works".  More so, if this is an easily duplictable error.  And yes, no doubt there are other issues when using ODBC, but that is not the point.

You must have more patience than I, if you are prepared to wait on IT.

Anthony

Simon,

don't think the server guys were able to apply the fix usccessfully, but I think that's their fault rather than the bugs ;-)

I'm guessing this is the correct fix, so am closing this question, hoping that we can sort ours out before too long?!!

cheers anyway.