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_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_WEXP_iOrder
,@.wsSTUAPP_WEXP_sEmployerName
,@.wsSTUAPP_WEXP_sJobTitle
,@.wsSTUAPP_WEXP_sJobDESC
,@.wsSTUAPP_WEXP_dStart
,@.wsSTUAPP_WEXP_dEnd
)
END
ELSE
BEGIN
UPDATE tbl_wsSTUAPP_WEXP
SET 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_dEnd
WHERE wsSTUAPP_WEXP_iID= @.wsSTUAPP_WEXP_iID
END
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
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
|||The failure may be caused by implict column list in INSERT statement. Do you replicate all the columns in the table or are there any identity column in the replicated table? You can try to explicitly specify the column list in your INSERT statement.
Hope it helps.
Peng
|||Thanks Raymond and Peng,
The problem has been sloved by the following
"use the undocumented system procedure 'sp_identitycolumnforreplication' to disable the 'not for replication' property on all the identity columns at the publisher"
I really appreciate it!
Jim
|||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,0
GO
Thank you!
No comments:
Post a Comment