Sudden replicationproblem, unsolvable?

[Freehansje]

Hi,
We're running a replicationprocess for months now, and 2 days ago it broke down, for no appearant reason I can detect. The setup is this:
A SQL2K server has a publication defined on a database, consisting of several dozen tables. It is a transactional publication, running continuously. There is 1 subscriber, a SQL2005. Both servers run with Win2003 and all have the latest servicepacks.
Up until 2 days ago there was hardly a problem. Then I received errormessages on being unable to load into a specific table. That table had been changed on that day, namely, 1 column was changed from CHAR(13) to CHAR(12). I do not know if this has any relation with the problem we experience.
Currently the databases are structural the same. I could not get the replicationprocess to work and deleted all, publication on server1 and subscription on server2. I tried to setup a new transactional publication, which is not a problem, however, I cannot create a succesful subscription. I receive the message:

The process could not bulkcopy into table 'Tablename', where tablename is the changed table...

Again, both tables in publisher and subscruiber are the same in all respects. When I remove the offending table from the publication, the same message now with another tablename... Removing this table will result in again this message with another tablename...

I am really flabbergasted. Any idea where to look at?

TIA
FreeHansje

 

[philhege]

You say that a column definition was changed. Did you remove replication to make the change? You're not supposed to alter the schema of tables currently participating in replication.

The right thing to do is to remove the table from the publication, alter it in both the publisher and subscriber, and then add the article (table) back into the publication.

From your current situation, it sounds like you need to re-create the pub in its entirety. Make absolutely sure the publisher and subscriber DB schema are identical, using a tool like Red Gate or rolling your own schema comparison SP.

Good luck.

Phil Hegedusich
Senior Programmer/Analyst
IIMAK

http://www.iimak.com

-----------
I'll have the roast duck with the mango salsa.

 

[Freehansje]

Hi Phil,
Tnx for answering. I did re-create the publishing from scratch, after recreating the targetDB scripted from the publishing db. Same problem. And I use Redgate...
Still looking, but I really am flabbergasted

 

[Catadmin]

Schema changes to columns can actually be made in the publication properties, but then all the subscriptions have to be reinitialized and re-pushed.

But since you're past that point, try this:

Remove the subscription, remove the publication, in fact, remove (in reverse order of setup) all the replication components, including the distributor. Then truncate all the tables on the subscription DB. Re-install replication in the proper order, tell it to recreate the tables in the subscription DB via snapshot and then let it run. If this doesn't work, try removing the tables in the subscription DB and run the initial snapshot to start replication.

I know it's a lot of work, but it sounds like replication is well and truly mucked-up. And if you're using SQL 2000, once it's so screwed up everything is failing, you almost have to rebuild it from scratch to get it to work properly.



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"