Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.