Job not working as expected - 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 2000 Forums

SQL Server Development (2000)

Job not working as expected

Author

Topic

spikev2
Starting Member

6 Posts

Posted - 2009-01-21 : 09:41:57

Hi all.

Hope the following info is clear. I basically have a job that runs 3 stored procedures.

But I am getting some strange anomalies ie Records not appearing in t2 or t3 but since disappeared. Not in t1 but were at some stage.

My gut feeling is that the t1 external update is not happening correctly if there is nothing wrong with the other procedures.

My other thought was using Exist instead of outer joins to check if records exist.

I have 4 tables

T1 Movements – Updated every 30 mins
PK on the following 2 fields
Order varchar (6)
Panel int

T2 Assignments
PK on the following fields
Order varchar (6)
Panel int

T3 Archive
Pk on following fields
Order varchar (6)
Panel int

T4 additional table info

Job is run every 30 mins which clears the data in T1 then adds it from a unix box.

My 3 step job runs as follows after the above.

1.Archive – looks at T2 . If no corresponding order in T1 then write record to T3

Code as follows:

Insert into T3 Archive
SELECT assmorder,asspanelcode,frame,clearknit,T2.priority
FROM T2 LEFT OUTER JOIN
T1 ON T2.order = T1.ORDER AND
T2.panelcode = T1.PANELCODE
WHERE (T1.ORDER IS NULL)

2.Delete – looks at t2. If no corresponding order in T1 then delete record from t2

DELETE FROM t2
FROM t2 LEFT OUTER JOIN
T1 ON t2.order = t1.order AND
T2.panelcode = t1.PANELCODE
WHERE (t1.ORDER IS NULL)

3.Add – looks at t1 if record does not exist in t2 then add it.

insert into t2
SELECT t1.MORDER, t1.PANELCODE, NULL AS frame,
dbo.SQ_MAKING_ORDERS.CLEARKNIT_DATE AS clearknit, t4.PRIORITY,GETDATE() as AssignedPriority
FROM t4 INNER JOIN
t1 ON t4.ORDER = t1.MORDER LEFT OUTER JOIN
t2 ON t1.MORDER = t2.assmorder AND
t1.PANELCODE = t2.asspanelcode
WHERE (t2.assmorder IS NULL)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-21 : 09:46:06

show some data sample to illustrate discrepancy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources