Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_disable_table_internal

  No additional text.


Syntax
create procedure sys.sp_cdc_disable_table_internal
(
	@source_schema sysname,
	@source_name sysname,
	@capture_instance sysname
)
as
begin
	declare @retcode int
			,@is_cdc_enabled bit
			,@stmt nvarchar(max)
			,@role_exists bit
			,@source_object_id int
			,@schema_owner sysname
			,@quoted_source_table nvarchar(1000)
			,@object_id int
			,@db_name sysname
			,@use_count int
			,@column_name sysname
			,@action nvarchar(1000)
			,@raised_error int
			,@raised_message nvarchar(4000)
			,@trancount int
			,@resource nvarchar(255)
			,@table_resource nvarchar(255)
			,@applock_result int
			,@db_id int
			
    set nocount on

    set @db_name = db_name()
    set @db_id = db_id()
    set @resource = N'__$cdc__db_' + convert(nvarchar(10),@db_id)
    set @source_name = rtrim(@source_name)
    set @source_schema = rtrim(@source_schema)
    set @raised_error = 0

    -- Verify source table schema is not null or empty
    if (@source_schema is null) or (@source_schema = N'')
    begin
		raiserror(22963, 16, -1, N'@source_schema')
        return 1
    end

    -- Verify source table name is not null or empty
    if (@source_name is null) or (@source_name = N'')
    begin
		raiserror(22963, 16, -1, N'@source_name')
        return 1
    end

    -- Verify @source_schema and @source_name together identify a local table
    select @quoted_source_table = quotename(@source_schema) + N'.' + quotename(@source_name)
    select @source_object_id = object_id(@quoted_source_table)
    select @table_resource = N'__$cdc__table_' + convert(nvarchar(10),@source_object_id)

    if (@source_object_id is null)							
    begin
		raiserror(22931, 16, -1, @source_schema, @source_name)
        return 1
    end

    -- If the capture instance is not null, verify that the table
    -- is currently enabled for change tracking using the
    -- identified capture instance.
    if (@capture_instance is not null)
	begin
		select @object_id = object_id
		from [cdc].[change_tables] with (index = source_object_id_idx)
		where capture_instance = @capture_instance
		and source_object_id = @source_object_id
	end

	if ((@capture_instance is null) or ((@object_id is null) and (UPPER(@capture_instance) <> N'ALL')))
	begin
		raiserror(22960, 16, -1, @capture_instance, @source_schema, @source_name )
		return 1
	end
	
	-- Wrap transaction logic in a TRY/CATCH block
	BEGIN TRY
	
		-- Authorization and verification checks have completed.
		-- Open a transaction to begin metadata updates.
		set @trancount = @@trancount
		
		begin tran
		save tran sp_disable_table_cdc
	
		--  Get shared database lock
		exec @applock_result = sys.sp_getapplock @Resource = @resource, @LockMode = N'Shared',
			@LockOwner = 'Transaction', @DbPrincipal = 'db_owner'

		If @applock_result < 0
		begin
			-- Lock request failed.
			set @action = N'sys.sp_getapplock @Resource = ' + @resource + N'@LockMode = N''Shared'', @LockOwner = ''Transaction'', @DbPrincipal = ''db_owner'' '
			raiserror(22840, 16, -1, @action, @applock_result)
		end

		-- The lock request was granted.
		-- Insure the database is still enabled before proceeding
		If ([sys].fn_cdc_is_db_enabled() = 0)
		begin
			-- If database is disabled now, another thread disabled CDC while
			-- we were waiting for the shared lock. Error with an indication
			-- that CDC is no longer enabled for the database.
			raiserror(22901, 16, -1, @db_name)
		end
	
		if (UPPER(@capture_instance) <> N'ALL')
		begin
			set @action = N'sp_cdc_disable_table_instance'

			exec @retcode = [sp_cdc_disable_table_instance] @source_schema
				,@source_name
				,@capture_instance
		end	
		else
		begin
   			declare #hinstance cursor local fast_forward
			for
				select capture_instance
				from [cdc].[change_tables] with (index = source_object_id_idx)
				where source_object_id = @source_object_id

			-- Drop tracking entries for the individual capture instance
			open #hinstance
			fetch #hinstance into @capture_instance
	
			while (@@fetch_status <> -1)
			begin
				set @action = N'sp_cdc_disable_table_instance'
				exec @retcode = [sp_cdc_disable_table_instance] @source_schema
					,@source_name
					,@capture_instance
			
				fetch #hinstance into @capture_instance
			end
	
			close #hinstance
			deallocate #hinstance
		end

		-- If there are no capture instances remaining for the source table, we can
		-- go with the simpler query that is less likely to produce deadlock.
		if not exists(
			select source_object_id from cdc.change_tables with (index = source_object_id_idx)
			where source_object_id = @source_object_id
			)
		begin	
			declare #hcolumn cursor local fast_forward
			for
				select name from sys.columns where object_id = @source_object_id
		end
		else
		begin
			declare #hcolumn cursor local fast_forward
			for
				select name from sys.columns where object_id = @source_object_id
				and column_id not in
				(	select distinct c.column_id from [cdc].[captured_columns] c
					join [cdc].[change_tables] t with (index = source_object_id_idx)
						on c.object_id = t.object_id
					where t.source_object_id = @source_object_id )
		end	

		open #hcolumn
		fetch #hcolumn into @column_name


		while (@@fetch_status <> -1)
		begin
			--clear this bit on source table column to unblock updatetext/writetext operation, exposed by columnproperty(..,.., 'is_cdctracked')
			set @action = N'SetCDCTracked(Value = 0)'
			EXEC %%ColumnEx(ObjectID = @source_object_id, Name = @column_name).SetCDCTracked(Value = 0)

			fetch #hcolumn into @column_name
		end

		close #hcolumn
		deallocate #hcolumn

		-- Disable verbose logging for the table.
		-- NOTE:  We will only disable verbose logging if there are no longer
		--        entries in the cdc.change_tables for the source table.
		if not exists (
			select top 1 source_object_id
			from [cdc].[change_tables] with (index = source_object_id_idx)
			where source_object_id = @source_object_id )
		begin
			set @action = N'sp_MSverboselogging'
			exec [sys].[sp_MSverboselogging]
				@source_object_id, 0
		end
	
		-- Call sp_replflush to notify capture process that a capture instance has been disabled.
		set @action = N'sys.sp_replflush'
		exec sys.sp_replflush
	
		commit tran
		
	END TRY
	
	BEGIN CATCH	

		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran sp_disable_table_cdc
				commit tran
			end	
		end
		
		set @raised_error = ERROR_NUMBER()
		set @raised_message = ERROR_MESSAGE()
		
	END CATCH
	
	if @raised_error = 0
		return 0
		
	raiserror(22833, 16, -1, @quoted_source_table, @action, @raised_error, @raised_message)
    		return 1
end

 
Last revision 2008RTM
See also

  sp_cdc_disable_table (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