November 24, 2006 at 4:35 am
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 subscriber 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
Greetz,
Hans Brouwer
November 27, 2006 at 8:00 am
This was removed by the editor as SPAM
December 8, 2006 at 1:47 pm
Normally I would suggest that you put the original table back with the original schema definition of the varchar(13) column, but maybe that isn't the case here.
My most common issue with sudden replication failures involved permissions - can you take a profile trace of the 2005 subscriber (or examine its security event logs) and see if the username/processes involved is failing to log in?
Also SQL should give you a more specific error somewhere, have you tried in the replication monitor or detailed event logs to get you something more specific - usually I was able to get something that was at least googleable by hunting around for a detailed error.
-- http://dbachman.blogspot.com
December 10, 2006 at 2:57 am
I managed to remedy the problem by completely deleting publication and setting it up with slightly different articleproperties. Duno why, but got me a config which works now...
Greetz,
Hans Brouwer
December 11, 2006 at 3:04 pm
Generally Replication bombs out on schema changes. You can add/drop columns but if you need to alter a column, its best to drop the article from the publication, make the change on the subscriber, and re-add the article back. This will work smooth if the table is small. Rep agents will try to reinitialize only the particular table on the subscriber side and you should be fine. If the table is huge (> few hundred million rows) then its a different scenario.
******************
Dinakar Nethi
Life is short. Enjoy it.
******************
December 11, 2006 at 4:08 pm
Another method will be add a dummy column and update... drop orignal column and add new columm...
http://www.sqlservercentral.com/columnists/pibison/alteringacolumnonareplicatedtable.asp
MohammedU
Microsoft SQL Server MVP
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply