HELP - Problem with Replication

Hello,

I have transactional replication running between two SQL Servers. It's a push subscription and the distributor is on the local publisher.

Recently after running for a year the replication stopped working and we get the following two errors.

1 - The process could not bulk copy into the table "History_Rep"

String Data, Right Truncation. Error Number 22001.

-

2 - The process could not bulk copy into the table "History_Rep"

Unexpected EOF encountered in BCP Data-File. Error Number S1000

The data types between the source and target tables are identical. I've tried emptying my source tables so that BCP doesn't have to send anything to the subscriber but it still fails. Do I maybe need to purge my distribution database and BCP log files? How do I go about doing that.

Thanks in advance Guys,

Hugo

Hi Hugo,

Once you have emptied the source table, you would need to reinitialize your subscription and re-generate the snapshot in order to 'remove' the bad bcp data from the system. Having said that, I can't help but suspect that there is a schema (column size) mismatch between the publisher and the subscriber based on the error message that you posted. Is this on SQL2000 (my guess) or SQL 2005?

-Raymond

|||

Raymond.

Yes, SQL Server 2000.

I've checked the column names, data types, sizes, three or four times - they're identical. How do I reinitialize the subscription - do I have to remove it and re-add it? Can it be done easily with Enterprise manager?

Thanks,

Hugo

|||

Hi Hugo,

You should be able to just right-click on your subscription node in Enterprise Manager and choose reinitialize subscription on the context manual. Does the problematic table have multiple text\image columns at the end by any chance?

-Raymond

|||

Hey Raymond,

When I right click on the subscription node, there's no option for re-initializing the subscription. I can't find that anywhere.

The table is all varchars and ints. No Text fields. There is a varchar 4000 second from the end where we put some XML data but that's it.

Thanks,

Hugo

|||

Hi Hugo,

My thinking was probably mixed up by how the SQL2005 UI is laid out, here is what worked for me using the SQL2000 Enterprise Manager:

For a well-known push or pull subscription:

1) Publisher Server->Expand Replication Folder->Expand Publications Folder->Select publication whose subscription(s) you want to reinitialize, all your subscriptions should show up on the right plane->now right-click on the subscription you want to reinitialize on the right-plane and you should be able to choose "Reinitialize" from the context menu.

For an anonymous pull subscription:

2) Subscriber Server->Expand Replication Folder->Select Subscriptions Folder, all your pull subscriptions should show up on the right-plane-> Right-click on the subscription you want to reinitialize on the right-plane and you should be able to choose "Reinitialize" from the context menu.

Hope that helps,

-Raymond

|||

Raymond,

Awesome - that worked. Not it says it's pending - why? How can I find out why it's pending - there's nothing in any logs that I can see.

Thanks for all your help,

Hugo

|||

Hey Raymond,

I also started the snapshot agent and it ran okay. But, my subscription still says pending and when I try to start the distribution agent I get the same error.

Hugo

|||

Have you restarted the snapshot agent?

-Raymond

|||

Sorry for the hasty reply, were there any rows bulk copied out by the snapshot agent for the problematic table?

-Raymond

|||

No need for apologies

Well, it said four articles were generated - I'll investigate more.

Hugo

|||

Hugo, something is not adding up correctly in your case (assuming that you have indeed deleted all data of the problematic table at the publisher), you can drop and re-add the subscription to make sure that it is not picking up a stale snapshot (I am out of ideas and my mind if that doesn't work). I am also wondering if you have different code pages between the publisher and the subscriber (or if you are using character mode snapshot) which would have caused the original bcp problem to happen. In any case, it would appear that the problem becomes extremely difficult to debug through forum postings, and so I would really appreciate if you can:

1) Open a support case with product support so they can gather sufficient information for us to investigation the issue further, or

2) Log a bug @. http://connect.microsoft.com/registration/registration.aspx with enough information (schema & data of problematic article) so we can investigate the issue properly.

Thanks much!

-Raymond

|||

Raymond,

We solved the problem last night - thanks. There was a program pumping data into the problematic table and once we shut that program down and followed your advice it worked. I think that maybe I have character mode of the snapshot going on because that has never worked. Can I change that - how?

Thanks again,

Hugo