473,728 Members | 1,955 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
Jul 20 '05 #1
12 11027
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
Erland Sommarskog <es****@sommars kog.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
Jul 20 '05 #3
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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #4
Erland Sommarskog <es****@sommars kog.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(forg ive 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
Jul 20 '05 #5
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(forg ive 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..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs

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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #6
Erland Sommarskog <es****@sommars kog.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(forg ive 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..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs
select MAX(ShipRegion) from Northwind..Orde rs

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,
maxDepositAmoun t 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(maxDepositA mount) 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
Jul 20 '05 #7
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(maxDepositA mount) 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(maxDepositA mount)
from testwarnings
where Txntid=1 and maxDepositAmoun t is not null)

Or even

set @fTemp = (
Select max(coalesce(ma xDepositAmount, 0))
from testwarnings
where Txntid=1 and maxDepositAmoun t is not null)
Jul 20 '05 #8
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..Orde rs.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(maxDepositA mount) 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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #9
Also, post the output of

msdb..sp_help_j ob @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****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
33867
by: Yeray Garcia | last post by:
Hi, I'm using php to delete, rename, copy... some files. Sometimes php has no permissions to do these operations, so the unlink(), rename(), copy(), ... methods return a warning and send it to the browser. What I'd like to do is to get these warning messages and save them in a variable to print them somewhere else later so that it look nicer. With the @ before the methods I can ignore warnings/errors but that's
10
2205
by: Kylotan | last post by:
I have the following code: def IntToRandFloat(x): """Given a 32-bit integer, return a float in """ x = int(x) x = int(x << 13) ^ x return (1.0-((x*(x*x*15731+789221)+1376312589)&0x7fffffff)/1073741824.0) Basically it's a function directly copied from a C implementation. Now
1
2230
by: rob | last post by:
In Python 2.3, this code still prints a warning (when run as a script, not from the interpreter). How do I get rid of the warning? I'd like to do it without passing command line args to python. import warnings warnings.filterwarnings('ignore') a = 0xffffffff Thanks,
22
8310
by: John Fisher | last post by:
void f(int p) { } Many (most?) compilers will report that p is unreferenced here. This may not be a problem as f may have to match some common prototype. Typically pointers to functions are involved. For a long time I have used
9
5363
by: Henry Fr?d?ric | last post by:
I am currently working in Aisin-AW, in development of navigation systems (embedded SW). Since a few weeks, we are checking our source code with splint. We encounter this problem. I hope this mailing-list can help us to solve it : Our team is responsible of 1 module. In our C files, we include Header files from others modules.
2
8642
by: Zarnywoop | last post by:
Hi, Could someone offer a solution to the following issue I have with vc7.1. With VC6 it was possible to turn off linker warnings with a #pragma comment( linker, "/ignore:4049" ) line in the source. With VC7.1 the linker now reports that /ignore is unrecognised, and sure enough when consulting the docs. the option is no
3
3190
by: gil | last post by:
Hi, I'm trying to find the best way to work with compiler warnings. I'd like to remove *all* warnings from the code, and playing around with the warning level, I've noticed that compiling with /W3 I get warnings that with /W4 are shown as remarks, e.g.: warning #177: variable "Foo" was declared but never referenced ....is displayed as a "remark #177" with /W4. That doesn't fit the
0
966
by: minermadison | last post by:
Hi, I have an asp.net project written with VB in Visual Studio Professional 2005. Visual Studio has a bunch of warnings that I would like to ignore. In a Windows Form or Class Library project I simply open up the project's .vbproj file and add the warning numbers I would like to ignore to the <nowarn> section. But my asp.net project is simply based on a folder and does not have a .vbproj file.... So how can I disable/hide...
1
1085
by: tesis | last post by:
Hi all gurus, I'm trying to convert an ASP.NET (2003) project into 2005. It uses C1 components and CR11.5V2, which model is NOT CLS-compliant. This generates several warnings, blocking compilation. I tried do include config.web directive and @Page directives to ignore warnings, with no success: no matter I do, an error (red-backed cross) tells "too many errors". How can I manage to compile ignoring warnings? TIA
0
9424
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9277
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9198
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9135
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8133
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6013
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3236
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2668
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2161
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.