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

Gary (rooty_hill2002@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, esquel@sommarskog.se

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

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns95212D2EFFCFYazorman@127.0.0.1>...
> Gary (rooty_hill2002@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

Gary (rooty_hill2002@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, esquel@sommarskog.se

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

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns9522921677BCCYazorman@127.0.0.1>...
> Gary (rooty_hill2002@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

Gary (rooty_hill2002@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, esquel@sommarskog.se

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

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns952382AF03399Yazorman@127.0.0.1>...
> Gary (rooty_hill2002@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

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)

Gary (rooty_hill2002@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, esquel@sommarskog.se

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

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, esquel@sommarskog.se

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