A script to reproduce a bug in Merge Replication with FILESTREAM data
USE master; | |
GO | |
-- | |
-- CLEANUP | |
-- | |
IF DB_ID('TestMergeRep') IS NOT NULL | |
BEGIN | |
BEGIN TRY | |
exec TestMergeRep.sys.sp_dropmergepublication @publication=N'TestMergeRep'; | |
END TRY | |
BEGIN CATCH | |
PRINT 'sp_dropmergepublication failed' | |
END CATCH | |
BEGIN TRY | |
exec TestMergeRep.sys.sp_removedbreplication 'TestMergeRep', 'merge'; | |
END TRY | |
BEGIN CATCH | |
PRINT 'sp_removedbreplication failed' | |
END CATCH | |
BEGIN TRY | |
exec TestMergeRep.sys.sp_replicationdboption @dbname = N'TestMergeRep', @optname = N'merge publish', @value = N'false'; | |
END TRY | |
BEGIN CATCH | |
PRINT 'sp_replicationdboption failed' | |
END CATCH | |
ALTER DATABASE TestMergeRep SET SINGLE_USER WITH ROLLBACK IMMEDIATE; | |
DROP DATABASE TestMergeRep; | |
END | |
GO | |
-- | |
-- CREATE DATABASE | |
-- | |
CREATE DATABASE TestMergeRep; | |
GO | |
-- WE NEED A FILESTREAM FILEGROUP | |
DECLARE @path nvarchar(128), @sql nvarchar(max); | |
SELECT @path = LEFT(physical_name, LEN(physical_name) - CHARINDEX('\', REVERSE(physical_name),1) + 1) | |
FROM sys.database_files | |
WHERE type = 0 | |
ALTER DATABASE TestMergeRep | |
ADD | |
FILEGROUP [TestmergeRep_FileStream01] CONTAINS FILESTREAM; | |
SET @sql = ' | |
ALTER DATABASE TestMergeRep | |
ADD | |
FILE | |
( NAME = N''TestmergeRep_FS01'', FILENAME = ''' + @path + 'TestMergeRep_FS01'' , MAXSIZE = UNLIMITED) | |
TO FILEGROUP [TestmergeRep_FileStream01]; | |
' | |
EXEC(@sql) | |
-- | |
-- CREATE TABLE | |
-- | |
USE TestMergeRep; | |
GO | |
SET ANSI_NULLS ON | |
GO | |
SET QUOTED_IDENTIFIER ON | |
GO | |
CREATE TABLE [dbo].[DataStream]( | |
[DataStreamGUID] [uniqueidentifier] ROWGUIDCOL NOT NULL, | |
[ValueData] [varbinary](max) FILESTREAM NOT NULL, | |
CONSTRAINT [DataStream_DataStream_PK] PRIMARY KEY CLUSTERED | |
( | |
[DataStreamGUID] ASC | |
) | |
) | |
GO | |
-- I know it doesn't make sense, but the bug only shows | |
-- when the table has a second UNIQUE constraint on the PK column | |
ALTER TABLE [DataStream] ADD CONSTRAINT UQ_MESL_DataStreamPK UNIQUE ([DataStreamGUID]); | |
-- WORKAROUND: create the UNIQUE index without creating the UNIQUE constraint: | |
--CREATE UNIQUE NONCLUSTERED INDEX UQ_MESL_DataStreamPK ON [DataStream] ([DataStreamGUID]); | |
-- | |
-- SET UP REPLICATION | |
-- | |
USE master | |
EXEC sp_replicationdboption | |
@dbname = N'TestMergeRep', | |
@optname = N'merge publish', | |
@value = N'true'; | |
use [TestMergeRep] | |
exec sp_addmergepublication | |
@publication = N'TestMergeRep', | |
@description = N'Merge publication of database TestMergeRep.', | |
@retention = 30, | |
@sync_mode = N'native', | |
@allow_push = N'true', | |
@allow_pull = N'true', | |
@allow_anonymous = N'false', | |
@enabled_for_internet = N'false', | |
@conflict_logging = N'publisher', | |
@dynamic_filters = N'false', | |
@snapshot_in_defaultfolder = N'true', | |
@compress_snapshot = N'false', | |
@ftp_port = 21, | |
@ftp_login = N'anonymous', | |
@conflict_retention = 14, | |
@keep_partition_changes = N'false', | |
@allow_subscription_copy = N'false', | |
@allow_synctoalternate = N'false', | |
@add_to_active_directory = N'false', | |
@max_concurrent_merge = 0, | |
@max_concurrent_dynamic_snapshots = 0, | |
@publication_compatibility_level = N'100RTM', | |
@use_partition_groups = N'false'; | |
exec sp_addpublication_snapshot | |
@publication = N'TestMergeRep', | |
@frequency_type = 1, | |
@frequency_interval = 1, | |
@frequency_relative_interval = 1, | |
@frequency_recurrence_factor = 1, | |
@frequency_subday = 1, | |
@frequency_subday_interval = 5, | |
@active_start_date = 0, | |
@active_end_date = 0, | |
@active_start_time_of_day = 10000, | |
@active_end_time_of_day = 235959; | |
exec sp_addmergearticle | |
@publication = N'TestMergeRep', | |
@article = N'DataStream', | |
@source_owner = N'dbo', | |
@source_object = N'DataStream', | |
@type = N'table', | |
@description = null, | |
@column_tracking = N'true', | |
@pre_creation_cmd = N'drop', | |
@creation_script = null, | |
@schema_option = 0x000000010C034FD1, | |
@article_resolver = null, | |
@subset_filterclause = N'', | |
@vertical_partition = N'false', | |
@destination_owner = N'dbo', | |
@verify_resolver_signature = 0, | |
@allow_interactive_resolver = N'false', | |
@fast_multicol_updateproc = N'true', | |
@check_permissions = 0, | |
@identityrangemanagementoption = 'manual', | |
@delete_tracking = N'true', | |
@stream_blob_columns = N'false', | |
@force_invalidate_snapshot = 1; | |
-- Sets all merge jobs owned by sa | |
DECLARE @job_id uniqueidentifier | |
DECLARE c CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY | |
FOR | |
SELECT job_id | |
FROM msdb.dbo.sysjobs AS sj | |
INNER JOIN msdb.dbo.syscategories AS sc | |
ON sj.category_id = sc.category_id | |
WHERE sc.name = 'REPL-Merge'; | |
OPEN c | |
FETCH NEXT FROM c INTO @job_id | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
EXEC msdb.dbo.sp_update_job @job_id=@job_id , @owner_login_name=N'sa' | |
FETCH NEXT FROM c INTO @job_id | |
END | |
CLOSE c | |
DEALLOCATE c |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment