Solved

query runs job but dts fails

Posted on 2004-09-23
23
2,536 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:TRACEYMARY
  • 9
  • 6
  • 6
  • +2
23 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12133499
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
 
LVL 10

Assisted Solution

by:Jay Toops
Jay Toops earned 300 total points
ID: 12133761
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12133795
"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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12133847
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12133887
--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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12133889
could you post the code for spiLoadProdSched_A  ?
0
 
LVL 8

Expert Comment

by:rajaloysious
ID: 12133965
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12134565
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12134657
potentially maybee the last truncate table command failed...
I wouldn't think it would continue and execute the last log insert though ..

strange

0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12134793
what about spiLoadProdSched_B   could this be failing what does it do ?
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12134806
try this after every command put
IF @@ERROR <> 0
  Insert Into tblDTSLog
(ExecDate, Message)
Values
(getdate(), 'Step XXX Reports errror')

JAY
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12134865
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12134904
Darlin if it were easy ...
we'd all be bored to death.
 
Jay
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12134996
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12135019
the Error thing is only good immediately after the SQL Statment you are checking

JAY
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 200 total points
ID: 12135106
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12135146
good call Wade
0
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12135684
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
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12135692
Glad it worked for you

Let us know!

Jay
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12135713
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
 
LVL 7

Author Comment

by:TRACEYMARY
ID: 12141805
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12141910
you are very welcome tracey, glad we could help.
0
 
LVL 10

Expert Comment

by:Jay Toops
ID: 12142332
:D yes.... glad you got it going... give us a hollar if you need more help ...

Jay
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now