locked
Snapshot Replication Failing RRS feed

  • Question

  • This is running on SQL Server 2008 R2 and has 1 push subscriber.  The job fails sporadically one day it runs with no issue the next day it fails with the errors below.  Always fails on the catalog table which only has 4 records.  Any ideas?

    SQL Server Log

    Error: 14151, Severity: 18, State: 1.

    Replication-Replication Distribution Subsystem: agent ServerName-DBName-Create Snapshots-ServerName-3 failed. The process could not bulk copy into table '"dbo"."Catalog"'.

    Replication Log:

    2012-04-25 07:31:09.729 Disconnecting from OLE DB Subscriber 'ServerName'
    2012-04-25 07:31:09.729 Bulk copying data into table 'CatalogEntryEx_PromptingProductSKU_Localization'
    2012-04-25 07:31:09.760 Bulk copying data into table 'CatalogEntryEx_PromptingProductSKU_Localization'
    2012-04-25 07:31:09.760 Bulk copying data into table 'CatalogEntryEx_PromptingProductSKU_Localization'
    2012-04-25 07:31:09.760 Bulk copying data into table 'CatalogEntryEx_PromptingProductSKU_Localization'
    2012-04-25 07:31:09.791 Agent message code 20037. The process could not bulk copy into table '"dbo"."Catalog"'.
    2012-04-25 07:31:09.823 Adding alert to msdb..sysreplicationalerts: ErrorId = 19726896,
    Transaction Seqno = 0005bd5c00002c5a01ab0000011f, Command ID = 957
    Message: Replication-Replication Distribution Subsystem: agent ServerName-DBName-Create Snapshots-ServerName-3 failed. The process could not bulk copy into table '"dbo"."Catalog"'.OLE DB Subscriber 'ServerName': exec dbo.sp_MSupdatelastsyncinfo N'ServerName',N'KnotCommerce_Admin', N'Create Snapshots', 0, 6, N'The process could not bulk copy into table ''"dbo"."Catalog"''.'
    2012-04-25 07:31:09.838 Disconnecting from OLE DB Subscriber 'ServerName'

    Wednesday, April 25, 2012 1:46 PM

All replies

  • Hi FHJJr,

    Could you please try to apply these BCP scripts under snapshot directory by manual to see what happens?

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    Thursday, April 26, 2012 2:15 AM
  • I will give that a shot.
    Sunday, May 6, 2012 10:40 PM
  • This is what I used to create the fmt file for the table

    bcp DB.Schema.tablename format nul -c -f TableName.fmt -T

    Trying to import with this command
    bcp B.Schema.tablename in "catalog_2.bcp"  -f "c:\Tablename.fmt" -T

    Getting this error:

    Starting copy...
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in
    BCP data-file

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total     : 1

    Sunday, May 6, 2012 11:25 PM
  • Can you post the contents of the failed catalog_2.bcp file here or post it to SkyDrive and provide the link?  There may be some bad characters in the bcp file.

    Also, are the collations the same on the publisher and subscriber?

    Hilary has also mentioned on here before that he has run into this when using dynamic snapshots.  Manually cleaning the dynamic snapshot folder prior to generating a dynamic snapshot solved the issue for him.


    Brandon Williams (blog | linkedin)

    Monday, May 7, 2012 1:06 AM
  • The job failure is intermittent.  The replication runs once a day and since our last failure it has run fine now for a week.  When the job fails we immediately re-run the job and it works just fine.  It always fails on the catalog table so next time it fails I will copy the BCP files for that table before I re-run the job.

    Thursday, May 10, 2012 3:45 PM
  • Sounbds like it may be a blocking contention issue with some other process having an exclusive lock on the table.  I would add a step to the snapshot job to capture contents of sys.dm_exec_requests and see what SQL is currently being executed


    Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

    Friday, May 11, 2012 1:16 PM
  • "Unexpected EOF encountered" is not blocking.

    Brandon Williams (blog | linkedin)

    Friday, May 11, 2012 4:22 PM
  • Hi you can refer it, hope it will be helpful for you .

    http://www.dbforums.com/microsoft-sql-server/925995-snapshot-replication-failing-ssl.html

    Saturday, May 19, 2012 9:54 AM