SQL Job Agent-Record Locking issue

[bt7403]

I have a SQL job w/ 5 steps:
1. Fetch records routine and update Table A
2. run SSIS pkg.
3. delete records from Table B
4. delete records from Table A
5. delete records from Table C

Although Step 1 ran okay, update could not perform because values were not avail. at the time. However the values became avail. when step 2 started and cont'd ok through step 5. But now step 1 did not get updated the final pkg is incomplete.

How to lock tables from changing during pkg run?

 

[mrdenny]

You can't lock a table between job steps.

You would need to build logic into step 1 that waits to see if the records are there, and if not it fails the job so that the rest of the steps don't run. Then when the data is there you can restart the job and have it complete without error.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[bt7403]

How to stop job from moving to next step if no records match per FETCH routine below.
-----------------------------------------------------------
USE Process
GO
declare @lot varchar (25),@sap varchar(10), @process varchar(10)
DECLARE field_cursor CURSOR FOR
SELECT product_order, sap_no, process_order FROM TEOSLot where product_order <> ''

OPEN field_cursor

-- Perform the first fetch.
FETCH NEXT FROM field_cursor INTO @lot,@sap,@process

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0

BEGIN
update teosautodata
set sap_no=@sap, process_order=@process WHERE product_order=@lot


--copy updated recordsets to TEOSMaster table on Server, then delete same recordsets from TeosAutoData.
IF EXISTS(SELECT * FROM Teosautodata WHERE product_order=@lot and process_order=@process and sap_no=@sap)
insert into teosmaster (Logtime,systemmode,TE_601,TE_351,PIT_350,TE_504,TE_502,TE_358,TE_352,TE_353,LIT_450,LIT_350,LIT_451,LIT_452,LIT_453,TE_362,TE_355,TE_356,sap_no,product_order,process_order)
SELECT Logtime,systemmode,TE_601,TE_351,PIT_350,TE_504,TE_502,TE_358,TE_352,TE_353,LIT_450,LIT_350,LIT_451,LIT_452,LIT_453,TE_362,TE_355,TE_356,sap_no,product_order,process_order FROM Teosautodata where sap_no=@sap and process_order=@process and product_order=@lot


--copy updated recordsets to TEOSMastertemp table on Server, then delete same recordsets from TeosAutoData.
IF EXISTS(SELECT * FROM Teosautodata WHERE product_order=@lot and process_order=@process and sap_no=@sap)
insert into teosmastertemp (Logtime,systemmode,TE_601,TE_351,PIT_350,TE_504,TE_502,TE_358,TE_352,TE_353,LIT_450,LIT_350,LIT_451,LIT_452,LIT_453,TE_362,TE_355,TE_356,sap_no,product_order,process_order)
SELECT Logtime,systemmode,TE_601,TE_351,PIT_350,TE_504,TE_502,TE_358,TE_352,TE_353,LIT_450,LIT_350,LIT_451,LIT_452,LIT_453,TE_362,TE_355,TE_356,sap_no,product_order,process_order FROM Teosautodata where sap_no=@sap and process_order=@process and product_order=@lot

-- This is executed as long as the previous fetch succeeds.


FETCH NEXT FROM field_cursor
INTO @lot,@sap,@process

END

CLOSE field_cursor
DEALLOCATE field_cursor
GO

 

[mrdenny]

This fetch cursor won't stop the job from running if there are no records to processes. The job step will complete successfully.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005) / MCITP Database Administrator (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Catadmin]

You can use the new Try..Catch code to set up an error check if no records are available. If no records are available, set the property of a variable to 0 (for instance) and do a conditional & Expression precedence constraint for the next step of the package which says, if 0 and success, then send an email failure. Otherwise, if 1 and success, go to the other steps of the package.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"