Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Burbank & Santa Cruz de la Sierra

    Question Unanswered: 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?

    Last edited by TallCowboy0614; 05-17-04 at 13:55.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  2. #2
    Join Date
    Jul 2003
    The Dark Planet

    This might be what you are looking for :
    select * from sysmessages where error = 8153
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Feb 2004
    Burbank & Santa Cruz de la Sierra
    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
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts