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;
go
--Create test partition
create partition function pf_test_dt (datetime)
as range right for values ( '20121201', '20121202', '20121203' );
go
create partition scheme ps_test_dt
as partition pf_test_dt all to ( [Primary] );
go
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;
go
create table dbo.test_table ( dt datetime
, a int
, b int
) on ps_test_dt (dt);
go
--Heap table ?1 for switching
if object_id ( N'dbo.test_table_01', N'U' ) is not null
drop table dbo.test_table_01;
go
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' );
go
-- 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 );
go
--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;
go
--Delete partition function and scheme
drop partition scheme ps_test_dt;
drop partition function pf_test_dt;
go
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';
go
-- Enable the test database for publishing
use test_db;
exec sp_replicationdboption @dbname = N'test_db', @optname = N'publish', @value = N'true';
go
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 – http://support.microsoft.com/kb/2002474 , 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
