Synchronization Procedure As Scheduled Job

Hi folks,

In an environment where replication is not possible (enabling replication breaks a proprietary application), I need to synchronize data from tables in a production database to a reporting database.

So far I have tried creating a procedure which does this in three stages (see below). While each of the stages works when executed separately, none of them seem to execute (at least nothing is effected) when the procedure is executed.

Here's an illustration of the stages. If anyone needs sample objects and data, please let me know and I'll whip some up.


CREATE PROCEDURE usp_sync_table1
AS

-- remove deleted records from target
BEGIN
DELETE FROM rept_table1
WHERE
id_col NOT IN (SELECT id_col from table1)
END

-- update existing records that have changed
BEGIN
UPDATE u
SETu.colA=s.colA
,u.colB=s.colB
,...etc
FROM
rept_table1u
,table1s
WHERE
s.modify_date > u.modify_date
END

-- add new records
BEGIN
INSERT INTO rept_table1 (
colA
,colB
,...etc)
SELECT
colA
,colB
,...etc
FROM
table1
WHERE
id_col NOT IN (SELECT id_col FROM rept_table1)
END


That's the basic idea. Each block works fine executed alone, outside the stored procedure. The procedure executes successfully, but no records are effected. Once I can get this to work I want to schedule a job to execute the procedure at a specific interval (there will actually be several, some executing hourly and some nightly).

Thanks in advance for any suggestions!

Regards,

Daniel