none
Replication sync problem

    Question

  • Hello all,

    We are having problem setting up transactional replication for one database. The sync at the subscriber is failing with the following error:

    "Column name or number of supplied values does not match table definition."

    I already matched the source and destination tables and didn't find anything different. The only difference I found is in the actual command (which is giving the error) in the Distribution database with the same command that I captured with SQL profiler.

    The following I found from the profiler (when the command executed against the subscription database):

    exec [dbo].[sp_MSins_dbotable] '1234 ','Address ',' ',' ',' ','

    ', ','2011-01-01 00:00:00'

    The following I found from the distribution database using sp_browsereplcmds:

    {CALL [dbo].[sp_MSins_dbotable] '1234 ','Address ',' ',' ',' ','

    ',' ',2011-01-01 00:00:00.000

    So the date data in the actual command in distribution database has milliseconds, but the command that executed against the subscription database does not have it. Can this be the problem? The collation of subscriber and publisher is same. Could you please provide some suggestions on this.

    Thanks in advance.

    Thursday, November 08, 2012 4:12 PM

Answers

All replies

  • That is not the problem. Can you open up the proc and verify that it has the same number of parameters as the execution statement.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, November 08, 2012 4:18 PM
    Moderator
  • The procedure sp_MSins_dbotable was created automatically when I set up the replication. I also checked the number of parameters and it is fine. I recreated this subscription 3 times and its giving the same error. Previously we had a problem with the Distribution database, as its compatibility level was 90 (the sql server is 2008 r2), but I recreated the Distribution database by running the "disable publishing and distribution" wizard, but the sync fails with same problem.

    Is there any other server or database level setting that I should match?

    Thanks. 

    Thursday, November 08, 2012 4:30 PM
  • Can I see the schema for this table?

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, November 08, 2012 4:35 PM
    Moderator
  • Hilary,

    I am giving the schemas for the article, the procedure and the destination table below.

    The result of sp_helparticlecolumns:

    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_dbotable 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 destination table:

    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

    Thursday, November 08, 2012 4:45 PM
  • Can  you try this:

    declare @artid int
    select @artid =artid From sysarticles where dest_table='Table1'
    exec sp_scriptinsproc @artid

    replace table1 with your actual table name.

    Then copy the output and paste it on your subscriber.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thursday, November 08, 2012 5:55 PM
    Moderator
  • Thanks Hilary.

    The thing is that when I created the transactional replication, the subscription was initialized from a backup file of the publication database (so the schema of the subscriber database should be exactly same). Do you still think the solution you provided might fix this problem?

    I do not have the replication running at the moment as I had to delete the subscription due to lack of disk space. But I will create the subscription again and try your solution if you think the solution might fix this problem.

    Thanks.  

    Wednesday, November 21, 2012 2:25 PM
  • They should be identical. I am guessing there was a schema change somewhere in the mix which caused this inconsistency. Regenerating the proc can help.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, November 21, 2012 2:34 PM
    Moderator
  • I will try again and use the solution that you provided.

    Thanks.

    Wednesday, November 21, 2012 2:41 PM