none
Merge Replication: "Error: Column name or number of supplied values does not match table deifnition"

    Question

  • Hi There,

    We are using merge replication with pull subscription. SQL SERVER 2005

    Following is the error we getting:

     

    Error messages:

    The schema script 'ALTER PROCEDURE [dbo].[TypeMappingExternalTypesLocal_Insert] (

     @ExternalReferenceType nVarChar(400),

     @ExternalReferenceCodeTypeID int,

     @ExternalStartDate smalldatetime,

     @ExternalEndDate smalldatetime,

     @ExternalCodeDescription nVarChar(400),

     @ExternalOrderOfValue int = 0,

     @ExternalMaxAge int = 0,

     @ExternalReturnType nVarChar(100) = NULL,

     @ExternalSearchText nVarChar(400) = NULL

    )

     

    AS

     

     INSERT INTO  dExternalTypes

     VALUES (@ExternalReferenceType,

       @ExternalRefe (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)

    Get help: http://help/MSSQL_REPL-2147201001

     

    Insert Error: Column name or number of supplied values does not match table definition. (Source: MSSQLServer, Error number: 213)

    Get help: http://help/213

     

    I had a look at the table structure and also the "SP: insert command" column values to check whether they are matching or not... both are same no issues. 

     

    Here is stored procedure that we have a problem:

    set ANSI_NULLS ON

    set QUOTED_IDENTIFIER ON

    go

     

    ALTER PROCEDURE [dbo].[TypeMappingExternalTypesLocal_Insert]

    (

    @ExternalReferenceType nVarChar(400),

    @ExternalReferenceCodeTypeID int,

    @ExternalStartDate smalldatetime,

    @ExternalEndDate smalldatetime,

    @ExternalCodeDescription nVarChar(400),

    @ExternalOrderOfValue int = 0,

    @ExternalMaxAge int = 0, 

    @ExternalReturnType nVarChar(100) = NULL,

    @ExternalSearchText nVarChar(400) = NULL

    )

     

    AS

     

    INSERT INTO dExternalTypes

    VALUES (@ExternalReferenceType, 

    @ExternalReferenceCodeTypeID, 

    @ExternalStartDate, 

    @ExternalEndDate,

    @ExternalCodeDescription, 

    default, 

    @ExternalOrderOfValue,

    @ExternalMaxAge, 

    @ExternalReturnType,

    @ExternalSearchText

    )

    RETURN

    I ve gone through following link aswell to check to see if there is any issue with "Not for replication" for identity columns. The identity column 'rowguid' : 'Not for replication' is set to 'No'



    I have very basic understanding of replication so please bear with me.

    Thanks


     

    Monday, July 12, 2010 4:05 AM

Answers

  • If your table “dExternalTypes” is also part of Merge Replication then please add the column list values to your insert statement inside the proc.

     

     

    From Books Online :-

     

    Considerations for Merge Replication

     

    Merge replication uses a globally unique identifier (GUID) column to identify each row during the merge replication process. If a published table does not have a uniqueidentifier column with the ROWGUIDCOL property and a unique index, replication adds one. Ensure that any SELECT and INSERT statements that reference published tables use column lists.


    Regards Rishi Maini
    Monday, July 12, 2010 4:32 AM
  • Yes the data will be overwritten at the subscriber until and unless you choose the option to upload changes first during re-initialization  , one should always be aware of the consequences of re-initialization and the possibility of data loss at Subscriber.

     

    Now there is a high possibility that even after selecting the option to upload unsent changes first from the Subscriber during re-initialization , your sync still might fail due to bad schema change that is still sitting in the meta data , schema changes are the first to be synched before any upload or download of changes start.

     

    You might need to clear this schema change manually from the meta data and thus I would recommend that you open a Ticket with CSS (Microsoft ) and get this resolved by the experienced engineers rather than messing up the production environment and loosing the unsent changes from Subscriber.


    Regards Rishi Maini
    • Marked as answer by prad9 Wednesday, July 14, 2010 4:50 AM
    Tuesday, July 13, 2010 3:27 AM

All replies

  • If your table “dExternalTypes” is also part of Merge Replication then please add the column list values to your insert statement inside the proc.

     

     

    From Books Online :-

     

    Considerations for Merge Replication

     

    Merge replication uses a globally unique identifier (GUID) column to identify each row during the merge replication process. If a published table does not have a uniqueidentifier column with the ROWGUIDCOL property and a unique index, replication adds one. Ensure that any SELECT and INSERT statements that reference published tables use column lists.


    Regards Rishi Maini
    Monday, July 12, 2010 4:32 AM
  • I mean the insert should look something like this :-

     

    Insert into table_1 (col1,col2,col3) values (1,2,3)


    Regards Rishi Maini
    Monday, July 12, 2010 4:37 AM
  • Hi Rishi,

     

    Thanks for the quick reponse. I have tried adding column list as you said. And executed at the publisher end. Now not sure what to do. It still shows the same error. Do i need to recreate the snapshot ? or reinitialize the subscriber? 

     

    Thanks 

    Monday, July 12, 2010 5:07 AM
  • First confirm if the insert statement is valid, i.e. try to run the insert statement manually from the SSMS and see if that works fine (you need to replace or declare the variable values)/ can also run the stored proc manually from SSMS

     

    Once its confirmed that the insert statement is good enough or you are able to run the proc manually without any errors, you can mark the Subscription for re-initialization and generate a new Snapshot shot.

    I guess the old bad schema change is still in the meta data and that’s why you are still running into the issue. Re-initialization should take care of this provided that the Insert is modified accurately!


    Regards Rishi Maini
    Monday, July 12, 2010 6:10 AM
  • Ok. If i Re-Initailize and create a new snapshot will the data be overwritten at the subscriber end with the new snapshot data. I am bit worried about the data at subscriber end.

    When the replication stopped working ours users entered data on both ends. So will that data still be there? can it be sync'd?

    Monday, July 12, 2010 9:06 PM
  • Yes the data will be overwritten at the subscriber until and unless you choose the option to upload changes first during re-initialization  , one should always be aware of the consequences of re-initialization and the possibility of data loss at Subscriber.

     

    Now there is a high possibility that even after selecting the option to upload unsent changes first from the Subscriber during re-initialization , your sync still might fail due to bad schema change that is still sitting in the meta data , schema changes are the first to be synched before any upload or download of changes start.

     

    You might need to clear this schema change manually from the meta data and thus I would recommend that you open a Ticket with CSS (Microsoft ) and get this resolved by the experienced engineers rather than messing up the production environment and loosing the unsent changes from Subscriber.


    Regards Rishi Maini
    • Marked as answer by prad9 Wednesday, July 14, 2010 4:50 AM
    Tuesday, July 13, 2010 3:27 AM
  • Hi Thanks for that. It really helped. I recreated the stored procedure by specifying the column list and then re-initialized and created a snapshot. It worked fine. Its syncing now. What i am not sure is when the snapshot applied to subscriber the old data that is entered before is still there...

    very surprising..

    I called up the client to check the data, they said every thing fine. So problem solved. Thanks anyway.

    • Proposed as answer by MaDEx Tuesday, December 21, 2010 11:53 AM
    Wednesday, July 14, 2010 4:49 AM