Job failing - help!

Question

Job failing - help!

Scott-144766

SSCarpal Tunnel

Points: 4333
More actions
July 7, 2005 at 3:12 am

#66721

SQL 2k SP3
I have a DTS package which is failing but I don't understand why. I have an Execute SQL task running against a SQL Server connection called 'debtors'. The last section of this is:
DECLARE @to varchar(255)
SET @to=dbo.udf_email('paid_in_full')
IF EXISTS (SELECT pif_mem_no FROM preneed.dbo.tbl_new_pif_plans
WHERE pif_processed IS NULL AND pif_dateEXEC master..xp_sendmail @recipients=@to,
@query='SELECT p.pif_mem_no,m.mem_status FROM preneed.dbo.tbl_new_pif_plans p
INNER JOIN preneed.dbo.tbl_infor_members m ON p.pif_mem_no=m.mem_no
WHERE p.pif_processed IS NULL AND p.pif_date@subject='Policies paid in full', @attach_results='FALSE'
INSERT INTO PROCESS_LOG (Expr1) VALUES('PIF mail sent')
UPDATE preneed.dbo.tbl_new_pif_plans SET pif_processed=getdate() WHERE pif_processed IS NULL AND pif_date
--
Scott

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 1

Are you returning DTSTransformStat_NoMoreRows anywhere. I remember that it used to make my task go to OnFailure. Please let me know...

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 2

I've never even heard of it! There are a couple of ActiveX Script jobs in the package but they're only used as anchors and the code in each of them is: Main = DTSTaskExecResult_Success

--
Scott

Gogula G. Aryalingam SSCrazy Points: 2688 More actions · Answer 3

Then it must be something else. Let me think about this. But, I can't promise you an answer...

Here's another thought: Why don't you log everything which happens, onto a file or a table, and see if anything might have gone wrong. (Remeber you say that: All The SQL seems to be running correctly)

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 4

I've sorted this. One of the earlier updates in the Execute SQL task had failed due to some invalid data. I had believed that the task would stop if an error was detected but what it actually did was execute all the remaining SQL including the logging updates to the PROCESS_LOG table and only then recognise an error had occurred and move on to the OnError task.

Thanks for your help

--
Scott

PhilPacha Hall of Fame Points: 3645 More actions · Answer 5

You appear to be missing an ending parenthesis for the EXISTS,

and pif_date is not compared to anything.

IF EXISTS (SELECT pif_mem_no FROM preneed.dbo.tbl_new_pif_plans

WHERE pif_processed IS NULL AND pif_date<DATEADD(WW,-2,GETDATE()))

EXEC master..xp_sendmail

Scott-144766 SSCarpal Tunnel Points: 4333 More actions · Answer 6

The ends of some of the lines of code seem to have got cut off - my code is syntactically correct, but someone created a 27 character code where it should only have been 26 characters! I was thrown by the fact that the code carried on executing after encountering the error rather than terminating immediately with a 'Binary or string data would be truncated' error.

--
Scott