none
Job fails from warnings? Null value is eliminated by an aggregate

    Question

  • Executed as user: ... ...SET operation. [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by an aggregate or other SET operation. The step failed.

    I have a Job that fails and there are multiple entries in the error log of the job history like the above error.  The job is calling a stored procedure that calls other procedures.

    Any suggestions?

    Searching Bing, Im turning up suggestions to turn off ANSI nulls, and/or to just ignore it.  However, if you notice, the job step FAILS - so either there is something else going on or, the number of these warnings (about 5 or 6) was enough to cause the job to fail.


    • Edited by shiftbit Tuesday, July 02, 2013 9:07 PM
    Tuesday, July 02, 2013 9:07 PM

All replies

  • That is not causing the job to fail, those are just warnings.  Something else is causing the job to fail after those messages.  The Agent history only shows the first 1000 characters.  You need to run the process manually and see the entire list of messages.

    Tuesday, July 02, 2013 9:37 PM
  • Run the job from tsql?

    When the job is run manually or even the next cycle, the error often does not happen again.

    Its the non-repeatability of this that is driving me crazy.

    Next step is to start wrapping the areas where I suspect the problem is, with try/catch and error logging, which should have been done in the first place.

    • Edited by shiftbit Tuesday, July 02, 2013 10:51 PM werwer
    Tuesday, July 02, 2013 10:49 PM
  • Yes, of course. Aggregate functions have removed NULLs since ANSI/ISO Standard SQL-86. It is a non-fatal warning. There is an actually a good story about ANSI X3H2 and when this warning shows up in a cursor (DECLARE, OPEN or FETCH?). 

    This is not your problem. You need to rewrite the code; this is bad SQL programming. 

    In SQL, unlike procedural programming, we do not want a procedure to call another procedure, if-then or loops.  SQL is declarative, so try to do the job in ONE statement  and not with a control flow of any kind. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Wednesday, July 03, 2013 2:22 AM
  • Run the job from tsql?

    When the job is run manually or even the next cycle, the error often does not happen again.

    Its the non-repeatability of this that is driving me crazy.

    Next step is to start wrapping the areas where I suspect the problem is, with try/catch and error logging, which should have been done in the first place.


    Try to use isnull for the relevant column for having it checked by the aggregate function.

    Many Thanks & Best Regards, Hua Min

    Wednesday, July 03, 2013 2:33 AM
  • Go into Advanced settings on the job step and setup a log to file.  Then you will get the entire string of messages.
    Wednesday, July 03, 2013 1:08 PM
  • Go into Advanced settings on the job step and setup a log to file.  Then you will get the entire string of messages.

    Thanks, I had forgot about that area.

    Can I make it output to the log ONLY on failure and not for everything?  Looking at the settings now and just tested it, but it appears it will log to file in both cases?

    Im reviewing msdn now

    http://msdn.microsoft.com/en-us/library/ms188952.aspx

    Wouldnt log to table be better?

    Log to table

    Logs job step output to the sysjobstepslogs table in the msdb database.

    • Edited by shiftbit Wednesday, July 03, 2013 6:34 PM sdfgdfg
    Wednesday, July 03, 2013 6:29 PM