How can I make a SQL agent job to ignore warnings?

[Gary]

Hi! guys,

I have a SQL agent job fails because it gets 10 warnings when it runs
a stored procedure. These warnings are trivial and can be ignored. Can
I make it ignore these warnings and proceed? I think there is some
setting I can do to change the default behavour of an agent job
regarding warnings but I just don't know how to do it.

Any idea?

Thanks,

Gary

[Erland Sommarskog]

Gary (ro************@yahoo.com.au) writes:

I have a SQL agent job fails because it gets 10 warnings when it runs
a stored procedure. These warnings are trivial and can be ignored. Can
I make it ignore these warnings and proceed? I think there is some
setting I can do to change the default behavour of an agent job
regarding warnings but I just don't know how to do it.

Exactly what warnings do you get?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gary]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn*********************@127.0.0.1>...

Gary (ro************@yahoo.com.au) writes:

I have a SQL agent job fails because it gets 10 warnings when it runs
a stored procedure. These warnings are trivial and can be ignored. Can
I make it ignore these warnings and proceed? I think there is some
setting I can do to change the default behavour of an agent job
regarding warnings but I just don't know how to do it.

Exactly what warnings do you get?

Erland,

Thanks for your reply.

I think it is warning 8153. Something like "NULL value has been
eliminated by aggregate function or by a SET statement" (not exact
words though). The job belongs to one of our vendors. Basically there
is a bug in one of the stored procedures it calls. One of SET
statements in the problematic stored procedure doesn't use isnull()
function to filter the result of a subquery properly.

Although I can modify the stored procedure to use isnull() or handle
the warnings within the code, I am not supposed to change their code
(of course I log a support call with them). The thing is I can't wait
for the response, in the meantime, I don't want to set ANSI_WARNINGS
OFF either.

That is the reason I raised my original question. It is also
interesting to me that whether we can do something on the
server/database level to refine the behavour of a agent job in
relation to error handling, or error tolerance rather.

Any idea?

Cheers,

Gary

[Erland Sommarskog]

Gary (ro************@yahoo.com.au) writes:

I think it is warning 8153. Something like "NULL value has been
eliminated by aggregate function or by a SET statement" (not exact
words though). The job belongs to one of our vendors. Basically there
is a bug in one of the stored procedures it calls. One of SET
statements in the problematic stored procedure doesn't use isnull()
function to filter the result of a subquery properly.

Although I can modify the stored procedure to use isnull() or handle
the warnings within the code, I am not supposed to change their code
(of course I log a support call with them). The thing is I can't wait
for the response, in the meantime, I don't want to set ANSI_WARNINGS
OFF either.

That is the reason I raised my original question. It is also
interesting to me that whether we can do something on the
server/database level to refine the behavour of a agent job in
relation to error handling, or error tolerance rather.

I have to admit that I'm versed in Agent's intricacies, since I use it
only occasionally. But I ran a two-step job, where the first step included
a number of "SELECT avg(col) FROM tbl" where col is a column with many
NULL values, and the second step was just a PRINT statement. I ran this
job, and it Agent says that it was successful. Sure, there is some output
in job history, but that is not failure.

One possibility is that there is a real error in your step as well.

If you look up the job in Agent, double-click it and go to steps,
and then Edit, there is the Advanced tab. Here you can control what is
going to happen on Success and Failure. You can also direct output
to a file.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gary]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Gary (ro************@yahoo.com.au) writes:

I think it is warning 8153. Something like "NULL value has been
eliminated by aggregate function or by a SET statement" (not exact
words though). The job belongs to one of our vendors. Basically there
is a bug in one of the stored procedures it calls. One of SET
statements in the problematic stored procedure doesn't use isnull()
function to filter the result of a subquery properly.

Although I can modify the stored procedure to use isnull() or handle
the warnings within the code, I am not supposed to change their code
(of course I log a support call with them). The thing is I can't wait
for the response, in the meantime, I don't want to set ANSI_WARNINGS
OFF either.

That is the reason I raised my original question. It is also
interesting to me that whether we can do something on the
server/database level to refine the behavour of a agent job in
relation to error handling, or error tolerance rather.

I have to admit that I'm versed in Agent's intricacies, since I use it
only occasionally. But I ran a two-step job, where the first step included
a number of "SELECT avg(col) FROM tbl" where col is a column with many
NULL values, and the second step was just a PRINT statement. I ran this
job, and it Agent says that it was successful. Sure, there is some output
in job history, but that is not failure.

One possibility is that there is a real error in your step as well.

If you look up the job in Agent, double-click it and go to steps,
and then Edit, there is the Advanced tab. Here you can control what is
going to happen on Success and Failure. You can also direct output
to a file.

Erland, thanks again for your reply.

I understand that I can arrange an agent job to do certain things like
"going to next step", or "reporting failure", etc.. Maybe I have not
phrased my question preciously(forgive me for my broken English), let
me try again. What I want is to let the agent NOT REGARD warnings as
part of the criteria for job failure, which means warnings are
IGNORED. I don't have problem with how an agent should do once a
failure occurs and I am sure there is no other error in the stored
procedure since I debugged it throughly.

By the way, if you try the following block, you will likely get the
warning 8153 about usage of NULL value:

declare @iTemp int
set @iTemp=select max(colname) from tablename;

In which "tablename" is the table name for the test while "colname" is
the column name in the table with data type integer. If we have
max(colname)= NULL for some reason (although sounds stupid), the
warning occurs. Depends on the business rule involved, in my case
here, an isnull() function should be used to avoid this warning.

If you set up a job with similiar statement in it then your job
probably will fail after 10 warnings occur (ANSI_WARNINGS is ON). But
if you don't want the job to fail just because of this, then my
original question becomes relevant.

It is quite shameful that I still don't know whether we can do this
even after using MSSQL for about 7 years.

Cheers,

Gary

[Erland Sommarskog]

Gary (ro************@yahoo.com.au) writes:

I understand that I can arrange an agent job to do certain things like
"going to next step", or "reporting failure", etc.. Maybe I have not
phrased my question preciously(forgive me for my broken English), let
me try again. What I want is to let the agent NOT REGARD warnings as
part of the criteria for job failure, which means warnings are
IGNORED. I don't have problem with how an agent should do once a
failure occurs and I am sure there is no other error in the stored
procedure since I debugged it throughly.
I think I understand very well what you were after. The problem I have
I is that I am not able to recreate the situation you are describing. This
first step the first of my job:

select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders

And there are plenty of rows with NULL in that column.
It is quite shameful that I still don't know whether we can do this
even after using MSSQL for about 7 years.

Obviously, you can. Or at least I can. However, since I don't use
Agent that much I don't want to rule out that there is some setting
that controls this. But at the same time, I don't want to rule out
the possibility that you have a real error in your job, but which
is occluded by the warnings. Have you redirected the output of the
job to a file, and reviewed the output?

If you set up a job with the code above, does succeed or fail?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gary]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn**********************@127.0.0.1>...

Gary (ro************@yahoo.com.au) writes:

I understand that I can arrange an agent job to do certain things like
"going to next step", or "reporting failure", etc.. Maybe I have not
phrased my question preciously(forgive me for my broken English), let
me try again. What I want is to let the agent NOT REGARD warnings as
part of the criteria for job failure, which means warnings are
IGNORED. I don't have problem with how an agent should do once a
failure occurs and I am sure there is no other error in the stored
procedure since I debugged it throughly.

I think I understand very well what you were after. The problem I have
I is that I am not able to recreate the situation you are describing. This
first step the first of my job:

select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders
select MAX(ShipRegion) from Northwind..Orders

And there are plenty of rows with NULL in that column.

It is quite shameful that I still don't know whether we can do this
even after using MSSQL for about 7 years.

Obviously, you can. Or at least I can. However, since I don't use
Agent that much I don't want to rule out that there is some setting
that controls this. But at the same time, I don't want to rule out
the possibility that you have a real error in your job, but which
is occluded by the warnings. Have you redirected the output of the
job to a file, and reviewed the output?

If you set up a job with the code above, does succeed or fail?

Erland, thank you for your time.

By running your statement, I didn't get that warning message because
the result of this MAX function will never be NULL although we have a
lots of NULL in the table.

I wrote the following block to simulate the issue I am getting in the
job.

-- 1. create a table to test this warning message.
create table testwarnings
(
TxntId integer not null,
maxDepositAmount float null
)

-- 2. populate the table with data.
insert into testwarnings values(1, null)
insert into testwarnings values(2, 10000.00)

-- 3. run the testing block which will give the message.
declare @fTemp float
set @fTemp =( select max(maxDepositAmount) from testwarnings where
Txntid=1)

I set up a job with only step 3 in it. The job fails as predicated,
with warning message "Warning: Null value is eliminated by an
aggregate or other SET operation."

The job log clearly shows me that after 10 warning messages, it
aborted without any other warnings or errors. Yes, I can't rule out
any possibility of any other warnings or errors. However before it
aborts it only get 10 times of warning as above. Only thing I am
interested in here is how to make the job to ignore warnings. If it
fails, let it fail on real errors, not warnings.

Cheers,

Gary

[Ross Presser]

On 11 Jul 2004 17:04:45 -0700, Gary wrote:

[snip]

-- 3. run the testing block which will give the message.
declare @fTemp float
set @fTemp =( select max(maxDepositAmount) from testwarnings where
Txntid=1)

I set up a job with only step 3 in it. The job fails as predicated,
with warning message "Warning: Null value is eliminated by an
aggregate or other SET operation."

The job log clearly shows me that after 10 warning messages, it
aborted without any other warnings or errors. Yes, I can't rule out
any possibility of any other warnings or errors. However before it
aborts it only get 10 times of warning as above. Only thing I am
interested in here is how to make the job to ignore warnings. If it
fails, let it fail on real errors, not warnings.

Cheers,

Gary

This may be an unattractive solution, but could you use the equivalent of

set @fTemp = (
Select max(maxDepositAmount)
from testwarnings
where Txntid=1 and maxDepositAmount is not null)

Or even

set @fTemp = (
Select max(coalesce(maxDepositAmount,0))
from testwarnings
where Txntid=1 and maxDepositAmount is not null)

[Erland Sommarskog]

Gary (ro************@yahoo.com.au) writes:

By running your statement, I didn't get that warning message because
the result of this MAX function will never be NULL although we have a
lots of NULL in the table.
If you run the batch which I posted, you do get a whole lot of

Warning: Null value is eliminated by an aggregate or other SET operation.

because there are plenty of NULLs in Northwind..Orders.ShipRegion. Of course
this presumes that you ANSI_WARNINGS ON.
-- 3. run the testing block which will give the message.
declare @fTemp float
set @fTemp =( select max(maxDepositAmount) from testwarnings where
Txntid=1)

I set up a job with only step 3 in it. The job fails as predicated,
with warning message "Warning: Null value is eliminated by an
aggregate or other SET operation."

The job log clearly shows me that after 10 warning messages, it
aborted without any other warnings or errors.

One thing is not clear to me. Do you run this batch 10 times in one
job step, and this step fails. Or does the job fail the tenth time
after nine succesful executions? Or does your job have 10 equal job
steps of which the tenth fall?

Sorry for asking stupid questions, but I created a job as you posted,
and it appears to run successfully each time. I included many copies
of the SET statement in the batch, and I have run the job multiple times.
I have not testet many job steps, yet, though.

Maybe it's time to check versions. What does SELECT @@version say at
your box? And, if you are running the job on a different server on
which you are runnin SQL Agent, which is the version on the SQL Agent
box?

Does the log include a "Job failed" message, or how do you see that the
job actually failed?

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

Also, post the output of

msdb..sp_help_job @jobname = 'your-job-name-here'

Since the information is wide, please put the output in an attachment. (Text
file, please!)

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Gary]

Erland Sommarskog <es****@sommarskog.se> wrote in message news:<Xn********************@127.0.0.1>...

Gary (ro************@yahoo.com.au) writes:

By running your statement, I didn't get that warning message because
the result of this MAX function will never be NULL although we have a
lots of NULL in the table.

If you run the batch which I posted, you do get a whole lot of

Warning: Null value is eliminated by an aggregate or other SET operation.

because there are plenty of NULLs in Northwind..Orders.ShipRegion. Of course
this presumes that you ANSI_WARNINGS ON.

-- 3. run the testing block which will give the message.
declare @fTemp float
set @fTemp =( select max(maxDepositAmount) from testwarnings where
Txntid=1)

I set up a job with only step 3 in it. The job fails as predicated,
with warning message "Warning: Null value is eliminated by an
aggregate or other SET operation."

The job log clearly shows me that after 10 warning messages, it
aborted without any other warnings or errors.

One thing is not clear to me. Do you run this batch 10 times in one
job step, and this step fails. Or does the job fail the tenth time
after nine succesful executions? Or does your job have 10 equal job
steps of which the tenth fall?

Sorry for asking stupid questions, but I created a job as you posted,
and it appears to run successfully each time. I included many copies
of the SET statement in the batch, and I have run the job multiple times.
I have not testet many job steps, yet, though.

Maybe it's time to check versions. What does SELECT @@version say at
your box? And, if you are running the job on a different server on
which you are runnin SQL Agent, which is the version on the SQL Agent
box?

Does the log include a "Job failed" message, or how do you see that the
job actually failed?

Erland,

Your input is appreciated.

I think I made a mistake. After double checking the vendor's job, I
found it actually calls one stored procedure(let's call it sp_a) which
calls another stored procedure(let's call it sp_b). The warning
message comes from the latter one (sp_b). I have been concentrated on
problematic one sp_b and totally forgot it is actually call by sp_a in
the job.

The job did fail after 10 warning messages (they arouse from sp_b). I
mean the job status is "failed" in the job list of Enterprise Manager.
I can't post the code here due to the understandable reason ( I know
it makes our discussion more difficult), but I can tell that sp_a
loops through a CURSOR and in every row in the CURSOR, it calls sp_b
which means sp_b is repeatedly called.

Also, Erland you are right. I also created a simple job which only
with
"declare @fTemp float" followed by 11 copies of "set @fTemp =( select
max(maxDepositAmount) from testwarnings where Txntid=1)". The job
finishes successfully each time!!! Now I am totally lost.

Result of "select @@version" is "Microsoft SQL Server 2000 -
8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003
Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build
2195: Service Pack 4) " and I am doing the testing on the server
itself.

I don't know whether just because our job (yours and mine) is doing
direct statement execution , not nested stored procedure calls (as my
vendor's job does), the warning messages are treated differently by
the SQL Agent Service. I will simplify the vendor's job without
changing its characteristic then reproduce the problem so we can dig
deeper.

I will keep you posted.

Thanks again.

Gary

[Erland Sommarskog]

Gary (ro************@yahoo.com.au) writes:

The job did fail after 10 warning messages (they arouse from sp_b). I
mean the job status is "failed" in the job list of Enterprise Manager.
I can't post the code here due to the understandable reason ( I know
it makes our discussion more difficult), but I can tell that sp_a
loops through a CURSOR and in every row in the CURSOR, it calls sp_b
which means sp_b is repeatedly called.
While it is a different scenario, I fail to see how that could affect SQL
Server Agent.

I realize that you cannot post the original code, and anyway I would
have had any use for it, without tables and data.

What I have been asking for is a "repro" - a script, together with
instructions to run it - that demonstrates the problem. The reason I
keep asking for this, is that without this I have to resort to guessing.
If you were to open a case with Microsoft about this, they would ask
you for the same thing. The one difference, is that in that case you
could actually share the code with MS, since it is not a public forum.
Result of "select @@version" is "Microsoft SQL Server 2000 -
8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003
Microsoft Corporation Enterprise Edition on Windows NT 5.0 (Build
2195: Service Pack 4) " and I am doing the testing on the server
itself.
That is SQL Server SP3, the same version as I am using.
I don't know whether just because our job (yours and mine) is doing
direct statement execution , not nested stored procedure calls (as my
vendor's job does), the warning messages are treated differently by
the SQL Agent Service. I will simplify the vendor's job without
changing its characteristic then reproduce the problem so we can dig
deeper.

So this is one of the other points of asking people for a repro. If
they have to condense their problematic code to a manageable repro,
they might as a side-effect find the real problem themselves and also
learnt something on the way. I realize that since you are working with
third-party code that you may be interested in this. But if that vendor
has a support desk, there is one more option.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Dan Wilson]

I have not tried this, but it will probably work for you.

There is a registry entry for this:

HKLM\Software\Microsoft\MSSQLServer\SQLServerAgent \
Key: NonAlertableErrors

Try adding the error number to the list and see if it works.

Hope it helps,
Dan Wilson

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!