Solved

Err.Description is blank

Posted on 2002-03-12
24
1,780 Views
Last Modified: 2007-11-27
I have produced a little debug/ error-trapping script, to inform users (in a very technical way :-0) what errors have been encountered during their attempt to write data to the database.

On our test server, this works fine, with descriptions such as the old faithful "Violation of PRIMARY KEY..." messages coming up. But on the live server, the error description is completely empty. I have the error number, and the source, but not the description? Seems odd to me.

Live server:
W2k Professional;
SQL Server 2000;
IIS 5;

Would restarting me application be useful, or is this something more peculiar?

Thanks.
0
Comment
Question by:AlfaNoMore
  • 10
  • 5
  • 3
  • +4
24 Comments
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6856172
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?"
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6856309
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....
0
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6856343
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.
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6856379
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?
0
 
LVL 23

Expert Comment

by:b1xml2
ID: 6856406
to check for the MDAC Version, do a search for msado15.dll and check the version of the DLL.
0
 
LVL 2

Expert Comment

by:UncleMatt
ID: 6856455
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.
0
 
LVL 18

Expert Comment

by:mdougan
ID: 6856735
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
0
 
LVL 6

Expert Comment

by:ebosscher
ID: 6856853
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!!
0
 
LVL 6

Expert Comment

by:ebosscher
ID: 6856900
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!!
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6856975
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
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp

Anthony
0
 
LVL 6

Expert Comment

by:ebosscher
ID: 6856997
Thanks Anthony!

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

Evan
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6857085
No problem, we have all done it.

Anthony
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6860027
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!! :-)
0
 
LVL 7

Accepted Solution

by:
simonsabin earned 100 total points
ID: 6860156
The problem is due to the locals of the machine.

Microsoft KB Q279760 http://support.microsoft.com/default.aspx?scid=kb;en-us;Q279760

The cause is :-
Localization issues cause this bug. If the system locale settings are not set to match the user locale setting for non-United States locale settings, the SQLOLEDB Provider returns an error when it encounters one, but the description text associated with the error is not returned.

0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6860157
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?
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6860182
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...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6860740
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
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6860826
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.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6860900
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
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6860975
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 ;-)
0
 
LVL 7

Expert Comment

by:simonsabin
ID: 6861000
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.
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6861059
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...
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 6861084
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
0
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 6924855
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I have helped a lot of people on EE with their coding sources and have enjoyed near about every minute of it. Sometimes it can get a little tedious but it is always a challenge and the one thing that I always say is:  The Exchange of information …
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

744 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

11 Experts available now in Live!

Get 1:1 Help Now