System.Data.SqlClient.SQLException and OLEDB Provider Error messag
Jordan
.NET Programming
Visual Basic 9.0
SQLClient
OLEDB Providers
ODBC Driver
Possibly SMO/DMO
I am attempting to obtain a more detailed error message from a .NET
SqlException class.
I am using code (Visual Basic .NET 2008) to connect to a SQL Server
instance (SQL 2005 Express Edition) running a linked server object. The
linked server object is an OLEDB Provider for ODBC Drivers.
I am using a Try/Catch block for the code where the connection is made and
data retrieved. The SQLClient.SQLException return from the Exception inherits
from System.Exception in the .NET Framework 2.0
When I encounter the error message "An error occurred while preparing the
query <SQL Statement> in code, I know there is more to this error message.
I know there is more to this error message because the full error message is
return to me when I execute this SQL statement in SQL Server Management
Studio Express.
The full error message is:
OLE DB provider "MSDASQL" for linked server "MAS90_ABC" returned message
"[ProvideX][ODBC Driver]Expected lexical element not found: FROM".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query <SQL Statement>
The first part is the error returned from the ODBC provider on behalf of the
OLEDB Provider. I want the SQLException class to push this message to me so I
can display it to a user who has written an incorrect SQL Statement in an
application wizard.
This is just one example. I also want to return a message when a table
doesn't exist.
I have scoured the SQLException object for a more detailed error message. Am
I just missing it, or is it simply not there in the object? There are some
portions of the object that do not appear in Visual Studio when using the
"view detail" option of the Exception object. Could it be in there somewhere?
I just assumed returning this error message is possible because SQL Server
Management Studio Express is able to get this entire error message. Is this
because SSMS is using SMO? If so, how could I use SMO in my code to get this
error message.
I have seen sample code on linking to a OLEDB-Provider server in SMO, but
how would this help me?
http://msdn.microsoft.com/en-us/library/ms162171(SQL.90).aspx
If I add these references can I execute my SQL Statement in the same way as
SQL Server Management Studio Express?
I could see a task flow where, If I return the SQLException "An error
occurred while preparing the query", then cut over to SMO and run the query
to get a better error message. Is there any doc on the SMO that will help me
accomplish this?
Erland Sommarskog
> Not sure where this question fits, but I think it's within SQL
> Programming.
Of all places, I see it in microsoft.public.data.oledb which definitely
is the wrong place. But else I would have seen it in sqlserver.programming,
which as we soon shall see is on topic.
> I am using a Try/Catch block for the code where the connection is made
> and data retrieved. The SQLClient.SQLException return from the Exception
> inherits from System.Exception in the .NET Framework 2.0
>
> When I encounter the error message "An error occurred while preparing the
> query <SQL Statement> in code, I know there is more to this error message.
>
> I know there is more to this error message because the full error
> message is return to me when I execute this SQL statement in SQL Server
> Management Studio Express.
>
> The full error message is:
> OLE DB provider "MSDASQL" for linked server "MAS90_ABC" returned message
> "[ProvideX][ODBC Driver]Expected lexical element not found: FROM".
> Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing the query <SQL Statement>
The reason you don't see the first part in your exception handler is
because that is a different message from SQL Server, one with a severity
0, so it does not cause an exception, and nor will you see it the
SqlException class.
Instead you need to set up an InfoMessage event handler, and capture
the message that way.
While you are at it, you can set the connection property
FireUserMessagesAsInfoMessages (the exact name may be different; I
am never able to remeber this insanely long name). With that property
set all errors with severity <= 16 from SQL Server fires InfoMessage
events rather than raising exception. This has some advantages, not
the least when you work with multiple result sets.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx