April 3, 2010 at 6:13 am
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.
April 3, 2010 at 6:21 am
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.
April 3, 2010 at 6:36 am
No, I dont have NULL 's from the query result.
April 3, 2010 at 6:42 am
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 "
April 3, 2010 at 6:42 am
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 😀
April 3, 2010 at 6:47 am
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;
April 3, 2010 at 6:50 am
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
April 3, 2010 at 7:04 am
Are you using Query Analyzer or SQL Server Management Studio?
If it is SSMS, what messages do you get on the Messages output tab?
April 3, 2010 at 7:27 am
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?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply