SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Replication failing with error "Column name or number of supplied values does not match table...


Replication failing with error "Column name or number of supplied values does not match table definition."

Author
Message
ryan_xh
ryan_xh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 437
Hi all,

I have created a pull transactional replication where the replication was running fine until the following error occured for a particular table:

"Replication-Replication Distribution Subsystem:..Column name or number of supplied values does not match table definition."

I checked the table definition, the definition of the stored procedure sp_MSins_dboTable (which is trying to insert the data into the table) and also the values that are being inserted and didn't find any error. One of the columns in the destination table is an identity column and it already has "Not for replication" set to Yes. Both publisher and subscriber have sql 2008 r2, one is enterprise and one is standard edition.

I already deleted and recreated both the publication and subscription once, but the same problem is occuring for this table. Not sure what else I can do now.

Please provide your thoughts.

Thanks.
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13842 Visits: 3697
ryan_xh (8/29/2012)
Hi all,

I have created a pull transactional replication where the replication was running fine until the following error occured for a particular table:

"Replication-Replication Distribution Subsystem:..Column name or number of supplied values does not match table definition."

I checked the table definition, the definition of the stored procedure sp_MSins_dboTable (which is trying to insert the data into the table) and also the values that are being inserted and didn't find any error. One of the columns in the destination table is an identity column and it already has "Not for replication" set to Yes. Both publisher and subscriber have sql 2008 r2, one is enterprise and one is standard edition.

I already deleted and recreated both the publication and subscription once, but the same problem is occuring for this table. Not sure what else I can do now.

Please provide your thoughts.

Thanks.
Not sure if this will have anything to do with it, as it usually generates a different error... but how are you managing the range of the identity for this? Manually?

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
ryan_xh
ryan_xh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 437
No the identity values are managed automatically at the publisher.
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13842 Visits: 3697
ryan_xh (8/29/2012)
No the identity values are managed automatically at the publisher.
So, it is possible that you exceeded the range... I would change it to manual if you want to let the publisher handle it.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
ryan_xh
ryan_xh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 437
Sorry I was wrong, identity management for the article is Manual at the publisher. I also found the following:

Publisher range size = 0
Subscriber range size = 0
Range threshold percentage = 0
Maximum identity value: 2147483647

I think the maximum value should be the same at subscriber, right? The identity value that was being inserted (which gave the error) is around 70000.

Do I still need to check or change anything at the subscriber regarding this?

Thanks.
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13842 Visits: 3697
If it is set to Manual, I wouldn't worry about anything else with that. Let me go back and read your first post again and see if I missed anything to ask with the knowledge that I have.

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13842 Visits: 3697
Are all columns being replicated?

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
ryan_xh
ryan_xh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 437
Yes, I selected all tables, views, SPs, functions and did not filter anything.

I am also giving the following information, if it helps to identify the problem:

The result of sp_helparticlecolumns for the article in publisher:

column published publisher type subscriber type

CUST 1 char(15) char(15)
CODE 1 char(15) char(15)
SNID 1 char(15) char(15)
ZONE 1 char(3) char(3)
MTHD 1 char(15) char(15)
TAXID 1 char(15) char(15)
NAME 1 char(61) char(61)
ADDR1 1 char(61) char(61)
ADDR2 1 char(61) char(61)
ADDR3 1 char(61) char(61)
COUNTRY 1 char(61) char(61)
CITY 1 char(35) char(35)
STATE 1 char(29) char(29)
ZIP 1 char(11) char(11)
PHONE1 1 char(21) char(21)
PHONE2 1 char(21) char(21)
PHONE3 1 char(21) char(21)
FAX 1 char(21) char(21)
ENTDT 1 datetime datetime
UPDDT 1 datetime datetime
INTID 1 char(31) char(31)
INTSRCE 1 smallint smallint
NINTID 1 char(31) char(31)
NCODE 1 char(7) char(7)
DLID 1 char(15) char(15)
LOCNUM 1 char(11) char(11)
ERR 1 char(15) char(15)
USER1 1 char(21) char(21)
USER2 1 char(21) char(21)
UQ_DT 1 datetime datetime
UQ_ID 1 int int

The script for sp_MSins_dbotableName is the following:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create procedure [dbo].[sp_MSins_dboTable]
@c1 char(15),
@c2 char(15),
@c3 char(15),
@c4 char(3),
@c5 char(15),
@c6 char(15),
@c7 char(61),
@c8 char(61),
@c9 char(61),
@c10 char(61),
@c11 char(61),
@c12 char(35),
@c13 char(29),
@c14 char(11),
@c15 char(21),
@c16 char(21),
@c17 char(21),
@c18 char(21),
@c19 datetime,
@c20 datetime,
@c21 char(31),
@c22 smallint,
@c23 char(31),
@c24 char(7),
@c25 char(15),
@c26 char(11),
@c27 char(15),
@c28 char(21),
@c29 char(21),
@c30 datetime,
@c31 int
as
begin
insert into [dbo].[Table](
[CUST],
[CODE],
[SNID],
[ZONE],
[MTHD],
[TAXID],
[NAME],
[ADDR1],
[ADDR2],
[ADDR3],
[COUNTRY],
[CITY],
[STATE],
[ZIP],
[PHONE1],
[PHONE2],
[PHONE3],
[FAX],
[ENTDT],
[UPDDT],
[INTID],
[INTSRCE],
[NINTID],
[NCODE],
[DLID],
[LOCNUM],
[ERR],
[USER1],
[USER2],
[UQ_DT],
[UQ_ID]
) values (
@c1,
@c2,
@c3,
@c4,
@c5,
@c6,
@c7,
@c8,
@c9,
@c10,
@c11,
@c12,
@c13,
@c14,
@c15,
@c16,
@c17,
@c18,
@c19,
@c20,
@c21,
@c22,
@c23,
@c24,
@c25,
@c26,
@c27,
@c28,
@c29,
@c30,
@c31 )
end

GO

The script for the table is also given below:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Table](
[CUST] [char](15) NOT NULL,
[CODE] [char](15) NOT NULL,
[SNID] [char](15) NOT NULL,
[ZONE] [char](3) NOT NULL,
[MTHD] [char](15) NOT NULL,
[TAXID] [char](15) NOT NULL,
[NAME] [char](61) NOT NULL,
[ADDR1] [char](61) NOT NULL,
[ADDR2] [char](61) NOT NULL,
[ADDR3] [char](61) NOT NULL,
[COUNTRY] [char](61) NOT NULL,
[CITY] [char](35) NOT NULL,
[STATE] [char](29) NOT NULL,
[ZIP] [char](11) NOT NULL,
[PHONE1] [char](21) NOT NULL,
[PHONE2] [char](21) NOT NULL,
[PHONE3] [char](21) NOT NULL,
[FAX] [char](21) NOT NULL,
[ENTDT] [datetime] NOT NULL,
[UPDDT] [datetime] NOT NULL,
[INTID] [char](31) NOT NULL,
[INTSRCE] [smallint] NOT NULL,
[NINTID] [char](31) NOT NULL,
[NCODE] [char](7) NOT NULL,
[DLID] [char](15) NOT NULL,
[LOCNUM] [char](11) NOT NULL,
[ERR] [char](15) NOT NULL,
[USER1] [char](21) NOT NULL,
[USER2] [char](21) NOT NULL,
[UQ_DT] [datetime] NOT NULL,
[UQ_ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
CONSTRAINT [PKTable] PRIMARY KEY NONCLUSTERED
(
[CUST] ASC,
[CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Table] WITH CHECK ADD CHECK ((datepart(hour,[UPDDT])=(0) AND datepart(minute,[UPDDT])=(0) AND datepart(second,[UPDDT])=(0) AND datepart(millisecond,[UPDDT])=(0)))
GO

ALTER TABLE [dbo].[Table] WITH CHECK ADD CHECK ((datepart(hour,[ENTDT])=(0) AND datepart(minute,[ENTDT])=(0) AND datepart(second,[ENTDT])=(0) AND datepart(millisecond,[ENTDT])=(0)))
GO

ALTER TABLE [dbo].[Table] ADD DEFAULT (getutcdate()) FOR [UQ_DT]
GO
ryan_xh
ryan_xh
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 437
Well I tried to execute the stored procedure sp_MSins_dboTable (which was giving the error) with the same values in SSMS (with IDENTITY_INSERT ON) for the table and the values were inserted without any error. So this is very confusing, why the error is only happening when the stored procedure sp_MSins_dboTable is being run by replication agent? Can this be a permission issue?
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search