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.