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...
>
>

capturing a warning - ongoing basis

Hi all,

I currently have a series of stored procedures that capture stock prices on a daily basis, then summarize the results into a daily, and further, a weekly summary of the "index" of a group of stocks. The data is accumulated from a (to use a highly technical unit of measurement...) bunch-O-individual rows of data using aggregate functions such as AVG and SUM.

The problem is that I occasionally get a warning on such aggregate statements which is the common one complaining thusly: "Warning: Null value is eliminated by an aggregate or other SET operation"

I know where it comes from, and I know how to code to protect the aggregate from complaining (i.e., AVG(ISNULL(yadayada,0)) ) but I am interested in figuring out a way to REPORT the statement that contains null values. I can, of course, capture ERRORS in selects, but is the same mechanism used to capture these NULL warnings on my aggregate statements? I don't necessarily want to know which individual row is causing it, just want to "tag" somehow the statement that results in the warning so I can go back after the run and check into it (after capturing local "pointer" info at the time the offending aggregate is invoked).

The code I use to capture errors and trace information follows:
UPDATE PortfolioPerformance
SET PrevDate = @.PrevDate,
DailyPerChg = GPP.DailyPerChg,
DailySumPriceChg = GPP.DailySumPriceChg,
SumCurrPrice = GPP.SumCurrPrice,
SumPrevPrice = GPP.SumPrevPrice,
StockCount = GPP.StockCnt,
AvgHighPriceRatio = GPP.AvgHighPriceRatio,
AvgLowPriceRatio = GPP.AvgLowPriceRatio,
Volume = GPP.Volume
FROM PortfolioPerformance PP (nolock), VIEW_Get_PortfolioPerformance GPP
WHERE PP.PortfolioID = GPP.PortfolioID AND
(PP.CreateDate = GPP.CreateDate AND
PP.CreateDate = @.CreateDate) AND
PP.PrevDate IS NULL

SELECT @.RowCount = LTRIM(STR(@.@.ROWCOUNT)) /* capture rowcount so @.m_error select doesn't clobber it */

SELECT @.m_error = @.@.Error IF @.m_error <> 0 GOTO ErrorHandler
SET @.TraceMsg = 'Completed Daily Portfolio Performance calculations (updated ' + @.RowCount + ' rows)'
EXECUTE [dbo].[tracelog] 1, 'Index', 'sp_Set_PortfolioPerformance', @.TraceMsg

NOTE: the aggregation in the above code is performed in the view referenced as "GPP", but that's outside the realm of the question, I think, so I won't bore you with the details of that just yet.

So I think if I can capture the warning like I do the errors, I can accomplish what I want to accomplish. I haven't yet been able to find any guidance in the Books Online, so do any of you have any pointers?

Thanks!
Paulhttp://msdn.microsoft.com/library/en-us/howtosql/ht_automaem_5fi1.asp

This might be what you are looking for :
select * from sysmessages where error = 8153|||I think that's a step in the right direction, however, I need to find out what triggers that alert and try to get directly at that. Using this scheme (as I understand the web documentation) will allow me to execute a stored procedure or send an email when the problem HAPPENS, but won't allow my a way to capture the date or portfolio that caused the problem.

That's where my head-scratching comes in on the issue. It would work perfectly if there was a way to pass run-time data (data processing date - which could be different from the system timestamp/current date - and portfolioID) to the alert.

Trouble is, I am processing a buncha-days and a buncha portfolios, and was just looking for an easy way to detect the warning AT THE TIME it occurs, so I can dump out a trace that says what I was working on at the time it happened.

I think I need to know what happens in SQL Server-Land that triggers the alert...I mean, the error/message number of 8153 should be written SOMEWHERE, shouldn't it?

OR, are you suggesting (as I will try out immediately) that I can execute the SQL statement, then check for error 8153 where I check for error codes in my post-select status checking?

If THAT is the current thought, then I am confused, since I check for an sql-error code of ANYTHING other than zero, and (supposedly) bail out and report the error if the post-select status is anything other than zero (in other words, why isn't my error # 8153 being caught up in my web of lies and deceit...err...sorry, I mean my web of error checking :blush:)