Blog Post

SQL Server issue: switching partitions using the parameter with enabled replication


It’s quite an unpleasant thing that we had to face once. There was a working script, which had been functioning for a long time and did not cause any problems until a need to replicate a few directories…

But let’s get into the details step by step:

First of all, we create the partition function and a scheme to demonstrate very unexpected behavior of SQL Server

set nocount on;

--Create test database

create database test_db;


--Create test partition

create partition function pf_test_dt (datetime)

as range right for values ( '20121201', '20121202', '20121203' );


create partition scheme ps_test_dt

as partition pf_test_dt all to ( [Primary] );


Now we add 3 tables: one partitioned according to the created scheme, in which we will switch the data and two heap tables

--Partitioning test table

if object_id ( N'dbo.test_table', N'U' ) is not null

drop table dbo.test_table;


create table dbo.test_table ( dt datetime

, a int

, b int

) on ps_test_dt (dt);


--Heap table ?1 for switching

if object_id ( N'dbo.test_table_01', N'U' ) is not null

drop table dbo.test_table_01;


create table dbo.test_table_01 ( dt datetime

check ( dt >= '20121201' and dt = '20121202' and dt

Now let’s switch data from these tables into the partitioned table

-- Make 1st switch through a constant

alter table dbo.test_table_01

switch to dbo.test_table partition $partition.pf_test_dt( '20121201' );


-- Make 2nd switch through a variable

declare @dt datetime = '20121202';

alter table dbo.test_table_02

switch to dbo.test_table partition $partition.pf_test_dt( @dt );


--Data appeared in the partitioned table

select * from dbo.test_table;

--Delete tables

drop table dbo.test_table

, dbo.test_table_01

, dbo.test_table_02;


--Delete partition function and scheme

drop partition scheme ps_test_dt;

drop partition function pf_test_dt;


That’s all! Now the code works fine … but up to a certain time! The moment you enable replication a problem appears:

use master

--Enable replication

exec sp_adddistributor @distributor = N'MySQLServer', @password = N'';

exec sp_adddistributiondb @database = N'distribution';

exec sp_adddistpublisher @publisher = N'MySQLServer', @distribution_db = N'distribution';


-- Enable the test database for publishing

use test_db;

exec sp_replicationdboption @dbname = N'test_db', @optname = N'publish', @value = N'true';


Then run the code specified above (with partitioning) in the context of our database enabled for publishing … and you get the error:

Msg 137, Level 15, State 2, Line 1

Must declare the scalar variable “@dt”.

Msg 3609, Level 16, State 2, Line 4

The transaction ended in the trigger. The batch has been aborted.

In real life everything can’t go smoothly and such an error may occur after some time (well, you just have not tested this particular piece of code in the test environment), and you it will take a long time to find out the cause of this behavior. Moreover there are sufficient number of triggers on the production server. But the cause of problem if one of the DDL triggers which are created automatically when you enable publishing, namely tr_MStran_altertable


How to avoid this error:

•Disable replication 🙂

•Run disable trigger tr_MStran_altertable on database before switching sections with a variable, which is the same absurd:)

•And the most simple way is to make the code dynamic, which will also help.

Later I even found an article in Microsoft Knowledge Base – , but it has reference only to SQL Server 2008 and nothing is said about SQL Server 2008 R2 and SQL Server 2012. And I’ve got the problem in SQL Server 2012:

Microsoft SQL Server 2012 – 11.0.2100.60 (X64)

Feb 10 2012 19:39:15

Copyright (c) Microsoft Corporation

Developer Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

And one more moment that cannot but upset is:

Note:SQL Server 2005 is not affected by this problem since the support for switching partitions in replicated environments is only introduced in SQL Server 2008

Original post (opens in new tab)
View comments in original post (opens in new tab)