Merge Replication SQL Server 2008->2005 fails SET (LOCK_ESCALATION = TABLE)' could not be propagated to the subscriber - by Chris Wruck

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


6
0
Sign in
to vote
ID 536571 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 2/25/2010 4:26:53 PM
Access Restriction Public

Description

We have just setup a merge replication between SQL Server 2008 (publisher) and a SQL Server 2005 (subscriber) database. After a schema change on the publisher, the merge replication agent is failing with the error below as seen in the replication monitor:-

The schema script 'if object_id(N'[dbo].[OrderPrintQueue]') is not null exec('ALTER TABLE [dbo].[OrderPrintQueue] SET (LOCK_ESCALATION = TABLE)
')' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Incorrect syntax near the keyword 'SET'. (Source: MSSQLServer, Error number: 156)

This appears to be caused by an invalid SET command being issued to the subscriber as the SET (LOCK_ESCALATION = TABLE) is not supported in SQL Server 2005. This in turn would mean that replication between SQL Server 2008 and SQL Server 2005 is broken. 

Compatibility level is set to 90 on the publisher database, with replication compatibility set to SQL Server 2005 or above. Therefore it appears as if SQL Server 2008 replication agent is not honoring the compatibility level to ensure SQL commands being generated are compatible.
Sign in to post a comment.
Posted by DomBat on 5/16/2011 at 1:09 AM
When is 2008 R2 Sp1 being released? This bug is from 2010, it's now May 2011 and not fixed in our version!!!!
Posted by Microsoft on 12/3/2010 at 10:06 AM
Hi Chris,

thanks for taking the time to share your feedback, this is really important to us.
This issue will be fixed in next major release of SQL Server.

Best regards
Jean-Yves Devant
Program Manager (Replication/CDC/CT)
SQL Server Engine Team

Posted by JenDB on 4/22/2010 at 1:09 PM
We are having a similar issue with fulltext indexes. It is scripting the fulltext index in SQL 2008 syntax instead of SQL 2005. Transactional replication between SQL 2008 publisher and SQL 2005 subscriber correctly replicates fulltext indexes, but not in merge replication.

The schema script 'ArticleIndex_32.dri' could not be propagated to the subscriber. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001
Incorrect syntax near '('. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon. (Source: MSSQLServer, Error number: 319)
Posted by Microsoft on 4/5/2010 at 9:29 AM
Thanks for reporting the issue. We are investigating.
Posted by imannoee on 2/25/2010 at 6:53 PM
f