none
Replication error (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

    Question

  • Hi,

    In our data replication(log transaction) between the publisher and subscriber, a particular datatable will lead to this error:
    The process could not bulk copy into table '"dboABC"."TableA"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
    If I don't include the TableA in the replication, the the replication goes just fine.
    How do I fix this problem?
    Thanks for help.

    Jason

    Friday, November 30, 2012 7:42 AM

All replies

  • Hello,

    You'll probably get more help from the replication forum as this is a direct question about it and not high availabilty or disaster recovery. Moderator, please move this thread to the replication forum.

    -Sean


    Sean Gallardy | Blog | Twitter

    Friday, November 30, 2012 1:47 PM
  • Hi Jason,

    There should be additional messages shortly after this error in the Distribution Agent log.  Increase the -HistoryVerboseLevel to 3, try again, and post the additional messages back here.  You can also output the verbose agent log to file using -OutputVerboseLevel 2 -Output C:\TEMP\distributionagent.log.


    Brandon Williams (blog | linkedin)

    Monday, December 03, 2012 6:11 PM
  • Thanks.
    The error message advised me to do:
    bcp "DB1"."dboA"."TableE" in "D:\ReplData\unc\DBServer_DB1_DB1PUBLICATION2\20121204101831\TableE_2.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SDBServerB -T -w
    I run that command in Dos promt, PowerShell, and SQL Analyzer, still error only.

    Tuesday, December 04, 2012 2:51 AM
  • What does sp_helparticle return for the article in question?  Specifically, what gets returned for pre_creation_cmd in the result set?  You will want to make sure this is set to drop.

    Brandon Williams (blog | linkedin)

    Tuesday, December 04, 2012 3:11 AM
  • Thanks a lot.
    The sp_helparticle DB1PUBLICATION2 returns with:

    1 TableE [dboA].[TableE] TableE [dbo].[syncobj_0x3033333634423346] 1 9 NULL  CALL [dbo].[sp_MSins_dboATableE] SCALL [dbo].[sp_MSupd_dboATableE] CALL [dbo].[sp_MSdel_dboATableE] NULL 0 1 NULL 0x000000000803509F dboA dboA 
    TableE dbo syncobj_0x3033333634423346 NULL NULL 0 NULL NULL NULL 0 0

    The pre_creation_cmd returns 1.

    Tuesday, December 04, 2012 3:28 AM
  • That is good.

    Between the error "The process could not bulk copy into table..." and "bcp "DB1"."dboA"."TableE" in "D:\ReplData\unc\DBServer_DB1_DB1PUBLICATION2\20121204101831\TableE_2.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SDBServerB -T -w", there should be more information.

    For example:

    Error messages:
    The process could not bulk copy into table '"dbo"."Test"'. (Source: MSSQL_REPL, Error number: MSSQL_REPL20037)
    Get help: http://help/MSSQL_REPL20037
    Batch send failed
    The query has been canceled because the estimated cost of this query (1018256) exceeds the configured threshold of 3000. Contact the system administrator. (Source: MSSQLServer, Error number: 8649)
    Get help: http://help/8649
    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 "TestDB"."dbo"."Test" in "F:\MSSQL11.MSSQLSERVER\MSSQL\ReplData\unc\SrcServer_TESTDB_TESTDB_TEST\20120710142457\Test_2.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SDestServer -T -w (Source: MSSQLServer, Error number: 20253)

    This example shows the process could not bulk copy because of the query governor cost limit.  You should find additional information in your log such as this.


    Brandon Williams (blog | linkedin)

    Tuesday, December 04, 2012 3:39 AM
  • Thanks again.
    There is a "Data Transform failed" error in between.

    Tuesday, December 04, 2012 5:33 AM
  • Which version of SQL Server are you using and can you post the exact message here please?

    Brandon Williams (blog | linkedin)

    Tuesday, December 04, 2012 5:58 AM
  • The "Data Transform failed" should be "Data conversion failed".
    Tuesday, December 04, 2012 6:32 AM
  • Are you using compatible collation and code page for publisher, distributor, and subscriber?

    Brandon Williams (blog | linkedin)

    Tuesday, December 04, 2012 6:41 AM
  • Thanks.
    The distributor and publisher are the same machine.
    The publisher and subscriber are using the same collation, and actually the DB1 of the subscriber is the restoration from publisher.
    Without the TableE on in the articles, the replication works fine.  But as long as the TableE is included in the articles, replication failed.
    Tuesday, December 04, 2012 7:22 AM
  • Try starting from an empty database at the subscriber and see if that helps.

    Brandon Williams (blog | linkedin)

    Tuesday, December 04, 2012 7:30 AM
  • I reconfigured the replication, the replication still stuck at TableE.
    I run the command in DOS as it suggests:

    bcp "DB1"."dboA"."TableE" in "D:\ReplData\unc\DBServer_DB1_DB1PUBLICATION2\20121204101831\TableE_2.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SDBServerB -T -w

    and it returns with
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unable to open BCP error file

    Monday, December 17, 2012 2:12 AM
  • I believe the bcp is failing due to your -e "errorfile" parameter.  This needs to be a full path, "errorfile" is not a full path.

    Also, did you try starting from an empty Subscriber database, rather than a restoration from the publisher?


    Brandon Williams (blog | linkedin)

    Monday, December 17, 2012 2:19 AM
  • Thanks a lot.
    I created an errorfile.txt under D:\ReplData and use the full path to execute bcp command.  I won't give me any error.  However, 0 rows was copied!

    On the subscriber database, I use the recent bak file from DBServer to retore that database on DBServerB.  And TableE has a column which is an image data type.

    Monday, December 17, 2012 2:46 AM
  • Do not use a recent bak file from DBServer.  Use an empty database at the Subscriber and see if that helps.

    Brandon Williams (blog | linkedin)

    Monday, December 17, 2012 2:49 AM
  • I droped that database on ServerB, created a new db, and also deleted that replication/subscription.  I reconfigered the Publication/subscription.  However the replication still stuck at the TableE.
    The error has some word as:
    '(null)' OLE DB 'STREAM' '[!BulkInsert].Noten'。 ( MSSQLServer,: 7339)


    Monday, December 17, 2012 3:54 AM
  • What version of SQL Server is the Publisher?

    What version of SQL Server is the Distributor?

    What version of SQL Server is the Subscriber?

    What is the collation at Publisher, Distributor, and Subscriber?

    I believe it is a data issue of some kind, perhaps some invalid data.

    Try setting the Snapshot format to 'character'.  Right-click the publication -> Properties, Snapshot page, select 'Character' and save.  Then generate a new snapshot and initialize the subscription with that.


    Brandon Williams (blog | linkedin)

    Monday, December 17, 2012 5:00 AM
  • Thanks a lot.
    Publisher=Distributor:
    Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64)  
    Jul  9 2008 14:17:44  
    Copyright (c) 1988-2008 Microsoft Corporation 
    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
    SQL_Latin1_General_CP1_CI_AS

    Subscriber:
    Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)  
    Jun 17 2011 00:54:03   Copyright (c) Microsoft Corporation 
    Standard Edition (64-bit) on Windows NT 6.1 <X64>
    (Build 7601: Service Pack 1) (Hypervisor)
    Chinese_Taiwan_Stroke_CI_AS

    Monday, December 17, 2012 5:26 AM
  • Does 'Character' snapshot format solve the problem?

    Right-click the publication -> Properties, Snapshot page, select 'Character' and save.  Then generate a new snapshot and initialize the subscription with that.


    Brandon Williams (blog | linkedin)

    Monday, December 17, 2012 5:34 AM
  • Also, can you try having the same collation on both Publisher and Subscriber?

    Brandon Williams (blog | linkedin)

    Monday, December 17, 2012 5:42 AM
  • Thanks a lot.
    Even using the 'Character' snapshot format, the replication on TableE still not working.
    I'm working on changing collation of ServerB.
    The DB1 on both servers have same collation, but the master on both server are different!
    Monday, December 17, 2012 6:10 AM
  • Thanks a lot.
    I'm thinking using the master.bak backup file from ServerA to restore Master on the ServerB.  The ServerB is a development db server at this moment, and has fewer datbases/logon accounts than ServerA.
    Both ServerA and ServerB using MSSQLServer instance name.
    Cause changing collation on ServerB requires rebuilding Master, the restoration from A to B will let ServerB's master have same collation as ServerA's master.
    But I'm not sure doing so will result in disaster for ServerB.
    Monday, December 17, 2012 8:51 AM