Does replication alter schema?

Question

Does replication alter schema?

Kwisatz78

SSChampion

Points: 12478
More actions
June 1, 2012 at 2:13 am

#258954

Hi all
We have a transactional replication setup on various articles. Yesterday when re-initialising our app produced an error of:
Could not complete cursor operation because the table schema changed after the cursor was declared
It looks likely that this was because of the replication but I am trying to understand why this would be the case.
Would anyone be able to help?
Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 1

Replication doesn't just change schema randomly.

It would require someone to modify it by releasing a change or editing the table structure through ssms. Might be worth checking the default trace at around the time the error occurred if its still available.

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 2

Did you set up replication recently? It does require a PK of some sort on tables, and I believe it adds a column if there isn't one already.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

Kwisatz78 (6/1/2012)
... Yesterday when re-initialising our app produced an error of:
Could not complete cursor operation because the table schema changed after the cursor was declared
...

Which server is throwing this error? Publisher or Subscriber?

If it is subscriber, then it is the expected (default) behavior.

When you re-initialize the subscriber, on the subscriber all the aricles are deleted and recreated.

If you want, you can change the article properties to not to delete existing objects.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

Steve Jones - SSC Editor (6/1/2012)
Did you set up replication recently? It does require a PK of some sort on tables, and I believe it adds a column if there isn't one already.

Transaction replication does not allow to pick a table if it does not have PK.

I believe merge replication prompts to add a timestamp column.

Kwisatz78 SSChampion Points: 12478 More actions · Answer 5

Hi all thanks for the replies. No this was not a recent replication, although was being re-initialised at the time. It happened at the Publisher so I am still confused as to how this could happen. Maybe it was a co-incidence and was actually something else.

I have looked and someone else had exact same problem here:

http://www.sqlservercentral.com/Forums/Topic919519-1550-1.aspx

Although no explanation as to what it was exactly.