Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Добрый день,

Я пытаюсь релицировать execution одной хранимой процедуры. Она динамически строит код ниже и запускает его.

IF EXISTS (select * from MyDB..sysfiles sf (nolock) where name = 'MyDB_201201')
ALTER DATABASE [MyDB]  REMOVE FILE [MyDB_201201]
IF EXISTS (select * from MyDB..sysfilegroups sfg (nolock) 
                where groupname = 'MyDB_201201' and sfg.groupname not in(
					SELECT distinct fg.name file_group_name
					FROM sys.filegroups fg
					LEFT JOIN sys.database_files df
					ON fg.data_space_id = df.data_space_id
					LEFT JOIN sys.partitions p
					ON fg.data_space_id = p.partition_number
					WHERE (p.object_id>4096 or p.object_id IS NULL)
					and fg.name='MyDB_201201'))
ALTER DATABASE [MyDB] REMOVE FILEGROUP [MyDB_201201]


Процедура прекрасно отрабатывает, если ее запустить напрямую на публикующем сервере и на подписчике. Когда я пытаюсь реплицировать запуск этой процедуры (@type = N'proc exec' или "Execution of the stored procedure") то в replication monitor получаю ошибку:
автор
ALTER DATABASE statement not allowed within multi-statement transaction. (Source: MSSQLServer, Error number: 226)
Get help: http://help/226



Что нужно поменять, чтобы такая репликация работала?

Спасибо.
30 июл 15, 09:01    [17953330]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
Roust_m,
выполнять отдельными пакетами, например, через динамический запрос.
30 июл 15, 12:06    [17954247]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
1. На подписчике создайте связанный сервер на себя с remote proc transaction promotion, выставленным в false.
2. Процедуру перепишите примерно так:
create procedure MyProc
as
begin
 set nocount on;

 declare @ДинамическиСформированныйКод nvarchar(max);
 select
  @ДинамическиСформированныйКод = ...

 declare @ProcName sysname = N'sys.sp_executesql';
 if (sessionproperty(N'replication_agent') = 1 or @@trancount > 0) and exists(select * from sys.servers where name = N'Связанный сервер на себя')
  select @ProcName = N'[Связанный сервер на себя].' + quotename(db_name()) + N'.' + @ProcName;

 exec @ProcName @ДинамическиСформированныйКод;
end;
30 июл 15, 12:41    [17954493]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
invm,

Создать связанный сервер очень трудно, ибо секьюрити и архитекторы этого не любят. Хотелось бы обойтись хранимой процедурой.
31 июл 15, 07:01    [17958179]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roust_m
Что нужно поменять, чтобы такая репликация работала?

Репликация тут непричем.
ALTER DATABASE запрещено выполнять в транзакции. Всегда.

The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.
31 июл 15, 09:49    [17958536]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Glory
Roust_m
Что нужно поменять, чтобы такая репликация работала?

Репликация тут непричем.
ALTER DATABASE запрещено выполнять в транзакции. Всегда.

The ALTER DATABASE statement must run in autocommit mode (the default transaction management mode) and is not allowed in an explicit or implicit transaction.


Так что-же нужно сделать, чтобы реплицировать эти команды? Процедура прекрасно работает если ее запустить на публикующем сервере или подписчике напрямую. Ломается только когда я пытаюсь реплицировать ее execution и только на подписчике.
4 авг 15, 02:54    [17971728]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roust_m
Процедура прекрасно работает если ее запустить на публикующем сервере или подписчике напрямую.

Ага, просто прекраснее некуда
BEGIN TRANSACTION
exec MyProc

Сообщение было отредактировано: 4 авг 15, 10:34
4 авг 15, 10:34    [17972429]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Glory
Roust_m
Процедура прекрасно работает если ее запустить на публикующем сервере или подписчике напрямую.

Ага, просто прекраснее некуда
BEGIN TRANSACTION
exec MyProc


Без "begin transaction" прекрасно работает. Вопрос только в том, как реплицировать эти изменения на подписчик. Реплицированием экзекьюшн хранимой процедуры не получается.
5 авг 15, 02:36    [17976529]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roust_m
Без "begin transaction" прекрасно работает.

Ну так делов то. Уберите ALTER DATABASE и все тоже будет прекрасно работать
5 авг 15, 09:42    [17976932]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Roust_m
Вопрос только в том, как реплицировать эти изменения на подписчик.
Вы сначала огласите перечень того, чего "не любят" ваши секьюрити а архитекторы.
5 авг 15, 10:35    [17977276]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Glory
Roust_m
Без "begin transaction" прекрасно работает.

Ну так делов то. Уберите ALTER DATABASE и все тоже будет прекрасно работать


Не совсем понял юмора. Alter database как раз то, что мне нужно реплицировать. Как я его могу убрать?
6 авг 15, 02:52    [17981596]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Roust_m,
А сделать её запуск без репликации, через Service Broker, например?
6 авг 15, 08:42    [17981793]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Jovanny
Roust_m,
А сделать её запуск без репликации, через Service Broker, например?


Не очень знаком с Service Broker. Засел читать.
6 авг 15, 09:07    [17981878]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
invm
Roust_m
Вопрос только в том, как реплицировать эти изменения на подписчик.
Вы сначала огласите перечень того, чего "не любят" ваши секьюрити а архитекторы.


Они много чего не любят, ибо банк и все очень бюрократично. Но я попробую если это работает, то спрошу, можно ли это организовать. Спасибо за подсказку.
6 авг 15, 09:09    [17981887]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roust_m
Glory
пропущено...

Ну так делов то. Уберите ALTER DATABASE и все тоже будет прекрасно работать


Не совсем понял юмора. Alter database как раз то, что мне нужно реплицировать. Как я его могу убрать?

Приблизительно так же, как вы убрали begin transaction
6 авг 15, 09:34    [17982013]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Roust_m
Они много чего не любят, ибо банк и все очень бюрократично. Но я попробую если это работает, то спрошу, можно ли это организовать. Спасибо за подсказку.
Я вам предложил единственный вариант, позволяющий реплицировать вызов процедуры (точнее выполнение ее тела) синхронно с остальными реплицируемыми инструкциями.
Если синхронность не требуется, то есть много вариантов с использование Job или Service Broker.
Самый простой - небольшая модификация исходной процедуры + Service Broker.
6 авг 15, 12:06    [17983069]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Glory
Roust_m
пропущено...


Не совсем понял юмора. Alter database как раз то, что мне нужно реплицировать. Как я его могу убрать?

Приблизительно так же, как вы убрали begin transaction



Опять не понимаю, begin transaction у меня не было и это не требуется.
7 авг 15, 02:17    [17986796]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
invm
Roust_m
Они много чего не любят, ибо банк и все очень бюрократично. Но я попробую если это работает, то спрошу, можно ли это организовать. Спасибо за подсказку.
Я вам предложил единственный вариант, позволяющий реплицировать вызов процедуры (точнее выполнение ее тела) синхронно с остальными реплицируемыми инструкциями.
Если синхронность не требуется, то есть много вариантов с использование Job или Service Broker.
Самый простой - небольшая модификация исходной процедуры + Service Broker.


Попробую и то и другое. Синхронность не требуется, нужно только чтобы на подписчике пустые файлы удалились вскоре после того как они удалены на публикующем сервере. Вариант Job на подписчике я рассматривал, но я хочу этого избежать, чтобы не создавать два процесса: на публикующем сервере и на подписчике.

Спасибо большое за помощь.
7 авг 15, 02:20    [17986800]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
invm
1. На подписчике создайте связанный сервер на себя с remote proc transaction promotion, выставленным в false.
2. Процедуру перепишите примерно так:
create procedure MyProc
as
begin
 set nocount on;

 declare @ДинамическиСформированныйКод nvarchar(max);
 select
  @ДинамическиСформированныйКод = ...

 declare @ProcName sysname = N'sys.sp_executesql';
 if (sessionproperty(N'replication_agent') = 1 or @@trancount > 0) and exists(select * from sys.servers where name = N'Связанный сервер на себя')
  select @ProcName = N'[Связанный сервер на себя].' + quotename(db_name()) + N'.' + @ProcName;

 exec @ProcName @ДинамическиСформированныйКод;
end;


Оказалось, что на подписчике уже есть связанный сервер на себя:repl_distributor

Поменял процедуру следующим образом:
CREATE PROCEDURE [dbo].[My_Test2] (@DBName sysname)
AS
BEGIN
	SET NOCOUNT ON
	DECLARE @s NVARCHAR(4000)
	select @s = 'IF EXISTS (select * from master..sysdatabases sf (nolock) where name = ''' + @DBName + ''')
	ALTER DATABASE ' + @DBName + ' SET READ_ONLY WITH ROLLBACK IMMEDIATE'
	declare @ProcName sysname = N'sys.sp_executesql';
	if (sessionproperty(N'replication_agent') = 1 or @@trancount > 0) and exists(select * from sys.servers where name = N'repl_distributor')
	select @ProcName = N'[repl_distributor].' + quotename(db_name()) + N'.' + @ProcName;
	exec @ProcName @s;
 	SET NOCOUNT OFF
		
END
go 

Команду поменял на "ALTER DATABASE ' + @DBName + ' SET READ_ONLY ", ибо данная команда тоже имеет ту же проблему и ее проще тестировать.

Опубликовал ее как "execution of the stored procedure".
Запускаю на публикующем сервере:
USE MyDB2
GO
 exec [dbo].[My_Test2] 'MyDB'


На публикующем сервере отрабатывает отлично, база в read_only. В Replication Monitor появляется ошибка:
Server 'repl_distributor' is not configured for RPC. (Source: MSSQLServer, Error number: 7411)
Get help: http://help/7411


Проверяю, RPC и RPC Out установлены в True.

На всякий случай запускаю на подписчике:
exec sp_serveroption @server='repl_distributor', @optname='rpc', @optvalue='true'
exec sp_serveroption @server='repl_distributor', @optname='rpc out', @optvalue='true'

Ошибка остается.
7 авг 15, 04:38    [17986831]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Glory
Member

Откуда:
Сообщений: 104760
Roust_m
Glory
пропущено...

Приблизительно так же, как вы убрали begin transaction



Опять не понимаю, begin transaction у меня не было и это не требуется.

ну если именно у вас нет и именно вам не это не требуется, то вот вы сами и выполняйте эту процедуру.
Что вы к репликации пристали ?
7 авг 15, 09:24    [17987062]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31438
Roust_m
Опять не понимаю, begin transaction у меня не было и это не требуется.
Вы хотите использовать репликацию как некое средство реплицирования вызовов.

А репликация для этого не предназначена, и это не умеет.

Она предназначена для репликации данных.

Для репликации данных у неё есть такой режим (как одно из её реализаций) - репликация процедур, менняющих данные.
Но это должны быть именно процедуры определённых типов, с некими ограничениями - процедуры, только меняющие данные, и способные работать в транзакции.

Обёртывание в транзакцию сделано для обеспечения целостности. Когда вы проектируете процедуру, вы не обязательно должны обеспечивать целостность, ну а транзакции вообще недопустимы при выполнении неких команд. Так что не все ваши процедуры можно реплицировать в реплликации.

Используйте другие средства репликации вызовов.
7 авг 15, 11:59    [17988135]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
invm
Member

Откуда: Москва
Сообщений: 9405
Roust_m,

Фокус не выйдет: repl_distributor - служебный сервер, с ним так нельзя.
7 авг 15, 12:36    [17988447]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7868
В любом случае, если в архитектуру заложено
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
то это весьма гниловато.
7 авг 15, 13:01    [17988616]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Glory
Roust_m
пропущено...



Опять не понимаю, begin transaction у меня не было и это не требуется.

ну если именно у вас нет и именно вам не это не требуется, то вот вы сами и выполняйте эту процедуру.
Что вы к репликации пристали ?


Я хочу избежать создания на подписчике второго процесса удаления старых данных. Я надеялся сделать это реплицированием исполнения хранимой процедуры, все работает, кроме удаления пустых файлов.

Поэтому хочу найти другой способ, который позволит вызвать эту команду с публикующего сервера на подписчике.
10 авг 15, 07:31    [17996937]     Ответить | Цитировать Сообщить модератору
 Re: репликация execution хранимой процедуры  [new]
Roust_m
Member

Откуда: Сидней
Сообщений: 1144
Владислав Колосов
В любом случае, если в архитектуру заложено
ALTER DATABASE [MyDB] REMOVE FILE [MyDB_201201]
то это весьма гниловато.


Нет, в архитектуру заложено определение файла для удаления через динамический SQL. В примере выше я использовал конечный результат, ибо проще для иллюстрации.
10 авг 15, 07:55    [17996955]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить