none
SQL replication issue

    Question

  • Hi All,

    I am using oracle publisher and sql server distributer and subscriber and it was running fine until yesterday.it is giving the below error while replicating data from Distributer to subscriber.

    Error messages:
    The process could not bulk copy into table '"dbo"."MPI_BREAK_TYPE"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
    Get help: http://help/MSSQL_REPL20037
    Invalid object name 'dbo.MPI_BREAK_TYPE'. (Source: MSSQLServer, Error number: 208)
    Get help: http://help/208
    To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options. (Source: MSSQLServer, Error number: 20253)
    Get help: http://help/20253
    bcp "FlexNet"."dbo"."MPI_BREAK_TYPE" in "G:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\ReplData\unc\MESD01_DISTRIBUTION_MPI_DEV\20140401230725\BREAK_TYPE_2.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SCIDCSWDMESMPD01 -T -w (Source: MSSQLServer, Error number: 20253)
    Get help: http://help/20253

    The table is earlier present but while replicating the table is getting invalid so i am not able to replicate the table to the subscriber.Kindly help me out as my production is down because of it.

    Awaiting for your response.

    Wednesday, April 02, 2014 3:18 AM

Answers

  • Hi Snehasis pattnaik,

    According to your description and error message, there are some troubleshooting steps which you can review them.

    1.Confirmed that the “BREAK_TYPE_2.bcp” file is present under the snapshot folder. Pull the table script form the BCP file and tried to create the table manually on the Subscriber.

    2.These errors may also occurs because there was already a primary key name on another table and replication was trying to add another PK constraint with the same name on the table “MPI_BREAK_TYPE”.

    3. We delete all the tables from subscriber that were part of replication and regenerated a new snapshot along with the new subscriptions check if go fine without any issue.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Thursday, April 03, 2014 7:49 AM
    Moderator

All replies

  • Can you obtain the script for the table, recreate it, and then restart the agent? It looks like it has dropped the table and then is trying to bcp into it.

    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, April 02, 2014 3:34 PM
    Moderator
  • But in the article option i am using drop existing object and create new one..but still its giving the above error.

    Wednesday, April 02, 2014 4:08 PM
  • Hi Snehasis pattnaik,

    According to your description and error message, there are some troubleshooting steps which you can review them.

    1.Confirmed that the “BREAK_TYPE_2.bcp” file is present under the snapshot folder. Pull the table script form the BCP file and tried to create the table manually on the Subscriber.

    2.These errors may also occurs because there was already a primary key name on another table and replication was trying to add another PK constraint with the same name on the table “MPI_BREAK_TYPE”.

    3. We delete all the tables from subscriber that were part of replication and regenerated a new snapshot along with the new subscriptions check if go fine without any issue.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Thursday, April 03, 2014 7:49 AM
    Moderator