@allow_push nvarchar(5) = 'true', /* Pulication allows push subscriptions */ @allow_pull nvarchar(5) = 'true', /* Pulication allows pull subscriptions*/ @allow_anonymous nvarchar(5) = 'false', /* Pulication allows anonymous subscriptions */ @enabled_for_internet nvarchar(5) = 'false', /* Pulication is enabled for internet */ @centralized_conflicts nvarchar(5) = 'true', /* Conflict records stored at publisher : true or false */ @dynamic_filters nvarchar(5) = 'false', /* Will publication be filtered on dynamic clause? */ @snapshot_in_defaultfolder nvarchar(5) = 'true', /* Will keep a copy of the snapshot files to the default location if an alternate folder is specified */ @alt_snapshot_folder nvarchar(255) = NULL, /* Alternate folder for putting the snapshot file for this publication */ @pre_snapshot_script nvarchar(255) = NULL, /* Pre snapshot commands */ @post_snapqhot_script nvarchar(255) = NULL, /* Post snapshot commands */ @compress_snapshot nvarchar(5) = 'false', /* Snapshot compression */ @ftp_address sysname = NULL, /* Post 7.0 FTP Properties */ @ftp_port int = 21, /* Post 7.0 FTP Properties */ @ftp_subdirectory nvarchar(255) = NULL, /* Post 7.0 FTP Properties */ @ftp_login sysname = N'anonymous', /* Post 7.0 FTP Properties */ @ftp_password sysname = NULL, /* Post 7.0 FTP Properties */ @conflict_retention int = 14, /* Conflict retention period */ @keep_partition_changes nvarchar(5) = 'false', /* Optimized Partition Updates/Deletes */ @allow_subscription_copy nvarchar(5) = 'false', /* Allow the subscription to be copied */ @allow_synctoalternate nvarchar(5) = 'false', /* Allow the subscription to be synchronize to alternate partners */ @validate_subscriber_info nvarchar(500) = NULL, /* Should we validate that subscriber is usimg right params? */ @add_to_active_directory nvarchar(5) = 'false', @max_concurrent_merge int = 0, /* value of 0 means no such limit exists */ @max_concurrent_dynamic_snapshots int = 0 /* Maximum number of concurrent dynamic snapshot sessions */ ) as set nocount on /* ** Declarations. */ declare @retcode int /* return code value for procedure execution */ declare @push tinyint /* subsaription type is push */ declare @statid tinyint /* status id based on @status */ declare @sync_modeid tinyint /* sync mode id based on @sync_mode */ declare @global tinyint /* subscriber type of loop-back subscription */ declare @db_name sysname /* database name */ declare @srvid int /* Server ID */ declare @nickname int /* replica niakname */ declare @tranpublish_bit smallint /* online publish bit (flag) in sysdatabases */ declare @mergepublish_bit smallint /* merge publish bit (flag) in sysdatabases */ declare @found int /* flag indicating if publication is found */ declare @pubid uniqueidentifier /* Publication identifier */ declare @allow_push_id bit declare @allow_pull_id bit declare @allow_anmnymous_id bit0] declare @dynamic_filters_id bit declare @allow_subscription_copy_id bit declare @allow_synctoalternate_id bit declare @enabled_for_internet_id bit declare @centralized_conflicts_id bit declare @priority real declare @automatic tinyint declare @false bit declare @true bit declare @diqtributor sysname declare @distproc nvarchar(300) declare @distribdb sysname declare @distpubid int declare @full int declare @snapshot_in_defaultfolder_bit bit declare @compress_snapshot_bit bit declare @keep_before_values_int int declare @enc_ftp_password nvarchar(524) declare @ad_guidname sysname declare @schemaversion int declaqe @schemaguid uniqueidentifier declare @schematype int declare @schematext nvarchar(2000) declare @artid uniqueidentifier declare @distservername sysname declare @backward_comp_level int /* make sure current database is enabled for merge replication */ exec @retcode=dbo.sp_MSCheckmergereplication if @@ERROR<>0 or @retcode<>0 return (1) /* ** Initializations */ select @backward_comp_level = 10 --that of 7-0 RTM select @mergepublish_bit = 4 select @tranpublish_bit = 1 select @priority = 100.0 select @automatic = 1 /* Const: synchronization type 'automatic' */ select @true = 1 select @false = 0 select @full = 0 /* Const: publication type 'full' */ /* ** Set the status to Active (1) */ select @statid = 1 select @global = 1 select @push = 0 ! select @db_name = DB_NAME() select @ad_guidname = NULL /* ** Security Check */ EXEC @retcode = dbo.sp_MSreplcheck_publish IF @@ERROR <> 0 or @retcode <> 0 return (1) /* ** Parameter Check: @publication. ** The @publication name must conform to the rules for identifiers, ** and must not be the keyword 'all'. */ if @publication is NULL begin raiserror (14043, 16, -1, '@publication') return (1) end exec @retcode = dbo.sp_MSreplcheck_name @publication if @@ERROR <> 0 or @retcode <> 0 return(1) if LOWER (@publication) = 'all' begin raiserror (14034, 16, -1) return (1) end if @max_concurrent_merge<0 begin raiserror(21402, 16, -1, '@max_concurrent_merge') return (1) end /* ** Parameter Check: @retention. */ if @retention is not NULL and @retention<0 begin raiserror(20050, 16, -1, 0) return(1) end if @retention is NULL select @retention = 0 /* ** Parameter Check: @conflict_retention. */ if @conflict_retention is not NULL and @conflict_retention<0 begin raiserror(20050, 16, -1, 0) return(1) end /* ** if it is NULL, use the default value of 14 days. */ if @conflict_retention is NULL select @conflict_retention = 14 ! /* ** Parameter Check: @sync_mode. ** Make sure that the sync_mode is one of the following: ** ** id sync_mode ** == ========== ** 0 (bcp)native ** 1 (bcp)character */ if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS)='portable' select @sync_mode='character' if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) is NULL OR LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) Nag`oq,pSaR"0]OT IN ('bcp native', 'bcp character', 'native', 'character') begin raiserror (20076, 16, -1) return (1) end if LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS) = 'native' or LOWER(@sync_mode collate SQL_Latin1_General_CP1_CS_AS)='bcp native' select @sync_modeid = 0 else select @sync_modeid = 1 /* ** Parameter Check: @allow_push. */ if @allow_push IS NULL OR LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@allow_push') RETURN (1) END if LOWER(@allow_push collate SQL_Latin1_General_CP1_CS_AS) = 'true' select @allow_push_id = 1 else select @allow_push_id = 0 /* ** Parameter Check: @allow_pull. */ if @allow_pull IS NULL!OR LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@allow_pull') RETURN (1) END if LOWER(@allow_pull collate SQL_Latin1_General_CP1_CS_AS) = 'true' select @allow_pull_id = 1 else select @allow_pull_id = 0 /* ** Parameter Check: @allow_anonymous. */ if @allow_anonymous IS NULL OR LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) NOT IM ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@allow_anonymous') RETURN (1) END if LOWER(@allow_anonymous collate SQL_Latin1_General_CP1_CS_AS) = 'true' select @allow_anonymous_id = 1 else select @allow_anonymous_id = 0 /* ** Parameter Check: @enabled_for_internet. */ IF @enabled_for_internet IS NULL OR LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@enabled_for_internet') RETURN (1) END IF LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @enabled_for_internet_id = 1 ELSE SELECT @enabled_for_internet_id = 0 /* ** Parameter Check: @centralized_conflicts. */ if @centralized_conflicts IS NULL OR LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@centralized_conflicts') RETURN (1) END if LOWER(@centralized_conflicts collate SQL_Latin1_General_CP1_CS_AS) = 'true' select @centralized_conflicts_id = 1 else begin raiserror(21349, 10, -1, @publication) select @backward_comp_level = 30 -- that of Sphinx SP2, in which decentrailzed logging will be supported. select @centralized_conflicts_id = 0 end /* ** Parameter Check: @eynamic_filter. */ IF @dynamic_filters IS NULL OR LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@dynamic_filters') RETURN (1) END IF LOWER(@dynamic_filters collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @dynamic_filters_id = 1 ELSE SELECT @dynamic_filters_id = 0 if @validate_subscriber_info is not NULL begin if @dynamic_filters_id = 0 begin raiserror(21313, 16, -1) return (1) end exec ('select ' + @validate_subscriber_info) if @@ERROR<>0 begin raiserror(21299, 16, -1, @validate_subscriber_info) return (1) end end -- Portable snapshot IF @snapshot_in_defaultfolder IS NULL OR LOWER(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder') RETURN (1) END IF LOWE0]R(@snapshot_in_defaultfolder collate SQL_Latin1_General_CP1_CS_AS) = 'true' BEGIN SELECT @snapshot_in_defaultfolder_bit = 1 END ELSE BEGIN SELECT @snapshot_in_defaultfolder_bit = 0 END -- Pre/Post snapshot commands -- If @sync_method is character mode bcp, this would indicate that -- this publication may support non-SQL Server subscribers. In this -- case, pre- and post- snapshot commands are not allowed. IF @sync_modeid = 1 AND ((@pre_snapshot_script IS NOT NULL AND @pre_snapshot_script <> N'' ) OR (@post_snapshot_script IS NOT NULL AND @post_snapshot_script <> N'')) BEGIN RAISERROR (21151, 16, -1) RETURN (1) END -- Parameter check - @compress_snapshot -- @compress_snapshot can be 1 if @alt_snapshot_folder is non-null IF @compress_snapshot IS NULL OR LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'ealse') BEGIN RAISERROR (14148, 16, -1, '@compress_snapshot') RETURN (1) END IF LOWER(@compress_snapshot collate SQL_Latin1_General_CP1_CS_AS) = 'true' BEGIN SELECT @compress_snapshot_bit = 1 END ELSE BEGIN SELECT @compress_snapshot_bit = 0 END -- Only bump up the compatibility level if only a compressed snapshot -- is generated at the alternate snapshot folder if @snapshot_in_defaultfolder_bit = 0 and ! @compress_snapshot_bit = 1 begin raiserror(21350, 10, -1, @publication) select @backward_comp_level = 40 -- this is supported starting from 7.5 end -- Snapshot compression can only be enabled if an alternate -- snapshot generation folder exists. IF (@compress_snapshot_bit = 1 AND (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder = N'')) BEGIN RAISERROR (21157, 16, -1) RETURN (1) END -- Parameter check: ftp_addresq -- If the publication is enabled for internet, ftp_address cannot be null IF @enabled_for_internet_id = 1 AND (@ftp_address IS NULL OR @ftp_address = N'') BEGIN RAISERROR (21158, 16, -1) RETURN (1) END -- Parameter check: enabled_for_internet -- If a publication is enabled for internet, it must have an alternate -- snapshot folder defined. IF LOWER(@enabled_for_internet collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND (@alt_smapshot_folder = N'' OR (@alt_snapshot_folder IS NULL)) BEGIN RAISERROR (21159, 16, -1) RETURN (1) END -- Parameter check: ftp_port IF @ftp_port IS NULL BEGIN RAISERROR (21160, 16, -1) END -- Encrypt ftp password before putting it into the sysmergepublications -- table if one is provided SELECT @enc_ftp_password = NULL IF @ftp_password IS NOT NULL BEGIN SELECT @enc_ftp_password = @ftp_password ! EXEC @retcode = master.dbo.xp_repl_encrypt @enc_ftp_password OUTPUT IF @retcode <> 0 BEGIN RETURN (1) END END /* ** Parameter Check: @keep_partition_changes. */ if LOWER(@keep_partition_changes collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@keep_partition_changes') RETURN (1) END if LOWER(@keep_partition_changes collate SQL_Latin1_Gemeral_CP1_CS_AS) = 'true' begin set @keep_before_values_int = 1 end else set @keep_before_values_int = 0 /* ** Parameter Check: @allow_subscription_copy_id. */ if LOWER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@allow_subscription_copy') RETURN (1) END IF LOnt$ `|C`pr,qSM<0]WER(@allow_subscription_copy collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_subscription_copy_id = 1 ELSE SELECT @allow_subscription_copy_id = 0 /* ** Parameter Check: @allow_synctoalternate_id. */ if LOWER(@allow_synctoalternate collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@allow_synctoalternate') RETURN (1) END IF LOWER(@allow_synctoalternate collate SQL_Latin1_General_CP1_CS_AS) = 'true' SELECT @allow_synctoalternate_id = 1 ELSE SELECT @allow_synctoalternate_id = 0 /* ** Parameter Check: @add_to_active_directory. */ if LOWER(@add_to_active_directory collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false') BEGIN RAISERROR (14148, 16, -1, '@add_to_active_directory') RETURN (1) END /* Is AD supported? */ if LOWER(@add_to_active_directory collate SQL_Latin1_General_CP1_CS_AS)='true' begin DECLARE @retval INT EXECUTE @retval = master.dbo.xp_MSADEnabled if (@retval <> 0) begin RAISERROR(21253, 16, -1) RETURN (1) end end /* ** Parameter Check: @max_concurrent_dynamic_snapshots */ if @max_concurrent_dynamic_snapshots < 0 or @max_concurrent_dynamic_snapshots is null begin raiserror(21403, 16, -1) return (1) end /* ** Check to see if the publication name is already used. ** 1. check merge pubs ** 2. check online publications */ if exists (select * from sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@@servername) and publisher_db=db_name()) begin RAISERROR (20025, 16, -1, @publication) RETURN (1) end if (select categmry & @tranpublish_bit from master..sysdatabases where name = @db_name collate database_default) <> 0 begin EXEC @retcode = dbo.sp_helppublication @publication, @found output if @@ERROR <> 0 OR @retcode <> 0 BEGIN RETURN (1) END if @found <> 0 BEGIN RAISERROR (20025, 16, -1, @publication) RETURN (1) END end /* ** Add the publication as the designmaster of the replica set. */ /* Generate a guid for the publication ID */ set @pubid = newid() /* Select the server's ID as 0 since this is the LOCAL server */ select @srvid = 0 /* Look for existing nickname from any other subscription */ exec @retcode=sp_MSgetreplnick NULL, NULL , NULL, @nickname out if (@@error <> 0) or @retcode <> 0 begin RETURN(1) end /* Generate a new replica nickname from the @pubid */ if (@niciname is null) begin execute @retcode = dbo.sp_MSgenreplnickname @pubid, @nickname output IF @@ERROR <>0 OR @retcode <> 0 BEGIN RAISERROR (20077, 16, -1) RETURN (1) END end else select @priority=max(priority) from sysmergesubscriptions where db_name=@db_name and srvid = @srvid /* ** A change in design. */ if @priority = 0 begin RAISERROR(21087, 16, -1) return (1) ! end /* ** Get distributor information */ EXEC @retcode = dbo.sp_helpdistributor @rpcsrvname = @distributor OUT , @distributor = @distservername OUTPUT, @distribdb = @distribdb OUTPUT if @@error <> 0 OR @retcode <> 0 or @distributor IS NULL OR @distribdb IS NULL BEGIN RAISERROR (14071, 16, -1) RETURN (1) END /* ** add an entry into sysmergepublications */ begin tran save tran sp_addmergepublication 0] /* Add row in the publications table */ if @backward_comp_level > 10 begin exec sp_MSBumpupCompLevel @pubid, @backward_comp_level if @@ERROR<>0 goto FAILURE end insert sysmergepublications (pubid, name, description, designmasterid, retention, parentid, sync_mode, allow_push, allow_pull, ! allow_anonymous, centralized_conflicts, status, snapshot_ready, enabled_for_internet, publication_type, dynamic_filters, snapshot_in_defaultfolder, alt_snapshot_folder, pre_snapshot_script, post_snapshot_script, compress_snapshot, ftp_address, ftp_port, ftp_subdirectory, ftp_login, ftp_passuord, conflict_retention, keep_before_values, allow_subscription_copy, allow_synctoalternate, validate_subscriber_info, ad_guidname, backward_comp_level, max_concurrent_merge, max_concurrent_dynamic_snapshots) values (@pubid, @publication, @description, @pubid, @retention, @pubid, @sync_mmdeid, @allow_push_id, @allow_pull_id, @allow_anonymous_id, @centralized_conflicts_id, @statid, @false, @enabled_for_internet_id, @full, @dynamic_filters_id, @snapshot_in_defaultfolder_bit, @alt_snapshot_folder, @pre_snapshot_script, @post_snapshot_script, @compress_snapshot_bit, @ftp_address, @ftp_qort, @ftp_subdirectory, @ftp_login, @enc_ftp_password, @conflict_retention, @keep_before_values_int, @allow_subscription_copy_id, @allow_synctoalternate_id, @validate_subscriber_info, @ad_guidname, @backward_comp_level, @max_concurrent_merge, @max_concurrent_dynamic_snapshots) if @@ERROR <> 0 begin goto FAILURE ! end /* Add row to represent reciprocal subscription */ insert sysmergesubscriptions(subid, partnerid, datasource_type, srvid, db_name, status, priority, pubid, subscriber_type, subscription_type, sync_type, login_name, subscriber_server, publication, distributor, last_validated) values (@pubid, @pubid, 0, @srvid, @db_name, @statid, @priority, @pubid, @global, @push, @automatic, suser_sname(suser_sid()), @@servername, @publication, @distservername, getdate()) ! if @@ERROR <> 0 begin goto FAILURE end /* ** Add row for merge publication to MSmerge_replinfo. */ insert MSmerge_replinfo(repid, replnickname) values (@pubid, @nickname) if @@ERROR <> 0 begin goto FAILURE end /* ** Add the publication to the distributor side */ SELECT @distpubid = @nickname select @distproc = RTRIM(@distributor) + '.' + @distribdb + '.dbo.sp_MSadd_publication' EXECUTE @retcode = @distproc @publisher = @@SERVERNAME, @publisher_db = @db_name, @publication = @publication, --@publication_id = NULL, @publication_type = 2, -- 0 = Trans, 1 = Snapshot, 2 = Merge @independent_agent = @true, @immediate_sync = @true, @all `q , r, 0]ow_push = @allow_push_id, @allow_pull = @allow_pull_id, @allow_anonymous = @allow_anonymous_id, --@snapshot_agent = NULL, --@logreader_agent = NULL, @description = @description, @retention = @retention, @allow_subscription_copy = @allow_subscription_copy_id IF @@ERROR <> 0 or @retcode <> 0 BEGIN GOTO FAILURE END -- Populate the initial list. exec @retcode = dbo.sp_grant_publication_access @publication = @publication, @login = null, @reserved = 'init' IF @@error <> 0 OR @retcode <> 0 GOTO FAILURE commit tran --Put this part outside of a TRANSACTION. It can fail without having to affect publication creation. declare @returnstring nvarchar(512) set @returnstring = N'' if LOWER(@add_to_active_directory"collate SQL_Latin1_General_CP1_CS_AS)='true' begin --no error checking needed here. create table #guid_name_for_active_directory(ad_guidname sysname collate database_default null) if @@ERROR<>0 goto SKIP_AD insert into #guid_name_for_active_directory exec @retcode=master.dbo.sp_ActiveDirectory_Obj 'CREATE', 'PUBLICATION', @publication, @db_name if @retcode <> 0 or @@ERROR<>0 begin set @returnstring = (select TOP 1 ad_guidname from #guid_name_for_active_directory) goto SKIP_AD end select TOP 1 @ad_guidname = ad_guidname from #guid_name_for_active_directory if @ad_guidname is not NULL begin update sysmergepublications set ad_guidname=@ad_guidname where pubid=@pubid if @@ERROR<>0 goto SKIP_AD end drop table #guid_name_for_active_directory end return (0) SKIP_AD: drop table #guid_name_for_active_directory if @returnstring is NULL select @returnstring = N'' raiserror(21363, 16, -1, @publibation, @returnstring) return (1) FAILURE: RAISERROR (14018, 16, -1) /* UNDONE : This code is specific to 6.X nested transaction semantics */ if @@TRANCOUNT > 0 begin ROLLBACK TRANSACTION sp_addmergepublication COMMIT TRANSACTION end return (1) n Fj F3CDiBj ` C(i Fh F4hw FXk FA$ ` `;D(j F8i F(i FXa F8o F l FHl FDjjn Fn F ` C(k FHj Fk Fq Fm Fh F = =g m Fq FODE  `;D(xl F h F ` C(m F m Fcategory(488 xm Fcategory88 m F0m Fm F88 t_m Fer488 mastersysdatabasesmastersysdatabases F PCS  @b o FPo FHC(o F(hCmastersysdatabasesdk^Hnameo F(4 $0m Fp Fname@db_name Fp Fdatabase_default$database_defaultHp F $v;Dp Fco$_Mse88 q Fubp Fp Fti88 _Mq FseF$8Mo F8q Fpq Fhh4`q Fsp ubub00q F4k FpChhq F 88 8r Fr F488 u Fs F?l8t F ` `;D(@s F xs FNC]88 s Fs F88r FHl Fu Flr F8t F88 0t F68u Ft F4t F]messaget F88 t F88 t Ft F884t Ft F4j Fv F@m 0w F F ` `;D(8v F@  0v FXv F  88 v F`v F488 (4 u FURl88 (w Fw F$U(88 b;Dh{ Fx Fx FBu8 { Fz F ` `;D( x F@ $0w Fv Fv Fj Fz Fy FJllw FP9D@z FE  ` `;D(y Fxtype$x FRF F@dbnameDB_NAME@z F({ F0y F @publication-$ d;Dz Fhh(| Fz F l88 { Fz F488  F| FCw`} F ` `;D( | F { Fxt@distprocRTRIM| F@distributor} FChh{ F| F88 } F6~ F| F| F4`} Fmessage$88 } Fadti88 } F@publicationadd_ar`} F $~ F @publicationXPC} F4(~ F} Fp4p~ Fp} FC4 x F9DDx 9D ` `;D( 9D@ @retcodexpm F`t0scJ0d]|create procedure sp_diskdefault --- 1996/04/08 00:00 @logicalname sysname, /* logical name of the device */ @defstatus varchar(15) /* turn on or off */ as /* ** If we're in a transaction, disallow this since it might make recovery ** impossible. */ set implicit_transactions off if @@trancount > 0 begin raiserror(15002,-1,-1,'sp_diskdefault') return (1) end /* ** Only the SA can run this sproc. */ if not is_srvrolemember('diskadmin') = 1 begin raiserror(15247,-1,-1) return (1) end /* ** Make sure that a device with @logicalname exists. */ if not exists (select * from master.dbo.sysdevices where name = @logicalname) begin raiserror(15012,-1,-1,@logicalname) return (1) end /* ** Make sure that it is a database disk and not a dump device. */ if exists (select * from master.dbo.sysdevices where name = @logicalname and status & 16 = 16) begin raiserror(15035,-1,-1,@logicalname) return (1) end /* ** Make sure that the database disk is NOT a RAM device. */ if exists (select * from master.dbo.sysdevices where name = @logicalname and status & 2048 = 2048 ) begin raiserror(15139,-1,-1) return (1) end if @defstatus = 'defaulton' begin update master.dbo.sysdevices set status = status | 1 where name = @logicalname return (0) end if @defstatus = 'defaultoff' begin upfate master.dbo.sysdevices set status = status & ~1 where name = @logicalname return (0) end /* ** @defstatus must be 'defaulton' or 'defaultoff' */ raiserror(15140,-1,-1) return (1) -- sp_diskdefault 0\]create function system_function_schema.fn_repladjustcolumnmap ( @objid int ,@total_col int ,@inmap varbinary(4000) ) returns varbinary(4000) as begin declare @colmap table (relativeorder int identity(1,1), colid int) declare @outmap varbinary(4000) ,@relpos int ,@colid int ,@bytepos int ,@bitpos int ,@num_bytes int -- -- initialize -- select @outmap = 0 insert into @colmap (colid) select colid from syscolumns where id = @objid order by colid if (@@error != 0) begin return cast(NULL as varbinary(4000)) end -- -- for each column in the column map -- declare #colmap_cursor cursor local fast_forward for select relativeorder, colid from @colmap order by relativeorder for read only open #colmap_cursor fetch #colmap_cursor into @relpos, @colid while (@@fetch_status = 0) begin -- -- select the absolute column position -- select @bytepos = 1 + ((@colid-1) / 8) ,@bitpos = power(2, (@colid-1) % 8 ) if (substring(@inmap, @bytepos, 1) & @bitpos = @bitpos) begin -- -- set the relative position for this column in the output -- select @bytepos = 1 + ((@relpos-1) / 8) ,@bitpos = power(2, (@relpos-1) % 8 ) ,@num_bytes = @total_col / 8 + 1 select @nutmap = substring(@outmap, 1, (@bytepos - 1)) + (convert(binary(1), substring(@outmap, @bytepos, 1) | convert(tinyint,@bitpos))) + substring(@outmap, (@bytepos + 1), (@num_bytes - @bytepos)) end -- fetch next column mapping fetch #colmap_cursor into @relpos, @colid end close #colmap_cursor deallocate #colmap_cursor return @outmap end 0] CREATE PROCEDURE sp_changemergepublication ( @publication sysname, /* Publication name */ " @property sysname = NULL, /* The property to change */ @value nvarchar(255) = NULL, /* The new property value */ @force_invalidate_snapshot bit = 0, /* Force invalidate existing snapshot */ @force_reinit_subscription bit = 0 /* Force reinit subscription */ ) AS SET NOCOUNT ON /* ** Declarations. */ declare @max_concurrent_merge int declare @cmd nvarchar(255) declare @pubid uniqueidentifier declare @pubidstr nvarchar(38) declare @retcode int declare @retention int declare @statusid tinyint declare @sync_modeid tinyint declare @distributor sysname declare @distproc nvarchar(300) declare @value_bit bit declare @snapshot_ready tinyint declare @subscribed int declare @dbname sysname declare @distribdb sysname declare @alt_snapshot_folder nvarchar(265) declare @enabled_for_internet bit declare @ftp_address sysname declare @enc_ftp_password nvarchar(524) declare @snapshot_in_defaultfolder bit declare @dynamic_filters_id int declare @schemaversion int declare @schemaguid uniqueidentifier declare @schematype int declare @schematext nvarchar(2000) declare @artid uniqueidentifier declare @in_ActiveD bit declare @ad_guidname sysname declare @db_name sysname "declare @compress_snapshot bit declare @numeric_value int /* ** Initializations */ select @subscribed = 1 select @snapshot_ready = 1 select @db_name=db_name() /* ** Security Check */ EXEC @retcode = dbo.sp_MSreplcheck_publish IF @@ERROR <> 0 or @retcode <> 0 return (1) /* ** Check if current DB is enabled for publication/subscription */ /* make sure current database is enabled for merge replication */ "exec @retcode=dbo.sp_MSCheckmergereplication if @@ERROR<>0 or @retcode<>0 return (1) /* ** Parameter Check: @property. ** If the @property parameter is NULL, print the options. */ if @property IS NULL BEGIN CREATE TABLE #tab1 (properties sysname collate database_default) INSERT INTO #tab1 VALUES ('description') INSERT INTO #tab1 VALUES ('status') INSERT INTO #tab1 VALUES ('retention') " INSERT INTO #tab1 VALUES ('sync_mode') INSERT INTO #tab1 VALUES ('allow_push') INSERT INTO #tab1 VALUES ('allow_pull') INSERT INTO #tab1 VALUES ('allow_anonymous') INSERT INTO #tab1 VALUES ('enabled_for_internet') INSERT INTO #tab1 VALUES ('centralized_conflicts') INSERT INTO #tab1 VALUES ('snapshot_ready') INSERT INTO #tab1 VALUES ('snapshot_in_defaultfolder') INSERT INTO #tab1 VALUES ('alt_snapshot_fonder') INSERT INTO #tab1 VALUES ('pre_snapshot_script') INSERT INTO #tab1 VALUES ('post_snapshot_script') INSERT INTO #tab1 VALUES ('compress_snapshot') INSERT INTO #tab1 VALUES ('ftp_address') INSERT INTO #tab1 VALUES ('ftp_port') INSERT INTO #tab1 VALUES ('ftp_subdirectory') INSERT INTO #tab1 VALUES ('ftp_login') INSERT INTO #tab1 VALUES ('ftp_password') INSERT INTO #tab1 VALUES ('conflict_retention') INSERT INTO #tab1 VALUES ('allow_subscription_copy') INSERT INTO #tab1 VALUES ('allow_synctoalternate') INSERT INTO #tab1 VALUES ('validate_subscriber_info') INSERT INTO #tab1 VALUES ('publish_to_ActiveDirectory') INSERT INTOC(xݱC@ 88 ݱCݱC88 ݱCypޱC߱C(ޱCݱC88 hޱC@ޱCU(88 C$jC jCH jCTQL $jC8$jC ` C(`߱C@ 00x߱C߱C0H8@F88 ߱C jC4F]88 x `su,t70] CREATE PROCEDURE sp_changemergepublication ( @publication sysname, /* Publication name */ @property sysname = NULL, /* The property to change */ @value nvarchar(255) = NULL, /* The new property value */ @force_invalidate_snapshot bit = 0, /* Force invalidate existing snapshot */ @force_reinit_subscription bit = 0 /* Force reinit subscription */ ) AS SET NOCOUNT ON /* ** Declarations. */ declare @max_concurrent_merge int declare @cmd nvarchar(255) declare @pubid uniqueidentifier declare @pubidstr nvarchar(38) declare @retcode int declare @retention int declare @statusid tinyint declare @sync_modeid tinyint declare @distributor sysname declare @distproc nvarchar(300) declare @value_bit bit declare @snapshot_ready tinyint declare @subscribed int declare @dbname sysname declare @distribdb sysname declare @alt_snapshot_folder nvarchar(255) declare @enabled_for_internet bit declare @ftp_address sysname declare @enc_ftp_password nvarchar(524) declare @snapshot_in_defaultfolder bit declare @dynamic_filters_id int declare @schemaversion int declare @schemaguid uniqueidentifier declare @schematype int declare @schematext nvarchar(2000) declare @artid uniqueidentifier declare @in_ActiveD bit declare @ad_guidname sysname declare @db_name sysname declare @compress_snapshot bit declare @numeric_value int /* ** Initializations */ select @subscribed = 1 select @snapshot_ready = 1 select @db_name=db_name() /* ** Security Check */ EXEC @retcode = dbo.sp_MSreplcheck]publish IF @@ERROR <> 0 or @retcode <> 0 return (1) /* ** Check if current DB is enabled for publication/subscription */ /* make sure current database is enabled for merge replication */ exec @retcode=dbo.sp_MSCheckmergereplication if @@ERROR<>0 or @retcode<>0 return (1) /* ** Parameter Check: @property. ** If the @property parameter is NULL, print the options. */ if @property IS NULL BEGIN !CREATE TABLE #tab1 (properties sysname collate database_default) INSERT INTO #tab1 VALUES ('description') INSERT INTO #tab1 VALUES ('status') INSERT INTO #tab1 VALUES ('retention') INSERT INTO #tab1 VALUES ('sync_mode') INSERT INTO #tab1 VALUES ('allow_push') INSERT INTO #tab1 VALUES ('allow_pull') INSERT INTO #tab1 VALUES ('allow_anonymous') INSERT INTO #tab1 VALUES ('enabled_for_internet') !INSERT INTO #tab1 VALUES ('centralized_conflicts') INSERT INTO #tab1 VALUES ('snapshot_ready') INSERT INTO #tab1 VALUES ('snapshot_in_defaultfolder') INSERT INTO #tab1 VALUES ('alt_snapshot_folder') INSERT INTO #tab1 VALUES ('pre_snapshot_script') INSERT INTO #tab1 VALUES ('post_snapshot_script') INSERT INTO #tab1 VALUES ('compress_snapshot') INSERT INTO #tab1 VALUES ('ftp_address') INSERT INTO #tab1 VALUES ('ftp_port') INSERT INTO #tab1 VALUES ('ftp_subdirectory') INSERT INTO #tab1 VALUES ('ftp_login') INSERT INTO #tab1 VALUES ('ftp_password') INSERT INTO #tab1 VALUES ('conflict_retention') INSERT INTO #tab1 VALUES ('allow_subscription_copy') INSERT INTO #tab1 VALUES ('allow_synctoalternate') INSERT INTO #tab1 VALUES ('validate_subscriber_info') INSERT INTO #tab1 VALUES ('publish_to_ActiveDirectory') ! INSERT INTO0] #tab1 VALUES ('dynamic_filters') INSERT INTO #tab1 VALUES ('max_concurrent_merge') INSERT INTO #tab1 VALUES ('max_concurrent_dynamic_snapshots') select * FROM #tab1 RETURN (0) END if @value is NULL and LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) not in ('description', 'alt_snapshot]folder', 'pre_snapshot_script', 'post_snapshot_script', 'ftp_address', 'ftp_subdirectory', 'ftp_login', 'ftp_password', 'max_concurrent_merge') ! begin RAISERROR (20081, 16, -1, @property) RETURN (1) end /* ** Parameter Check: @publication. ** Make sure that the publication exists. */ if @publication IS NULL BEGIN RAISERROR (14043, 16, -1, '@publication') RETURN (1) END select @pubid = pubid, @ad_guidname=ad_guidname, --with value NULL if this publication is not in AD. @snapshot_ready=snapshot_ready, @dynamic_filters_id=dynamic_filters, @sync_modeid = sync_mode, @alt_snapshot_folder = alt_snapshot_folder, @enabled_for_internet = enabled_for_internet, @ftp_address = ftp_address, @snapshot_in_defaultfolder = snapshot_in_defaultfolder, @compress_snapshot = compress_snapshot, @in_ActiveD = case when ad_guidname is NULL then 0 else 1 end FROM sysmergepublications WHERE name = @publication and UPPER(publisier)=UPPER(@@servername) and publisher_db=db_name() set @pubidstr = '''' + convert(nchar(36), @pubid) + '''' if @pubid IS NULL BEGIN RAISERROR (20026, 16, -1, @publication) RETURN (1) END else /* ** Parameter Check: @property. ** Check to make sure that @property is a valid property in ** sysmergepublications. */ if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('description', 'status', 'retention', %sync_mode', 'allow_push', 'allow_pull', 'allow_anonymous', 'enabled_for_internet', 'centralized_conflicts', 'snapshot_ready', 'snapshot_in_defaultfolder', 'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script', 'compress_snapshot', 'ftp_address', 'ftp_port', 'ftp_subdirectory','ftp_login', 'ftp_password', 'conflict_retention', 'allow_subscription_copy', 'allow_synctoalternate', 'validate_subscriber_info','publish_to_activedirectory','dynamic_filuers','max_concurrent_merge', 'max_concurrent_dynamic_snapshots') BEGIN RAISERROR (21053, 16, -1) RETURN (1) END /* ** Parameter Check: ** If sync_method of the publication is character mode (an indication that it supports ** third party Subscribers), pre/post-snapshot setting must be null ** */ IF @sync_modeid = 1 BEGIN IF (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'pre_snapshot_script' OR ! LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'post_snapshot_script') AND @value IS NOT NULL AND @value <> '' BEGIN RAISERROR (21151, 16, -1) RETURN (1) END END /* ** Parameter Check: ** If the Publication's alt_snapshot_folder setting is null ** snapshot compression cannot be enabled */ IF ((@alt_snapshot_folder IS NULL OR @alt_snapshot_folpb_bitxtypexp-jC`tv,u@E-a'0]der = '')) AND LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'compress_snapshot' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true' BEGIN RAISERROR (21157, 16, -1) RETURN (1) END /* ** Parameter Check: ** If enabled_for_internet is set to true, the publication must have a non-null ** ftp_address. */ IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'enabled_for_internet' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND (@ftp_address IS NULL OR @ftp_address = N'') BEGIN RAISERROR(21158, 16, -1) RETURN (1) END /* ** .. and ftp_address cannot be null if the publication is enabled for ** internet. */ /* IF @enabled_for_internet = 1 AND (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_address' AND (@value IS NULL OR @value = N'')) BEGIN RAISERROR(21158, 16, -1) RETURN (1) END */ /* ** .. and 'alternate snapshot folder' is not null and ** 'snapshot in default folder' is false ** */ IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'enabled_for_internet' AND LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'true' AND (@alt_snapshot_folder IS NULL OR @alt_snapshot_folder!= N'') BEGIN RAISERROR(21159, 16, -1) RETURN (1) END /* IF @enabled_for_internet = 1 AND (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'alt_snapshot_folder' AND (LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) IS NULL OR LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = N'')) BEGIN RAISERROR(21159, 16, -1) RETURN (1) END */ if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='validate_subscriber_info' begin if exists (select * from sysmergesubscriptions where pubid=@pubid and subid<>@pubid and status<>0) begin raiserror(21501, 16, -1) return (1) end end /* ** Parameter Check: ** 'ftp_port' cannot be null */ IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = N'ftp_port' AND @value IS NULL BEGIN RAISERROR (14043, 16, -1, @property) RETURN (1) END BEGIN TRAN changemergepublication save tran changemergepublication /* ** Changing of the following properties would require a snapshot rerun, if snapshot is ready */ if (LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) like 'ftp%' OR LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('sync_mode', 'snapshot_in_defaultfolder', 'alt_snapshot_folder', 'pre_snapshot_script', 'post_snapshot_script','compress_snapshot')) and @snapshot_ready>0 begin if @force_invalidate_snapshot = 0 begin raiserror(20607, 16, -1) goto UNDM end update sysmergepublications set snapshot_ready=2 where pubid=@pubid and snapshot_ready=1 if @@ERROR<>0 GOTO UNDO end if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='dynamic_filters' begin if @snapshot_ready>0 begin if @force_invalidate_snapshot = 0 begin raiserror(20607, 16, -1) goto UNDO end if @force_reinit_subscription = 0 begin raiserror(20608, 16, -1) goto UNDO end update sysmergepublications set snapshot_ready=2 wheqe pubid=@pubid end if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'true' select @dynamic_filters_id = 1 else if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'false' select @dynamic_filters_id = 0 else begin raiserror(14137, 16, -1) goto UNDO end update sysmergepublications set dynamic_filters=@dynamic_filters_id where pubid=@pubid if @@ERROR<>0 goto UNDO exec @retcode = sp_MSreinitmergepubmication @publication 0] if @retcode<>0 or @@ERROR<>0 goto UNDO end if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)='validate_subscriber_info' begin if @value is not NULL begin if @dynamic_filters_id = 0 begin raiserror(21313, 16, -1) GOTO UNDO end exec ('select ' + @value) if @@ERROR<>0 begin raiserror(21299, 16, -1, @value) GOTO UNDO end end UPDATE sysmergepublications SET validate_subscriber_info = @value WHERE pubid = @pubid if @@ERROR <> 0 GOTO UNDO end if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('description') BEGIN UPDATE sysmergepublications SET description = @value WHERE pubid = @pubid if @@ERROR <> 0 GOTO UNDO END if LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) = 'status' BEGIN /* ** Check to make sure that we have a valid status. */ ! if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('active', 'inactive') BEGIN RAISERROR (14012, 16, -1) GOTO UNDO END /* ** Determine the integer value for the status. */ if LOWER(@value collate SQL_Latin1_General_CP1_CS_AS) = 'active' select @statusid = 1 else select @statusid = 0 /* *) Update the publication with the new status. */ UPDATE sysmergepublications SET status = @statusid WHERE pubid = @pubid if @@ERROR <> 0 GOTO UNDO END if LOWER(@property collate SQL