How to: Set the Compatibility Level for Merge Publications (Replication Transact-SQL Programming)

The compatibility level for a merge publication can either be set programmatically when a publication is created or modified programmatically at a later time. You can use replication stored procedures to set or change this publication property. For more information about publication compatibility level and related restrictions and requirements, see the "Compatibility Level for Merge Publications" section in the topic Using Multiple Versions of SQL Server in a Replication Topology.

To set the publication compatibility level for a merge publication

• At the Publisher, execute sp_addmergepublication (Transact-SQL), specifying a value for @publication_compatibility_level to make the publication compatible with older versions of Microsoft SQL Server. For more information, see How to: Create a Publication (Replication Transact-SQL Programming).

To change the publication compatibility level of a merge publication

• Execute sp_changemergepublication (Transact-SQL), specifying publication_compatibility_level for @property and the appropriate publication compatibility level for @value.

To determine the publication compatibility level of a merge publication

1. Execute sp_helpmergepublication (Transact-SQL), specifying the desired publication.

2. Locate the publication compatibility level in the backward_comp_level column in the result set.

Example

This example creates a merge publication and sets the publication compatibility level.

-- To avoid storing the login and password in the script file, the values 
-- are passed into SQLCMD as scripting variables. For information about 
-- how to use scripting variables on the command line and in SQL Server
-- Management Studio, see the "Executing Replication Scripts" section in
-- the topic "Programming Replication Using System Stored Procedures".

--Add a new merge publication.
DECLARE @publicationDB AS sysname;
DECLARE @publication AS sysname;
DECLARE @login AS sysname;
DECLARE @password AS sysname;
SET @publicationDB = N'AdventureWorks'; 
SET @publication = N'AdvWorksSalesOrdersMerge' 
SET @login = $(Login);
SET @password = $(Password);

-- Create a new merge publication. 
USE [AdventureWorks]
EXEC sp_addmergepublication 
    @publication = @publication, 
    -- Set the compatibility level to SQL Server 2000 SP3.
    @publication_compatibility_level = '80RTM'; 

-- Create the snapshot job for the publication.
EXEC sp_addpublication_snapshot 
    @publication = @publication,
    @job_login = @login,
    @job_password = @password;
GO

This example changes the publication compatibility level for the merge publication.

Note

Changing the publication compatibility level might be disallowed if the publication uses any features that require a particular compatibility level. For more information, see Replication Backward Compatibility.

DECLARE @publication AS sysname
SET @publication = N'AdvWorksSalesOrdersMerge' 

-- Change the publication compatibility level to 
-- SQL Server 2005.
EXEC sp_changemergepublication 
    @publication = @publication, 
    @property = N'publication_compatibility_level', 
    @value = N'90RTM'
GO

This example returns the current publication compatibility level for the merge publication.

DECLARE @publication AS sysname
SET @publication = N'AdvWorksSalesOrdersMerge' 

EXEC sp_helpmergepublication 
    @publication = @publication;
GO

See Also

Tasks

How to: Create a Publication (Replication Transact-SQL Programming)