SQL Server 2005 Transactional Replication Fails to Publish Stored Procedure Containing an Index Create
I've experienced a bizarre problem with a SQL Server 2005 Transactional Publication. The issue is this: If the publication contains an article that is a stored procedure that contains a create index statement, then there is an error thrown when attempting to replicate the schema of the stored procedure to a subscriber.
The behavior is very odd, because even if the create index statement is commented out, it still gives the exception, and it will only work if it is removed altogether.
Here is the exact error that's being returned:
Command attempted: GRANT EXECUTE ON [dbo]. [usp_Test] TO [CompanyDatabase_access]
(Порядковый номер транзакции: 0x00000170000008B9000500000000, Идентификатор команды: 5)
Сообщения об ошибках: не удается найти объект 'usp_Test', потому что его не существует или у вас нет разрешения. (Источник: MSSQLServer, номер ошибки: 15151) Получите помощь: http: // help / 15151 Не удается найти объект usp_Test, потому что его не существует или вы делаете нет разрешения. (Источник: MSSQLServer, номер ошибки: 15151) Получить help: http: // help / 15151
Ошибка верна, потому что, когда я проверяю подписчика, хранимая процедура не была создана, как ожидалось ... но это было целью публикации .. .
Кроме того, я могу создать хранимую процедуру на подписчике вручную, но когда я создаю моментальный снимок, он удаляет существующую хранимую процедуру, а затем по-прежнему возвращает это сообщение об ошибке.
И вот пример публикации, которая создает эту проблему.
Хранимая процедура:
USE [CompanyDatabase]
GO
CREATE PROCEDURE [dbo].[usp_Test]
AS
CREATE TABLE #TempTable(ID INT)
CREATE NONCLUSTERED INDEX [IX_TempTable] ON [dbo].[#TempTable](ID)
SELECT 'Test'
GO
GRANT EXECUTE ON [dbo].[usp_Test] TO [CompanyDatabase_access]
GO
Сценарий публикации:
-- Adding the transactional publication
use [CompanyDatabase]
exec sp_addpublication
@publication = N'Replication Test',
@description = N'Publication of database ''CompanyDatabase''.',
@sync_method = N'concurrent',
@retention = 0,
@allow_push = N'true',
@allow_pull = N'true',
@allow_anonymous = N'false',
@enabled_for_internet = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21,
@ftp_login = N'anonymous',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@repl_freq = N'continuous',
@status = N'active', @independent_agent = N'true',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@allow_queued_tran = N'false',
@allow_dts = N'false',
@replicate_ddl = 1,
@allow_initialize_from_backup = N'false',
@enabled_for_p2p = N'false',
@enabled_for_het_sub = N'false'
GO
-- Adding the transactional articles
use [CompanyDatabase]
exec sp_addarticle
@publication = N'Replication Test',
@article = N'usp_Test',
@source_owner = N'dbo',
@source_object = N'usp_Test',
@type = N'proc schema only',
@description = N'',
@creation_script = N'',
@pre_creation_cmd = N'drop',
@schema_option = 0x0000000048000001,
@destination_table = N'usp_Test',
@destination_owner = N'dbo',
@status = 16
GO
-- Adding the transactional subscriptions
use [CompanyDatabase]
exec sp_addsubscription
@publication = N'Replication Test',
@subscriber = N'OtherDatabaseServer',
@destination_db = N'CompanyDatabase',
@subscription_type = N'Pull',
@sync_type = N'automatic',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
GO
Сценарий подписки:
/****** Begin: Script to be run at Subscriber ******/
use [CompanyDatabase]
exec sp_addpullsubscription
@publisher = N'DatabaseServer',
@publication = N'Replication Test',
@publisher_db = N'CompanyDatabase',
@independent_agent = N'True',
@subscription_type = N'pull',
@description = N'',
@update_mode = N'read only',
@immediate_sync = 0
exec sp_addpullsubscription_agent
@publisher = N'DatabaseServer',
@publisher_db = N'CompanyDatabase',
@publication = N'Replication Test',
@distributor = N'DatabaseServer',
@distributor_security_mode = 1,
@distributor_login = N'',
@distributor_password = N'',
@enabled_for_syncmgr = N'False',
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 0,
@active_end_date = 0,
@alt_snapshot_folder = N'',
@working_directory = N'',
@use_ftp = N'False',
@job_login = null,
@job_password = null,
@publication_type = 0
GO
/****** End: Script to be run at Subscriber ******/
И снова странно то, что публикация по-прежнему будет содержать ту же ошибку, если индекс создания заявление закомментировано, но оно будет работать, если оно будет удалено полностью.
А пока я 'Мы только что удалили из публикации все хранимые процедуры, которые содержат эти операторы создания индекса, но я хотел бы, чтобы они были реплицированы для подписчиков, чтобы любые обновления DDL для процедур автоматически отражались на подписчиках.
- EDIT - -
Заглянув в каталог моментальных снимков, файл .sch для usp_Test содержит точно такой же блок кода, который я ранее опубликовал для хранимой процедуры ... на основе возвращенной ошибки кажется, что агент моментальных снимков решает не запускать CREATE PROCEDURE, если она содержит индекс создания, но затем продолжает и пытается выполнить команду GRANT EXECUTE, которая вызывает ошибку.
Кроме того, моя точная версия SQL Server:
Microsoft SQL Server 2005 -но я хотел бы, чтобы они были реплицированы на подписчиков, чтобы любые обновления DDL для процедур автоматически отражались на подписчиках.
- EDIT -
Если заглянуть в каталог моментальных снимков, файл .sch для usp_Test содержит тот же блок кода, который я ранее опубликовал для хранимой процедуры ... на основе возвращенной ошибки кажется, что агент моментального снимка решает не запускать команду CREATE PROCEDURE, если он содержит индекс создания, но затем продолжает и пытается запустите команду GRANT EXECUTE, которая вызывает ошибку.
Кроме того, моя точная версия SQL Server:
Microsoft SQL Server 2005 -но я хотел бы, чтобы они были реплицированы на подписчиков, чтобы любые обновления DDL для процедур автоматически отражались на подписчиках.
- EDIT -
Если заглянуть в каталог моментальных снимков, файл .sch для usp_Test содержит тот же блок кода, который я ранее опубликовал для хранимой процедуры ... на основе возвращенной ошибки кажется, что агент моментального снимка решает не запускать команду CREATE PROCEDURE, если он содержит индекс создания, но затем продолжает и пытается запустите команду GRANT EXECUTE, которая вызывает ошибку.
Кроме того, моя точная версия SQL Server:
Microsoft SQL Server 2005 -на основании возвращенной ошибки создается впечатление, что агент моментального снимка решает не запускать команду CREATE PROCEDURE, если она содержит индекс создания, но затем продолжает и пытается выполнить команду GRANT EXECUTE, которая вызывает ошибку.
Кроме того, моя точная версия SQL Server:
Microsoft SQL Server 2005 -на основании возвращенной ошибки создается впечатление, что агент моментального снимка решает не запускать команду CREATE PROCEDURE, если он содержит индекс создания, но затем продолжает и пытается выполнить команду GRANT EXECUTE, которая вызывает ошибку.
Кроме того, моя точная версия SQL Server:
Microsoft SQL Server 2005 - 9.00.5254.00 (2005 + SP4 Cumulative Update 1)
-- END EDIT --
My question is, why is this happening? Is there an issue with the configuration of my publication or subscription? As anyone else experienced anything like this? Where would I start in troubleshooting this issue?
-- UPDATE --
I've been talking to Hilary Cotter on technet... and still no luck. If I remove the GRANT EXECUTE permission on the procedure, then it creates successfully with the CREATE INDEX. So it will work with GRANT EXECUTE OR CREATE INDEX, but not both. Hilary suggested that it might be some type of spam appliance in my domain that was preventing the snapshot from being transferred correctly when it contained both of those keywords, but if I manually copy the .sch file to the subscriber and validate that it contains the expected commands, I still get the same issue.