T-SQL script won't run as job - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

T-SQL script won't run as job

Author

Topic

JustTroItIn
Starting Member

2 Posts

Posted - 2010-11-18 : 14:20:51

I'm not an SQL expert and only know enough to usually get what I need done by trial and error.

I created a script to archive records from a production database into an archive database based on a date, then purge the records from the production database. The script runs fine if I run it as a Query, but not when I schedule it as a job.

When running it as a job the status shows it's running the "Start job" with a status of "In progress", but never moves on to "Execute job". It will stay in this state until I cancel the job.

I'm running SQL 2005 Standard 9.00.1406.00

My script is as follows:

STEP1:

DECLARE

@CutOffDate DATEtime;
DECLARE @CTIMEMID datetime;

BEGIN

set @CTIMEMID = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
set @CutOffDate = DATEADD (mm, -47, @CTIMEMID)

END

BEGIN

IF @CutOffDate > DATEADD(mm,-24, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot copy/archive records younger than 2 years', 16, 1)

END
END

BEGIN TRAN

INSERT INTO ArchiveDB.dbo.itemlabel
( Part_ID, Created_DateTime, Serial_ID, Parent_Serial_ID, Old_Serial_ID, Quantity, Part_Description, Part_Revision_Level, Label_Type, Label_Format, Site_ID, Location_ID,
Lot_Serial_ID, Reference_ID, Dock_Code, PO_Nbr, PO_Line, Supplier_ID, Supplier_Serial_ID, Receiver_ID, Work_Order_ID, Backflush_Operation, Backflush_Line,
Production_DateTime, Release_ID, Schedule_Type, Sales_Order_ID, Sales_Order_Line, Schedule_Reference, Shipper_ID, Shipto_ID, Customer_Name,
Customer_Part_ID, Customer_PO_Nbr, Ship_DateTime, Ship_Type, Dealer_Order, Void_DateTime, Unit_Measure, Wt_Unit_Measure, Container_Weight, Part_Weight,
UM_Conversion, KanBan_Reference_ID, Issued_DateTime, Due_DateTime, Printed_DateTime, Printed_On_Printer, Created_By_UserID, Sort_1, Sort_2,
Container_Part_ID, PKG_11, PKG_12, PKG_13, PKG_14, PKG_15, PKG_16, PKG_17, User_Field_1, User_Field_2, User_Field_3, User_Field_4, User_Field_5,
User_Field_6, User_Field_7, User_Field_8, User_Field_9, User_Field_10, User_Field_11, User_Field_12, User_Field_13, User_Field_14, User_Field_15,
User_Field_16, User_Field_17, User_Field_18, User_Field_19, User_Field_20, User_Field_21, User_Field_22, User_Field_23, User_Field_24, User_Field_25,
User_Field_26, User_Field_27, User_Field_28, User_Field_29, User_Field_30, Host_Shipper_ID, Work_Order_Reference, Work_Order_Operation, Receipt_ASN,
Created_By_Program, Created_By_ScreenID, User_Date_1, User_Date_2, User_Date_3, User_Date_4, User_Date_5, User_Date_6, User_Date_7,
bakflushtagdate, visiondate, updateddate, User_Number_2, User_Number_3, User_Number_4, User_Number_5, User_Number_6, User_Number_7, User_Number_8, User_Number_9,
leaktestcount, po_errorcode )

select Part_ID, Created_DateTime, Serial_ID, Parent_Serial_ID, Old_Serial_ID, Quantity, Part_Description, Part_Revision_Level, Label_Type, Label_Format, Site_ID, Location_ID,
Lot_Serial_ID, Reference_ID, Dock_Code, PO_Nbr, PO_Line, Supplier_ID, Supplier_Serial_ID, Receiver_ID, Work_Order_ID, Backflush_Operation, Backflush_Line,
Production_DateTime, Release_ID, Schedule_Type, Sales_Order_ID, Sales_Order_Line, Schedule_Reference, Shipper_ID, Shipto_ID, Customer_Name,
Customer_Part_ID, Customer_PO_Nbr, Ship_DateTime, Ship_Type, Dealer_Order, Void_DateTime, Unit_Measure, Wt_Unit_Measure, Container_Weight, Part_Weight,
UM_Conversion, KanBan_Reference_ID, Issued_DateTime, Due_DateTime, Printed_DateTime, Printed_On_Printer, Created_By_UserID, Sort_1, Sort_2,
Container_Part_ID, PKG_11, PKG_12, PKG_13, PKG_14, PKG_15, PKG_16, PKG_17, User_Field_1, User_Field_2, User_Field_3, User_Field_4, User_Field_5,
User_Field_6, User_Field_7, User_Field_8, User_Field_9, User_Field_10, User_Field_11, User_Field_12, User_Field_13, User_Field_14, User_Field_15,
User_Field_16, User_Field_17, User_Field_18, User_Field_19, User_Field_20, User_Field_21, User_Field_22, User_Field_23, User_Field_24, User_Field_25,
User_Field_26, User_Field_27, User_Field_28, User_Field_29, User_Field_30, Host_Shipper_ID, Work_Order_Reference, Work_Order_Operation, Receipt_ASN,
Created_By_Program, Created_By_ScreenID, User_Date_1, User_Date_2, User_Date_3, User_Date_4, User_Date_5, User_Date_6, User_Date_7,
bakflushtagdate, visiondate, updateddate, User_Number_2, User_Number_3, User_Number_4, User_Number_5, User_Number_6, User_Number_7, User_Number_8, User_Number_9,
leaktestcount, po_errorcode

FROM dbo.itemLabel
WHERE created_datetime < @CutOffDate

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN

RAISERROR ('Error occured while copying data to ArchiveDB.dbo.ItemLabel', 16, 1)

END

IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
END

----------------------

STEP2:

DECLARE

@CutOffDate DATEtime;
DECLARE @CTIMEMID datetime;

BEGIN

set @CTIMEMID = DATEADD(dd, DATEDIFF(dd,0,getdate()), 0)
set @CutOffDate = DATEADD (mm, -47, @CTIMEMID)

END

BEGIN

IF @CutOffDate > DATEADD(mm,-24, CURRENT_TIMESTAMP)
BEGIN
RAISERROR ('Cannot copy/archive records younger than 2 years', 16, 1)

END
END

BEGIN TRAN
DELETE dbo.itemlabel
where created_datetime < @Cutoffdate

IF @@ERROR <> 0
BEGIN
ROLLBACK TRAN
RAISERROR ('Error occured while deleting data from ProductionDB.dbo.itemLabel', 16, 1)
END

IF @@TRANCOUNT > 0
BEGIN
COMMIT TRAN
END

-------------------------------------------

I have tried remarking out the RAISERROR messages but it made no difference.

The owner of the job is the SA account, so I don't think security is the issue. There are other jobs that run without problem on this server.

Any assistance is appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-11-18 : 14:25:36

You really need to get to a newer build. There have been literally thousands of bug fixes since 9.00.1406.00.

Install service pack 3 plus a recent cumulative update package as a start.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

JustTroItIn
Starting Member

2 Posts

Posted - 2010-11-29 : 10:59:13

I had some downtime over the weekend and was able to patch my server. I'm now running 9.00.4311.00 SP3. I'm still having the same problem. Any help is appreciated.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-29 : 11:10:12

Create stored procedures from the scripts then run them.
I always put SPs in the database affected so
step 1 would be in ArchiveDB
step 2 probably in the other database

But that's up to you
In step one the transaction is around a single statement. The statement will be atomic - it will either succeed and update or fail, give an error and not update. The transaction doesn't do anything here.
You should have a return after a raiserror to make sure it doen't carry on processing.
Didn't 2005 have try catch blocks? I would use this to trap errors.

The owner of the job is sa but is the step being run as sa?
I would say the obvious issues are the permissions, script having an issue being embedded in a step (truncation...) or just that it is taking a long time.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources