Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_disable_db_internal

  No additional text.


Syntax
create procedure [sys].[sp_cdc_disable_db_internal]
as
begin
	declare @retcode int
            ,@is_cdc_enabled bit
            ,@module_name nvarchar(1000)
            ,@db_name sysname
            ,@flush_proc nvarchar(300)
            ,@done_proc nvarchar(300)
            ,@clearcache_proc nvarchar(300)
            ,@object_id int
            ,@raised_error int
            ,@raised_message nvarchar(4000)
            ,@action nvarchar(1000)
            ,@trancount int
            ,@resource nvarchar(255)
            ,@applock_result int
            ,@db_id int
            ,@swuser_flag bit
	
    set nocount on

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

    -- Encapsulate transaction logic in TRY/CATCH.
    BEGIN TRY

   		-- Authorization and verification checks have completed.
		-- Open a transaction to begin metadata updates.
		set @trancount = @@trancount
		
		begin tran
		save tran sp_disable_db_change_tracking

		--  Get exclusive database lock
		exec @applock_result = sys.sp_getapplock @Resource = @resource, @LockMode = N'Exclusive',
			@LockOwner = 'Transaction', @DbPrincipal = 'db_owner'

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

		-- The lock request was granted.
		-- Insure database parity is not already disabled before proceeding
		If ([sys].fn_cdc_is_db_enabled() = 0)
		begin
			-- If database is disabled now, another thread was attempting to disable CDC
			-- at the same time. Raise an informational error.
			commit tran
			raiserror(22901, 10, -1, @db_name)
			return 0
		end
		
		-- Drop the CDC capture job if it exists
		BEGIN TRY
			exec [sys].[sp_cdc_drop_job] @job_type = N'capture'
		END TRY
		BEGIN CATCH	
			-- even if drop fails, continue on
		END CATCH	

		-- Drop the CDC cleanup job if it exists
		BEGIN TRY
			exec [sys].[sp_cdc_drop_job] @job_type = N'cleanup'
		END TRY
		BEGIN CATCH	
			-- even if drop fails, continue on
		END CATCH	

		
		-- Unmark all tables as tracked for CDC
		--  in a brute force way
		
		DECLARE #hC CURSOR LOCAL FAST_FORWARD FOR
		SELECT source_object_id from cdc.change_tables
		OPEN #hC
		FETCH #hC into @object_id
		WHILE (@@fetch_status <> -1)
		begin
			if exists (
				select * from sys.objects
				where object_id = @object_id
			)
			begin
				BEGIN TRY	
					exec sp_MSverboselogging @object_id, 0
				END TRY
				BEGIN CATCH
					-- Continue even if a failure is encountered here
				END CATCH		
			end	
			FETCH #hC into @object_id
		end
		CLOSE #hC
		DEALLOCATE #hC

		
		-- If transactional replication is not enabled, we'll clear out dbtable fields
		
		if not exists (select * from sys.databases where database_id = db_id() and is_published = 1)
		begin
			SELECT @flush_proc = QUOTENAME(@db_name) + N'.sys.sp_replflush'
			SELECT @done_proc  = QUOTENAME(@db_name) + N'.sys.sp_repldone'
			SELECT @clearcache_proc = QUOTENAME(@db_name) + N'.sys.sp_replhelp'

			set @action = N'sys.sp_replflush'
			exec @flush_proc
			
			set @action = N'sys.sp_repldone NULL, NULL, 0, 0, 1'
			exec @done_proc NULL, NULL, 0, 0, 1

			set @action = N'sys.sp_replflush'
			exec @flush_proc
			
			set @action = N'.sys.sp_replhelp N''ClearDbArticleCache'''
			EXEC @clearcache_proc N'ClearDbArticleCache' --clear article cache for this database

			set @action = N'.sys.sp_replhelp N''DisablePerDbHistoryCache'''
			EXEC @clearcache_proc N'DisablePerDbHistoryCache' --clear DMV cache for this database

			IF EXISTS
			(
				SELECT	*
				FROM	sys.objects
				WHERE	name = N'systranschemas'
				AND	ObjectProperty(object_id('systranschemas'), 'IsTable') = 1
			)
			BEGIN
				-- Switch to database user 'cdc' to drop table
				execute as user = 'cdc'
				set @swuser_flag = 1
				
				set @action = N'DROP TABLE dbo.systranschemas'
				DROP TABLE dbo.systranschemas
				
				revert
				set @swuser_flag = 0
   			END

		end

		-- Invoke unsetting CDC bit on this database.  Error is caught in CATCH block.
		set @action = N'SetCDCTracked(Value = 0)'
		exec %%DatabaseEx(Name = @db_name).SetCDCTracked(Value = 0)

		set @action = N'[sys].[sp_replflush]'
		exec sys.sp_replflush

		-- Drop the objects in the database reserved for exclusive use by change data capture.
		-- Error is caught in CATCH block. To mitigate against malicious DDL trigggers,
		-- sp_cdc_drop_objects executes as database user 'cdc'.
		execute as user = 'cdc'
		set @swuser_flag = 1

		set @action = N'[sys].[sp_cdc_drop_objects]'
		exec [sys].[sp_cdc_drop_objects]
		
		set @action = N'[sys].[sp_MScdc_ddl_database triggers] ''drop'''
		exec [sys].[sp_MScdc_ddl_database_triggers] 'drop'
		
		-- Switch to database 'dbo' user to drop the 'cdc' user
		revert
		set @swuser_flag = 0
		execute as user = 'dbo'
		set @swuser_flag = 1

		if database_principal_id ('cdc') is not null
		begin
			set @action = N'drop user cdc'
			drop user cdc
		end	

		-- Actions that could fire database DML or DDL triggers have completed.	
		revert
		set @swuser_flag = 0
		
		set @action = N'[sys].[sp_MScdc_ddl_server_trigger] ''drop'''
		exec [sys].[sp_MScdc_ddl_server_trigger] 'drop'
		
		commit tran

    END TRY

    BEGIN CATCH

		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran sp_disable_db_change_tracking
				commit tran
			end	
		end

		-- Save the error number and associated message raised in the TRY block
		select @raised_error = ERROR_NUMBER()
		select @raised_message = ERROR_MESSAGE()
		
    END CATCH

	if @swuser_flag = 1
	begin
		revert
	end	

	if @raised_error = 0
		return 0
		
    raiserror(22831, 16, -1, @db_name, @action, @raised_error, @raised_message)
    return 1
end

 
Last revision SQL2008SP2
See also

  sp_cdc_disable_db (Procedure)
sp_cdc_restoredb_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