Replication problem with stored procedure
I am working on a transaction replication, i have no problem with all the tables, but when adding the stored procedures, i got the error message:
Insert Error: Column name or number of supplied values does not match table definition.
The first column - wsSTUAPP_WEXP_iID 's "identity incremnet" definition of table tbl_wsSTUAPP_WEXP is set to 1.
The system works well in the publisher server, and the table itself has no problem for replication, but when adding the stored procedure to the replication article, the problem happens.
Please advise how to slove this problem. Below is the part of the store procedure and the error message:
THANK YOU IN ADVANCE!
STOREd PROCEDURE:
ALTER
PROCEDURE [dbo].[sp_wsSetSTUAPPWEXP]@wsSTUAPP_WEXP_iID
int, @wsSTUAPP_iIDint, @wsSTUAPP_WEXP_iOrderint, @wsSTUAPP_WEXP_sEmployerNamevarchar(75), @wsSTUAPP_WEXP_sJobTitlevarchar(75), @wsSTUAPP_WEXP_sJobDESCvarchar(360), @wsSTUAPP_WEXP_dStartdatetime, @wsSTUAPP_WEXP_dEnddatetimeAS
IF
NOTEXISTS(SELECT wsSTUAPP_WEXP_iIDFROM tbl_wsSTUAPP_WEXPWHERE wsSTUAPP_WEXP_iID= @wsSTUAPP_WEXP_iID)BEGIN
INSERTINTO tbl_wsSTUAPP_WEXPVALUES(@wsSTUAPP_iID
,@wsSTUAPP_WEXP_iOrder,@wsSTUAPP_WEXP_sEmployerName,@wsSTUAPP_WEXP_sJobTitle,@wsSTUAPP_WEXP_sJobDESC,@wsSTUAPP_WEXP_dStart,@wsSTUAPP_WEXP_dEnd)END
ELSE
BEGIN
UPDATE tbl_wsSTUAPP_WEXPSET wsSTUAPP_WEXP_iOrder= @wsSTUAPP_WEXP_iOrder,wsSTUAPP_WEXP_sEmployerName= @wsSTUAPP_WEXP_sEmployerName,wsSTUAPP_WEXP_sJobTitle= @wsSTUAPP_WEXP_sJobTitle,wsSTUAPP_WEXP_sJobDESC= @wsSTUAPP_WEXP_sJobDESC,wsSTUAPP_WEXP_dStart= @wsSTUAPP_WEXP_dStart,wsSTUAPP_WEXP_dEnd= @wsSTUAPP_WEXP_dEndWHERE wsSTUAPP_WEXP_iID= @wsSTUAPP_WEXP_iIDEND
ERROR MESSAGE:
Command attempted:
CREATE PROCEDURE "dbo"."sp_wsSetSTUAPPWEXP"
@wsSTUAPP_WEXP_iID int
, @wsSTUAPP_iID int
, @wsSTUAPP_WEXP_iOrder int
, @wsSTUAPP_WEXP_sEmployerName varchar(75)
, @wsSTUAPP_WEXP_sJobTitle varchar(75)
, @wsSTUAPP_WEXP_sJobDESC varchar(360)
, @wsSTUAPP_WEXP_dStart datetime
, @wsSTUAPP_WEXP_dEnd datetime
AS
IF NOT EXISTS(SELECT wsSTUAPP_WEXP_iID FROM tbl_wsSTUAPP_WEXP WHERE wsSTUAPP_WEXP_iID= @wsSTUAPP_WEXP_iID)
BEGIN
INSERT INTO tbl_wsSTUAPP_WEXP
VALUES(
@wsSTUAPP_iID
,@wsSTUAPP_WE
(Transaction sequence number: 0x00006D8E00000160000B00000000, Command ID: 12)
Error messages:
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)
Get help: http://help/213
[6240 byte] By [
Jimdba] at [2007-12-22]
Hi Jim,
You are likely to be hitting the same issue as discussed in the following forum thread:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=493307&SiteID=1
and this is basically the unintended consequence of the interaction among the following factors:
1) Replicated identity columns are marked "not for replication" by default at the publisher when you setup replication through SSMS in SQL2005
2) Any insert statements into a table with an identity column marked 'not for replication' executed from a replication agent (distribution agent for example) must supply an explicit value for the identity column
3) In SQL2005, the server verifies the validity of insert statements in stored procedures and triggers during the creation of these objects and raises error if it determines that an embedded insert statement does not supply all the required values.
In more concrete terms, 1) will basically lead to all identity columns being marked 'not for replication' at both the publisher and the subscriber and given that your replicated stored procedure is going to be created through a distribution agent connection, 2) and 3) will lead the subscriber server into thinking that the insert statement embedded in your stored procedure would need to supply an explicit value for the identity column marked 'not for replication' and so it raises an error when an explicit identity value is found missing. Now, the stored procedure that you are replicating is probably intended to be executed outside of replication even though it is created through replication so the check is technically invalid despite the good intentions behind it.
The only workaround that I can think of is to use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher and then re-initialize your subscriber using a new snaphsot. This is admittedly not a very good workaround but the good news is that this particular problem is fixed for snapshot processing in SP2. The bad news is that the same problem applies to our ddl replication logic (alteration of stored procedure e.g.) and we don't have a general solution in that part of replication yet. My suggestion would be to separate your schema objects (stored procedures) into a separate snapshot publication so changes to your stored procedures etc. can be refreshed through snapshot processing when SP2 comes out.
Hope that hope,
-Raymond
Hello Raymond,
I failed to use 'sp_identitycolumnforreplication' to disable the 'not for replication' property on the identity columns. However, it was succeessful before. But it doesn't work any more.
I tried to restore the database, and re-install SQL server, no use.
How to fix 'sp_identitycolumnforreplication' ?
Here is my query:
declare @int int
set @int =object_id('tbl_wsSTUAPP_WEXP')
exec
sys.sp_identitycolumnforreplication @int,0GO
Thank you!
