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:)