Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSverboselogging

  No additional text.


Syntax
create procedure [sys].[sp_MSverboselogging]
(
	@object_id int 			-- table or indexed view
	,@mode bit = 1   -- 0 = disable, 1 = enable
)
as
begin
    set nocount on
	declare @colname sysname
				,@qualifiedname nvarchar(517)
				,@tran_replicated bit
				,@tran_filtered bit
				,@active tinyint
				,@initiated tinyint
				,@cmd nvarchar(1024)
				,@retcode int
				,@system_type_id int
				,@user_type_id int
				,@action nvarchar(1000)
				,@raised_error int
				,@raised_message nvarchar(4000)
				,@trancount int
				,@db_name sysname

    
    -- security check - should be dbo or sysadmin
    
    exec @retcode = sp_MSreplcheck_publish
    if @@ERROR != 0 or @retcode != 0
        return 1
				
    
    -- security check
    -- Has to be executed from cdc enabled db
    
	if(@mode = 1) and not exists (select * from sys.databases where database_id = db_id() and is_cdc_enabled = 1)
	begin
		set @db_name = db_name()
		RAISERROR(22901, 16, -1, @db_name)
		return (1)
	end
	
	set @raised_error = 0
	
	-- no need to check for input here, this proc is going to be internal only, therefore not expecting bad input
	-- Wrap transaction processing in a TRY/CATCH block
	BEGIN TRY
		set @trancount = @@trancount
		
		begin tran
		save tran sp_MSverboselogging

		set @action = N'sp_MSget_qualified_name'
		exec sys.sp_MSget_qualified_name @object_id, @qualifiedname output

		select @tran_replicated = 0
				,@tran_filtered = 0
				,@active = 2
				,@initiated = 3

		--lock the table before checking and making metadata change
		set @action = N'LockMatchID'
		EXEC %%Object(MultiName = @qualifiedname).LockMatchID(ID = @object_id, Exclusive = 1, BindInternal = 0)

		set @action = N'SetCDCTracked'
		EXEC %%Relation(ID = @object_id).SetCDCTracked(Value = @mode)

		--when turning off replicated bit for cdc, check and see if tran replication still need these bits set
		if (@mode = 0 and object_id('sysarticles') is not null and object_id('syssubscriptions') is not null)
		begin
			--if table has active tran repl subscription
			if exists (select * from sysarticles A join syssubscriptions S on A.artid = S.artid where A.objid = @object_id and S.status = @active)
			begin
				set @tran_replicated = 1
			end

			--if table needs has_replication_filter set for tran replication
			if exists( select * from sys.objects where type = 'RF' and parent_object_id = @object_id )
				or exists( select * from sysarticles A join syspublications P on A.pubid = P.pubid where A.objid = @object_id and (P.allow_queued_tran = 1 or P.allow_sync_tran = 1))
				or exists( select * from sysarticles where objid = @object_id and (upper(upd_cmd) like 'CALL%' OR upper(upd_cmd) like 'XCALL%' ) )
				or exists( select * from sysarticles where objid = @object_id and (upper(del_cmd) like 'XCALL%' ) )
				or exists( select * from sysarticles A join syssubscriptions S on A.artid = S.artid where S.status = @initiated)
			begin
				set @tran_filtered = 1
			end

		end

		--enable, do it anyway regardless of tran repl
		--disable, only do it when tran repl does not need is_replicated anymore
		if(@mode = 1 or @tran_replicated = 0)
		BEGIN
			set @action = N'SetReplicated'
			EXEC %%Relation(ID = @object_id).SetReplicated(Value = @mode)

			DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR
			SELECT DISTINCT name, system_type_id, user_type_id from sys.columns
			where  object_id = @object_id
			OPEN #hC
			FETCH #hC into @colname, @system_type_id, @user_type_id
			WHILE (@@fetch_status <> -1)
			begin
				set @action = N'SetReplicated'
				EXEC %%ColumnEx(ObjectID = @object_id, Name = @colname).SetReplicated(Value = @mode)

				--image, text. ntext
				if (@system_type_id not in (34, 35, 99) and @user_type_id not in  (34, 35, 99)) --image, text. ntext
				begin
					set @action = N'SetLogForRepl'
					EXEC %%ColumnEx(ObjectID = @object_id, Name = @colname).SetLogForRepl(Value = @mode)
				end

				FETCH #hC into @colname, @system_type_id, @user_type_id
			end
			CLOSE #hC
			DEALLOCATE #hC

		END

		--enable, do it anyway regardless of tran repl
		--disable, only do it when tran repl does not need has_replication_filter set anymore
		if(@mode = 1 or @tran_filtered = 0)
		BEGIN
			set @action = N'SetHasFilterProc'
			EXEC %%Relation(ID = @object_id).SetHasFilterProc(Value = @mode)
		END

		if(@mode = 0 and @tran_replicated = 1 and @tran_filtered = 0)
		begin
			set @action = N'sp_MSSetLogForRepl'
			exec @retcode = sp_MSSetLogForRepl @object_id
		end

		commit tran
		
	END TRY
	
	BEGIN CATCH
	
		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran sp_MSverboselogging
				commit tran
			end
		end	
		
		set @raised_error = ERROR_NUMBER()
		set @raised_message = ERROR_MESSAGE()
		
	END CATCH
	
	if @raised_error = 0
		return 0
		
	raiserror(22834, 16, -1, @qualifiedname, @action, @raised_error, @raised_message)
	return 1
end

 
Last revision 2008RTM
See also

  sp_cdc_disable_db_internal (Procedure)
sp_cdc_disable_table_internal (Procedure)
sp_cdc_enable_table_internal (Procedure)
sp_MSdrop_cdc (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash