Job Failed wil Warning BUT Success from Query Analyzer

Question

Job Failed wil Warning BUT Success from Query Analyzer

Mike Levan

SSChampion

Points: 14201
More actions
April 3, 2010 at 6:13 am

#220032

Query used in job
SET ANSI_NULLS ON
GO
SET ANSI_WARNINGS ON
GO
DECLARE @db char(2)
DECLARE @Server char(5)
DECLARE @date datetime
set @Server = 'OBINVTR'
set @db = 'Rev_Prod'
select @date = max(BeginDate) from OBBEAVER.State_RevReport.dbo.DateParameters where RevID = @db
EXEC OBBEAVER.State_RevReport.[dbo].[FindCountInRevState] @db,@Server,@date
;
This is the error is see every time in a sql job but when i run in QA it works fine, how different is SQL Agent when executing SET Operations when comapred to Query Alalyzer
Executed as user: StateD\RepID. ...SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153) Warning: Null value is eliminated by an aggregate or ot... The step failed.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150457 More actions · Answer 1

Mike Levan (4/3/2010)
This is the error is see every time in a sql job but when i run in QA it works fine, how different is SQL Agent when executing SET Operations when comapred to Query Alalyzer

Either you have ANSI_WARNINGS OFF in your query session, or you are not checking the Messages tab for the warning messages.

The cause is the MAX aggregate. It is being asked to find the maximum value from a list that includes NULLs. Change the query so you do not include the NULLs.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 2

Mike Levan (4/3/2010)
SET ANSI_WARNINGS ON

@paul-2 : Still can we expect ANSI_WARNINGS OFF ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Mike Levan SSChampion Points: 14201 More actions · Answer 3

Mike Levan

SSChampion

Points: 14201

April 3, 2010 at 6:36 am

#1145299

No, I dont have NULL 's from the query result.

Mike Levan SSChampion Points: 14201 More actions · Answer 4

bhuvanesh

I think Paul was asking if i had OFF in my query session but NO either in job or query session i am explicitly using " ON "

Paul White SSC Guru Points: 150457 More actions · Answer 5

Bhuvnesh (4/3/2010)
Mike Levan (4/3/2010)
SET ANSI_WARNINGS ON
@paul-2 : Still can we expect ANSI_WARNINGS OFF ?

Not if that statement was executed in the query window - but I didn't watch him run the query manually 😀

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 6

Mike Levan (4/3/2010)
No, I dont have NULL 's from the query result.

There are lots of possibilities here, so I'm going to take them one by one.

Does the following query return zero?

SELECT COUNT(*)

FROM OBBEAVER.State_RevReport.dbo.DateParameters

WHERE RevID = 'Rev_Prod'

AND BeginDate IS NULL;

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Mike Levan SSChampion Points: 14201 More actions · Answer 7

Paul White NZ (4/3/2010)
Mike Levan (4/3/2010)
No, I dont have NULL 's from the query result.
There are lots of possibilities here, so I'm going to take them one by one.
Does the following query return zero?
SELECT COUNT(*)
FROM OBBEAVER.State_RevReport.dbo.DateParameters
WHERE RevID = 'Rev_Prod'
AND BeginDate IS NULL;

YES, it returns 0

Paul White SSC Guru Points: 150457 More actions · Answer 8

Are you using Query Analyzer or SQL Server Management Studio?

If it is SSMS, what messages do you get on the Messages output tab?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Paul White SSC Guru Points: 150457 More actions · Answer 9

Just to check: "OBBEAVER" is a remote server, right? It's not a loopback - that is, a linked server that points to the originating server?

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi