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

  • 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.

  • 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?

    Jared
    CE - Microsoft

  • No the identity values are managed automatically at the publisher.

  • 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.

    Jared
    CE - Microsoft

  • 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.

  • 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.

    Jared
    CE - Microsoft

  • Are all columns being replicated?

    Jared
    CE - Microsoft

  • 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],

    ,

    [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,

    [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,

    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

  • 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?

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply