Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_enable_db_internal

  No additional text.


Syntax
create procedure [sys].[sp_cdc_enable_db_internal]
as
begin
	declare @retcode int
			,@module_name nvarchar(1000)
			,@stmt nvarchar(1000)
			,@db_name sysname
			,@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

    if(@db_name = N'model' or @db_name = N'msdb' or @db_name = N'master' or @db_name = N'tempdb' or sys.fn_MSrepl_isdistdb(@db_name) = 1)
    begin
		raiserror(22989,16, 1, @db_name)
		return 1	
    end

    -- Verify database is not already enabled for change data capture
    if ([sys].[fn_cdc_is_db_enabled]() = 1)
    begin
		-- Raise an informational error only
		raiserror(22905, 10, -1, @db_name)
		return 0
    end

    -- Verify that the reserved 'cdc' database user and login do not already exist.
    if ([sys].[fn_cdc_db_objects_exist]() != 0)
    begin
		-- If the database has since become enabled, only raise informational error
		if ([sys].[fn_cdc_is_db_enabled]() = 1)
		begin
			-- Raise an informational error only
			raiserror(22905, 10, -1, @db_name)
			return 0
		end	
		raiserror(22906, 16, -1, @db_name)
        return 1
    end

    -- 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_enable_db_change_data_capture

		--  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 enabled before proceeding
		If ([sys].fn_cdc_is_db_enabled() = 1)
		begin
			-- If database is enabled now, another thread was attempting to enable CDC
			-- at the same time. Raise an informational error.
			commit tran
			raiserror(22905, 10, -1, @db_name)
			return 0
		end	

		
		-- Mark the database as enabled for CDC
		
        set @action = N'SetCDCTracked(Value = 1)'
		exec %%DatabaseEx(Name = @db_name).SetCDCTracked(Value = 1)
	
		-- Switch to 'dbo' before creating the cdc schema and user.
		-- This is the 'dbo' user of the database to be enabled,
		-- not the 'dbo' of the resource database.
		execute as user = 'dbo'
	    set @swuser_flag = 1
	
	    -- Create database CDC user
		set @action = N'create user cdc'
		create user [cdc] without login with default_schema = [cdc];

		-- Make 'cdc' user member of 'db_owner' for database
		set @action = N'sp_addrolemember ''db_owner'', ''cdc'''
		exec sp_addrolemember 'db_owner' , 'cdc'
		
		-- Switch to cdc user to crete the remaining objects
		revert
	    set @swuser_flag = 0
		execute as user = 'cdc'
	    set @swuser_flag = 1
		
		-- Create table systranschemas if it doesn't already exist
		if (object_id('dbo.systranschemas') is null)
		begin
			set @action = N'CREATE TABLE dbo.systranschemas'
			CREATE TABLE dbo.systranschemas
			(
				tabid int not null,
				startlsn binary(10) not null,
				endlsn binary(10) not null,
				typeid int not null default 52
			)
		
			set @action = N'create unique clustered index uncsystranschemas'
			create unique clustered index uncsystranschemas
			on systranschemas (startlsn)
		
			-- mark the index as a system object
			set @action = N'sp_MS_marksystemobject'
			exec dbo.sp_MS_marksystemobject 'systranschemas'
		end

		-- Create the objects in the database reserved for exclusive use by
		-- change tracking. Errors raised will be caught in catch block.
		set @action = N'sp_cdc_create_objects'
		exec [sys].[sp_cdc_create_objects]
		
		-- Create the functions in the database reserved for exclusive use by
		-- change tracking. Errors raised will be caught in catch block.
		set @action = N'sp_cdc_create_functions'
		exec [sys].[sp_cdc_create_functions]
		
		-- Create the database DDL triggers. Errors raised will be caught in catch block.
		set @action = N'sp_MScdc_ddl_database_triggers ''add'''
		exec [sys].[sp_MScdc_ddl_database_triggers] 'add'
		
		-- Actions that could fire DDL or DML database triggers have completed.
		revert
	    set @swuser_flag = 0
			
		-- Create the DDL server trigger. Errors raised will be caught in catch block.
		set @action = N'sp_MScdc_ddl_server_trigger ''add'' '
		exec [sys].[sp_MScdc_ddl_server_trigger] 'add'

		set @action = N'sp_replflush'
		exec sp_replflush

		commit tran

    END TRY

    BEGIN CATCH

		if @@trancount > @trancount
		begin
			if XACT_STATE() = 1
			begin
				rollback tran sp_enable_db_change_data_capture
				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()
		
		if @swuser_flag = 1
		begin
			revert
		end		

    END CATCH

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

end

 
Last revision 2008RTM
See also

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