Sunday, March 11, 2012

Custom procs for replication

SQL Server 2000 Enterprise

Hello,

I have a test publication of two tables. Both tables are supposed to use a custom update stored procedure
(you know, "Replace UPDATE commands with this stored procedure call:")
When updating Table1 the system calls my custom stored stored procedure.

Here is a small excerpt captured by the Profiler when updating Table1:

exec usp_FNLC_MSupd_Table1 'Jun 24 2005 12:00:00:000AM', NULL, 100081142, 0x01

Looks good. I see that my custom update proc was used (usp_FNLC_MSupd_Table1).

Now, when updating Table2 (Table2 is pretty identical to Table1) Profiler sends this back and the update on the subscriber fails:

exec sp_executesql N'delete from "Table2" where "Dates3ID" = @.P1 insert into "Table2" values (@.P2, @.P3) ', N'@.P1 int,@.P2 int,@.P3 datetime', 8, 8, 'Mar 24 2006 12:00:00:000AM'

Then..

exec sp_executesql N'insert into "Table1" values (@.P1, @.P2) ', N'@.P1 int,@.P2 datetime', 8, 'Mar 24 2006 12:00:00:000AM'
Go

This is what the distribution agent throws back:

Insert Error: Column name or number of supplied values does not match table definition.
(Source: DFBPBSO (Data source); Error number: 213)

So, why isn't the article Table2 using my custom update proc? All I do is updates, no deletes or inserts. No matter what I do for this article, it will not use the custom update proc.

I need help, please. Thank you in advance,
L

When you update a column that's part of a unique constraint, it will be replicated as delete/insert. I'm guessing you're updating a PK column on Table2?

You can find more info on deferred udates here:

http://support.microsoft.com/default.aspx?scid=kb;en-us;238254

|||Yes, thanks, that is what is was. I fixed it. Its working now.

No comments:

Post a Comment