Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Replication failing with error "Column name or number of supplied values does not match table definition." Expand / Collapse
Author
Message
Posted Wednesday, August 29, 2012 9:36 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
Points: 11, 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.
Post #1351714
Posted Wednesday, August 29, 2012 10:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,702, Visits: 3,411
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1351763
Posted Wednesday, August 29, 2012 10:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
Points: 11, Visits: 437
No the identity values are managed automatically at the publisher.
Post #1351766
Posted Wednesday, August 29, 2012 10:59 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,702, Visits: 3,411
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1351769
Posted Wednesday, August 29, 2012 11:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
Points: 11, 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.
Post #1351786
Posted Wednesday, August 29, 2012 11:32 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,702, Visits: 3,411
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
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1351787
Posted Wednesday, August 29, 2012 11:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:04 PM
Points: 2,702, Visits: 3,411
Are all columns being replicated?

Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1351790
Posted Wednesday, August 29, 2012 11:40 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
Points: 11, 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

Post #1351795
Posted Thursday, August 30, 2012 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 12:18 PM
Points: 11, 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?
Post #1352394
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse