query runs job but dts fails

Hi i have a stored procedure which if i run by query analyzer it works.
but in I Assign it to a job and run it ..it errors

after looking at results in query analyzer i get
 Null value is eliminated by an aggregate or other SET operation.
but this could be in one of my sum values .

Is there a way just to print out error line after each line of code

so that i can determine where this error is from what part of the code

Thanks
LVL 7
TRACEYMARYAsked:
Who is Participating?
 
ShogunWadeCommented:
ok.

The really irritating thing with SQL Agent is its limitation in the number of characters it will display in the error description.

the message you see is because when you perform an agregate such as MAX,MIN AVG, etc   NULLs have no meaning.  so a warning is displayed to let you know that the average skips that row.

thus:

say you have 2 records in a table values
10
0

the AVG of these would be 5

but  if the values were
10
NULL

the AVG would be 10  because it ignored the NULLS, thats what the messages is trying to say.   Anyway... to cut a long story short.... youll probably be able to get the true error message from the job history if you supress the warnings.

Change your job so that instead of just calling the stored procedure is does this:


set ansi_warnings off
go
EXEC spiLoadProdSched_A ......etc.....


the top line instructs SQL not to display this warning, then hopefully what you are left with in the job log when you run it again is the real error message.
0
 
AustinSevenCommented:
If it works in QA but not in a job, I think the query is fine.   If you check the job history and select the check box in the top right corner, this will show you more error info per step.   Find out why the job fails please.  Do other jobs run ok?  

AustinSeven
0
 
Jay ToopsCommented:
Things assigned to a job typically fail because the execute with the
SQL server agent permission and don't have access to somthing

Typically in debugging a package
id go to package -> properties -> advanced and
turn off "use transactions"
so that i can tell by looking at tables exactly what point the job fails..

you can alternatively places statements like

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDTSLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tblDTSLog] (
      [ExecDate] [datetime] NULL ,
      [Message] [varchar] (200) NULL
) ON [PRIMARY]
END

Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Process Begins step 1.')
GO

Jay
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ShogunWadeCommented:
"Null value is eliminated by an aggregate or other SET operation" should not cause a DTS task to fail is this is a warning not an error.

I suspect that this is permissions issue or some other context related issue
0
 
TRACEYMARYAuthor Commented:
Job history

Executed as user: dbo. 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 other SET operation. [SQLSTATE 01003] (Message 8153)  Warning: Null value is eliminated by

As for package part....hmmm
   All i am doing is goign to sql server agent job...adding job and
   doing a
      exec spiLoadProdSched_A command


Where do i place the
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDTSLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tblDTSLog] (
     [ExecDate] [datetime] NULL ,
     [Message] [varchar] (200) NULL
) ON [PRIMARY]
END

Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Process Begins step 1.')
GO

in the actually begining on code ?

We have jobs running but just this one fails.

0
 
Jay ToopsCommented:
--This part in the beginning of the sp

if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblDTSLog]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
CREATE TABLE [dbo].[tblDTSLog] (
     [ExecDate] [datetime] NULL ,
     [Message] [varchar] (200) NULL
) ON [PRIMARY]
END

---Put this after each job step
Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Process Begins step 1.')
GO
0
 
ShogunWadeCommented:
could you post the code for spiLoadProdSched_A  ?
0
 
rajaloysiousCommented:
A wild thought...
Does your DTS job do anything else other than querying the database.
If so, potential issues may be with that and not with the query.

Cheers
0
 
TRACEYMARYAuthor Commented:
Ok i just added this to the stored procedure

It is running and i get start and end on both jobs
2004-09-23 11:49:29.463      Start Load A .
2004-09-23 11:49:29.633      End  Step Load A.
2004-09-23 11:49:29.633      Exec B from Step Load A
2004-09-23 11:49:29.760      Start Load B.
2004-09-23 11:50:49.550      End of Load B
2004-09-23 11:50:49.550      Finish Step Load A

Stored procedure a calls b then cleans up stuff in A

CREATE  proc dbo.spiLoadProdSched_A AS

set nocount on

--vicky check if running

Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Start Load A .')
----------------vicky end

truncate table dbo.TempKeyRef

if exists (select * from sysindexes a,sysobjects b where a.name = 'idxTempKeyRefRecNum'
                                    and b.name ='TempKeyRef' and a.id = b.id )
            drop index TempKeyRef.idxTempKeyRefRecNum
if exists (select * from sysindexes a,sysobjects b where a.name = 'idxTempKeyRefKeyId'
                                    and b.name ='TempKeyRef' and a.id = b.id )
            drop index TempKeyRef.idxTempKeyRefKeyId

Insert into dbo.TempKeyRef(KeyId)
SELECT ID  
from VMFG..CUSTOMER_ORDER





---where status = 0

create index idxTempKeyRefRecNum on dbo.TempKeyRef(RecNum)
create index idxTempKeyRefKeyId on dbo.TempKeyRef(KeyId)

set nocount off






------------------vicky check to see if running
Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'End  Step Load A.')
----------vicky end



------------------vicky check to see if running
Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Exec B from Step Load A')
----------vicky end
Exec dbo.spiLoadProdSched_B with recompile  




truncate table dbo.TempKeyRef


------------------vicky check to see if running
Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Finish Step Load A')
----------vicky end
GO

So it gets to the end of Procedure A


.....................But the Job inside of the SQL Server has a red X against it
and gives the status of failed ?

Although it ran.

So it looks like it just does not set the Little red x to a blue icon


Wonder why not setting the little red x and doing failing message
0
 
Jay ToopsCommented:
potentially maybee the last truncate table command failed...
I wouldn't think it would continue and execute the last log insert though ..

strange

0
 
ShogunWadeCommented:
what about spiLoadProdSched_B   could this be failing what does it do ?
0
 
Jay ToopsCommented:
try this after every command put
IF @@ERROR <> 0
  Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Step XXX Reports errror')

JAY
0
 
TRACEYMARYAuthor Commented:
the stored procedure b does a lot of processing i added
the job number to the table..aswell

      9/23/2004 11:49:33 AM      30786
      9/23/2004 11:49:37 AM      35125
      9/23/2004 11:49:40 AM      35276
      9/23/2004 11:49:44 AM      35415
      9/23/2004 11:49:48 AM      35565
and 2004-09-23 11:50:49.550      End of Load B

Ok let me try adding the
IF @@ERROR <> 0
  Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Step XXX Reports errror')

part ............aswell.

Is bizarre this one ha..............
0
 
Jay ToopsCommented:
Darlin if it were easy ...
we'd all be bored to death.
 
Jay
0
 
TRACEYMARYAuthor Commented:
Smiles....................no kidding...........

I added the @@Error part just below the statements i do
an Insert into Table

i have a lot of select @value from table x
or select sum(x) from table b but i can't see these erroring ..!!...

or should i add this to every line....

Runs wonderfully......................no errors...just still red x
0
 
Jay ToopsCommented:
the Error thing is only good immediately after the SQL Statment you are checking

JAY
0
 
Jay ToopsCommented:
good call Wade
0
 
TRACEYMARYAuthor Commented:
I added the line to the 1st line on both a and b like so

CREATE  proc dbo.spiLoadProdSched_A AS
set ansi_warnings off

and guess what....................it worked....

Wow...............now i have a nice error tracking table in my stored procedure aswell.

Thank you all so much..........................i leave it tonight to see if it runs by itself rather then me just doing a start job.

How interesting and i really appreciate all your help....i will split all points equally amoung you all.

0
 
Jay ToopsCommented:
Glad it worked for you

Let us know!

Jay
0
 
ShogunWadeCommented:
one interesting thing is how a number of applications, etc behave incorrectly with warnings from SQL.   a sql warning is implemented in exactly the same way as an error but the severity is lower.  many applications ignore the severity.
0
 
TRACEYMARYAuthor Commented:
Just got it and great it ran wonderfully......just as well got a hugh part of it processing at end of month got there in the nick of time.

Thanks you all for taking the time in helping me out.

This forum is the best.
0
 
ShogunWadeCommented:
you are very welcome tracey, glad we could help.
0
 
Jay ToopsCommented:
:D yes.... glad you got it going... give us a hollar if you need more help ...

Jay
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.