Достаточно неприятная вещь, с которой пришлось столкнуться. Был вполне себе рабочий скрипт, который долгое время функционировал и не приносил никаких головных болей, пока не появилась необходимость реплицировать несколько справочников...
Но, давайте по порядку:
Первым делом подготовим функцию и схему секционирования, для демонстрации весьма неожиданного поведения SQL Server (http://msdn.microsoft.com/ru-ru/library/ms187802.aspx)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | set nocount on ;
create database test_db;
go
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
|
Теперь добавим три таблицы: одна секционированная по созданной схеме, в которую мы будем переключать данные и две таблицы-кучи
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | 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
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 is not null )
, a int
, b int
);
go
if object_id ( N 'dbo.test_table_02' , N 'U' ) is not null
drop table dbo.test_table_02;
go
create table dbo.test_table_02 ( dt datetime
check ( dt >= '20121202' and dt < '20121203' and dt is not null )
, a int
, b int
);
go
insert into dbo.test_table_01
values ( '20121201 04:00' , 1, 1 )
, ( '20121201 05:00' , 1, 1 )
, ( '20121201 06:00' , 1, 1 );
go
insert into dbo.test_table_02
values ( '20121202 04:00' , 2, 2 )
, ( '20121202 05:00' , 2, 2 )
, ( '20121202 06:00' , 2, 2 );
go
|
Ну а теперь проделаем вполне безобидное переключение данных из этих таблиц в секционированную таблицу
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 | alter table dbo.test_table_01
switch to dbo.test_table partition $partition.pf_test_dt( '20121201' );
go
declare @dt datetime = '20121202' ;
alter table dbo.test_table_02
switch to dbo.test_table partition $partition.pf_test_dt( @dt );
go
select * from dbo.test_table;
drop table dbo.test_table
, dbo.test_table_01
, dbo.test_table_02;
go
drop partition scheme ps_test_dt;
drop partition function pf_test_dt;
go
|
Вот собственно и всё! Данный код прекрасно работает... но до поры до времени! Теперь достаточно включить репликацию и у нас появятся проблемы:
1 2 3 4 5 6 7 8 9 10 11 | use master
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
use test_db;
exec sp_replicationdboption @dbname = N 'test_db' , @optname = N 'publish' , @value = N 'true' ;
go
|
Далее запускаем код, который указан выше (с секционированием), в контексте нашей БД с включенной публикацией... и получаем ошибку:
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.
И это ладно, что вы ожидали какого-то подвоха, а в жизни не всегда так гладко... т.к. подобная ошибка может появиться через какое-то время (ну не тестировали вы именно этот кусок кода на тестовом окружении), и вы долго будете искать причину такого поведения. Тем более на боевом сервере достаточное количество триггеров, в том числе и DDL. Но головную боль нам создаёт один из DDL-триггеров, которые создаются автоматически при включении публикации, а именно tr_MStran_altertable
Как обходить эту ошибку:
- Выключить репликацию :)
- Перед переключением секций с переменной делать disable trigger tr_MStran_altertable on database, что так же бред :)
- Ну и самое простое – заворачиваем код в динамику, тогда тоже всё работает
Самое интересное, что позже мне удалось найти даже статью в базе знаний МС - http://support.microsoft.com/kb/2002474, но в ней идёт ссылка на SQL Server 2008 и ничего не сказано про версии SQL Server 2008 R2 и SQL Server 2012. А у меня проблема возникла именно с SQL Server 2012:
1 2 3 4 | 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)
|
И, что ещё не может не огорчать, так это:
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
Поэтому я не поленился и разместил ФидБэк -
https://connect.microsoft.com/SQLServer/feedback/details/774508/parameterized-alter-table-statement-to-switch-a-partition-may-fail-for-a-database-that-is-published-for-transactional-replication-in-sql-server-2012.
Если вы попали на эту статью с аналогичной проблемой, то не стесняйтесь... заходите по ссылке и жмите "плюс", а то данный "сюрприз" будет нас ждать и в следующих версиях SQL Server.