Capturing Error Message Text

I am trying to find and debug errors in distributed transactions. I have
altered many of the 7300 series of errors so that they are logged in the SQL
Server Error Log file. The problem I am having is that the error messages
represent the values stored in the sysmessages table, and not what would be
displayed had I ran these queries in Query Analyzer.
For example if I execute this obviously garbled passthrough query I get back
a message 7321.
SELECT *
FROM OPENQUERY(QI3T,'
SELECT sdaf
FROM sdfadsf
')
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB
provider 'MSDAORA'.
[OLE/DB provider returned message: ORA-00942: table or view does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
returned 0x80040e14].
When I look in the SQL Log file the I see the following:
2005-01-21 23:08:23.88 spid55 Error: 7321, Severity: 16, State: 2
2005-01-21 23:08:23.88 spid55 An error occurred while preparing a query
for execution against OLE DB provider 'MSDAORA'. .
How can I capture this part of the message that I see when I execute the
statement in QA?
[OLE/DB provider returned message: ORA-00942: table or view does not exist
]
OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
returned 0x80040e14].
I have looked everywhere on how to actually capture error message text, and
not just the error string stored in sysmessage. I am about to give up but it
would help me sleep better if someone could definitively tell me that it is
not possible.
Thanks for the good night's rest in advance. Whoever answers this is my hero
.
JeremyHave you looked at Erland's excellent articles on error handling...
http://www.sommarskog.se/error-handling-I.html
http://www.sommarskog.se/error-handling-II.html
-oj
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>I am trying to find and debug errors in distributed transactions. I have
> altered many of the 7300 series of errors so that they are logged in the
> SQL
> Server Error Log file. The problem I am having is that the error messages
> represent the values stored in the sysmessages table, and not what would
> be
> displayed had I ran these queries in Query Analyzer.
> For example if I execute this obviously garbled passthrough query I get
> back
> a message 7321.
> SELECT *
> FROM OPENQUERY(QI3T,'
> SELECT sdaf
> FROM sdfadsf
> ')
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
> When I look in the SQL Log file the I see the following:
> 2005-01-21 23:08:23.88 spid55 Error: 7321, Severity: 16, State: 2
> 2005-01-21 23:08:23.88 spid55 An error occurred while preparing a query
> for execution against OLE DB provider 'MSDAORA'. .
> How can I capture this part of the message that I see when I execute the
> statement in QA?
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
>
> I have looked everywhere on how to actually capture error message text,
> and
> not just the error string stored in sysmessage. I am about to give up but
> it
> would help me sleep better if someone could definitively tell me that it
> is
> not possible.
> Thanks for the good night's rest in advance. Whoever answers this is my
> hero.
> Jeremy|||You could try to get the linked server to log the errors or alternatively
the client could possibly trap and log/display both messages (like query
analyser does).
I couldnt find a way to get SQL server itself to log the error thrown by the
linked OLEDB server. Although I got SQL agent to log it with no problem, I
also got a web server running ASP/ADO to trap and list both error messages:
on error resume next /* it only displays the useless one
without this */
Conn.Execute "my_sp"
for Cnt = 0 to Conn.Errors.Count-1
Response.write Conn.Errors(Cnt).Description&"<BR>"
next
hope this helps :)
Mr Tea
http://mr-tea.blogspot.com
"Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>I am trying to find and debug errors in distributed transactions. I have
> altered many of the 7300 series of errors so that they are logged in the
> SQL
> Server Error Log file. The problem I am having is that the error messages
> represent the values stored in the sysmessages table, and not what would
> be
> displayed had I ran these queries in Query Analyzer.
> For example if I execute this obviously garbled passthrough query I get
> back
> a message 7321.
> SELECT *
> FROM OPENQUERY(QI3T,'
> SELECT sdaf
> FROM sdfadsf
> ')
> Server: Msg 7321, Level 16, State 2, Line 1
> An error occurred while preparing a query for execution against OLE DB
> provider 'MSDAORA'.
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
> When I look in the SQL Log file the I see the following:
> 2005-01-21 23:08:23.88 spid55 Error: 7321, Severity: 16, State: 2
> 2005-01-21 23:08:23.88 spid55 An error occurred while preparing a query
> for execution against OLE DB provider 'MSDAORA'. .
> How can I capture this part of the message that I see when I execute the
> statement in QA?
> [OLE/DB provider returned message: ORA-00942: table or view does not exist
> ]
> OLE DB error trace [OLE/DB Provider 'MSDAORA' ICommandPrepare::Prepare
> returned 0x80040e14].
>
> I have looked everywhere on how to actually capture error message text,
> and
> not just the error string stored in sysmessage. I am about to give up but
> it
> would help me sleep better if someone could definitively tell me that it
> is
> not possible.
> Thanks for the good night's rest in advance. Whoever answers this is my
> hero.
> Jeremy|||oj,
Thanks for you reply. I took a look at the articles you suggested and it
looks like Yukon has a new method to retrieve the text of the error messages
that I am trying to obtain within SQL. This excerpt from the article confirm
s
my fears that what I need to do might not be possible the way I had hoped...
"The construct is similar to error-handling concepts in languages like C++.
If an error occurs in the TRY block, or in a stored procedure called by the
TRY block, execution is transferred to the CATCH block. In the CATCH block,
you have access to six new functions: error_number(), error_severity(),
error_state(), error_message(), error_procedure() and error_state(), that
gives you all parts of the message associated with the error. And, yes,
error_message(), is the expanded message with the parameters filled in.–"
http://www.sommarskog.se/error-handling-I.html#Yukon
Thanks again,
Jeremy Lubich
"oj" wrote:

> Have you looked at Erland's excellent articles on error handling...
> http://www.sommarskog.se/error-handling-I.html
> http://www.sommarskog.se/error-handling-II.html
>
> --
> -oj
>
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>
>|||Thanks for you reply. Unfortunately it seems that SQL Server is unable to
provide error message text from linked servers to any error handling code
until Yukon is around. I can't wait to get at the error handling features of
Yukon, it will dramatically improve our ability to gracefully handle
disruptions of connectivitly to the many linked servers that our databases
access.
Regards,
Jeremy Lubich
"Lee Tudor" wrote:

> You could try to get the linked server to log the errors or alternatively
> the client could possibly trap and log/display both messages (like query
> analyser does).
> I couldnt find a way to get SQL server itself to log the error thrown by t
he
> linked OLEDB server. Although I got SQL agent to log it with no problem, I
> also got a web server running ASP/ADO to trap and list both error messages
:
> on error resume next /* it only displays the useless one
> without this */
> Conn.Execute "my_sp"
> for Cnt = 0 to Conn.Errors.Count-1
> Response.write Conn.Errors(Cnt).Description&"<BR>"
> next
> hope this helps :)
> Mr Tea
> http://mr-tea.blogspot.com
> "Jeremy Lubich" <JeremyLubich@.discussions.microsoft.com> wrote in message
> news:8B85D3FA-6C90-4A04-A4F4-3EA29AF723DF@.microsoft.com...
>
>