Why does merge replication fail on setting a table's LOCK_ESCALATION?

Question

We're having a problem with a merge replication. Our publisher runs SQL Server 2008, while our two subscribers run 2005. Our publisher is trying to send an ALTER TABLE Foo SET (LOCK_ESCALATION) command out to our subscribers. I think I remember reading that this command is new in SQL Server 2008, and if so, it makes sense that the command would fail on our 2005 servers. Our merge replication is set up for 2005 compatibility, however.

The schema script 'if object_id(N'[dbo].[Users]') is not null exec('ALTER TABLE [dbo].[Users] SET (LOCK_ESCALATION = TABLE) ')' could not be propagated to the subscriber.

Any ideas on why our publisher would be trying to do this?

Edit: Our 2008 server's compatibility level is set to "Sql Server 2005 (90)"

Answer 1

Its a new feature in sql 2008 so not supported in 2005. Depending on how complex your setup is you may want to consider have your database run in compatibility 90 (sql 2005) to make sure you dont add sql 2008 features to your database. Have had big issues with replication of schema data ever since it came about so always a bit reticent. I always try and make it act dumb and just manage data - had to support a merge system with 32 subscribers with merge replication and had big schema issues constantly when we pushed schema changes.

That said if it works as documented it shouldn't be trying to push your lock change. Check the subscriptions are marked as sql 2005 compatible. Its likely they haven't created an auto map of the setting from 2008 to 2005 in the way they did for data types (for example)

One of the SQL dev guys blogged on the new locking types a while back

Answer 2

This occurs because the incompatibility of this instruction with sql server 2005 and aparently when I do a schema change in a table that is replicating puts this instruction in the schema changes.

There are two ways: Remove and create again the suscription, not applicable when It's in production server. Second way is go to sysmergeschemachange table in the database and delete the row that has something like this:

The schema script 'if object_id(N'[dbo].[Users]') is not null exec('ALTER TABLE [dbo].[Users] SET (LOCK_ESCALATION = TABLE) ')' could not be propagated to the subscriber.

I hope this helps.