synchronization procedure as scheduled job

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

General SQL Server Forums

New to SQL Server Programming

synchronization procedure as scheduled job

Author

Topic

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-12-08 : 13:26:25

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
	SET	u.colA	=	s.colA
	,	u.colB	=	s.colB
	,	...etc
	FROM
		rept_table1	u
	,	table1	s
	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

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-12-08 : 15:00:40

This really should have been in the Developer board. I didn't realize that I was in the "New to SQL Server" board when I posted.

If someone who has rights to move this happens along, would you be so kind as to move this topic to the Developer board?

Thanks!

Daniel

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-08 : 15:36:08

how big are the tables? if they are not too big, you could do it this way:

1. bcp out the tables in source
2. disable fk constraints in dest
3. truncate tables in dest
4. bcp into dest
5. reenable constraints

that way you don't need to maintain any fancy queries for finding what's been updated, what's been deleted, etc.

of course this tactic starts to suck if the tables involved are large. for 1m rows or so it should work ok i would think, depending on how fast you need the sync to happen. bcp is pretty fast. you could also use DTS/SSIS for this, but I never use them since I like the cmd line. :}

http://www.elsasoft.org

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-12-08 : 17:26:08

Thanks for the reply, jezemine. Unfortunately the situation is a bit more complex than I stated originally. This really needs to be handled as transactions on the target table (insert/update/delete) rather than using a bulk tool, as the target table is the publisher in a transactional replication setup. Beyond that, there are triggers firing on the subscriber tables; there's a whole workflow built around this that would crumble if we truncated and repopulated the sync target tables.

Won't get into all the details of that; suffice to say that the overall setup is the best method we can use with SQL2000, and we need to use a sync method rather than bulk record processing to achieve this particular goal.

That said, can you suggest why the procedure would fail to perform the operations that work just fine when executed separately?

Regards,

Daniel

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-08 : 19:31:39

Are you saying that NONE of your steps has any effect on the target when executed from the sproc, but they ALL work when called as ad-hoc queries? I don't know what would cause that.

http://www.elsasoft.org

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-12-11 : 09:32:50

That's exactly right. None of the steps (DELETE, UPDATE, INSERT) is effective when the sproc is executed. When I execute the stored procedure from SQL Query Analyzer, the result is "The command(s) completed successfully." However, no records are deleted, updated, or inserted in the target.

The target is a table in another database, but the user who created the stored procedure, and the user executing the stored procedure, have rights on both the source and target databases. Just to test, I elevated user permissions to db_owner and tried again, but this made no difference.

I think I'll try a SQL Trace to see what is really happening (or not), and post back.

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-12-11 : 09:53:06

Well, this is interesting.

When I execute other stored procedures (tried a few at random), SQL Profiler can capture the actual SQL executed using the EventClass SP:StmtCompleted. When executing these sync procedures, this event does not seem to occur -- I simply get SQL:StmtCompleted (or BatchCompleted) with the value 'EXECUTE usp_sync_table1'.

So, the execute request is sent, but it appears that the contents of the procedure are not executed at all (which effect was observed, and is now confirmed).

Any suggestions?

Thanks,

Daniel

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-11 : 13:41:56

what you are seeing in profiler is expected behavior.

when you see a sproc call in profiler, all it shows you is the name of the sproc that was executed. It doesn't show you the body of the sproc.

http://www.elsasoft.org

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-12-11 : 13:58:16

quote:
Originally posted by jezemine

what you are seeing in profiler is expected behavior.

when you see a sproc call in profiler, all it shows you is the name of the sproc that was executed. It doesn't show you the body of the sproc.

http://www.elsasoft.org

That isn't true. You just need to add the proper events to the trace to see the statements being executed inside the stored procedure.

Tara Kizer

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2006-12-11 : 15:58:30

thanks for the correction, Tara. good to know!

http://www.elsasoft.org

CorpDirect
Yak Posting Veteran

92 Posts

Posted - 2006-12-13 : 18:12:32

I've just discovered something really wacky.

Many of these procedures were created by an automated process -- another procedure I wrote for the express purpose of generating synchronization procedures for tables -- and so weren't formatted quite as prettily as those I normally write. Though I never would have suspected formatting to have any impact, it seems that was the culprit!

To test, I created a sample sproc like so:

CREATE PROCEDURE usp_sync_table1
AS
BEGIN
	UPDATE	u
	SET	u.colA		=	s.colA
	WHERE	u.id_col	=	s.id_col
END
GO

This worked, which really tweaked my brain -- I'd taken the code right from my non-working procedure! Then I stared long and hard, and finally (feeling silly all the while) tried taking out the indentations:

CREATE PROCEDURE usp_sync_table1
AS
BEGIN
UPDATE	u
SET	u.colA		=	s.colA
WHERE	u.id_col	=	s.id_col
END
GO

Unbelievably, the procedure now failed. Somehow the formatting really does matter here.

Now, I'm self-taught, and have never heard of this requirement/issue/bug... did I just miss out by not having any formal instruction? Have I just been lucky because I try to create readable code?

Feeling like I'm on Candid Camera,

Daniel

*Edit: typo

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2006-12-14 : 03:28:28

I wonder if the End of line character that your automated procedure produces is not the one that SQL expects.

I come across this sort of thing every now and again in data. You may need CHAR(13) + CHAR(10) at the end of a line or some combination of them i.e. if that doesn't work try them the other way around or on their own

steve

-----------

Don't worry head. The computer will do all the thinking from now on.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources