Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScdc_db_ddl_event

  No additional text.


Syntax
create procedure sys.sp_MScdc_db_ddl_event
(
	@EventData xml
)
as
begin
	set nocount on

	declare @old_database_name sysname
		,@new_database_name sysname
		,@event_type sysname
		,@proc nvarchar(1000)
		,@retcode int
		
	-- If the table msdb.dbo.cdc_jobs doesn't exist, simply return.
	if ([sys].[fn_cdc_jobs]() = 0)
		return 0

	select @event_type = event_instance.value('EventType[1]', 'nvarchar(100)')
		,@old_database_name = event_instance.value('DatabaseName[1]', 'sysname')
	FROM       @EventData.nodes('/EVENT_INSTANCE') as R(event_instance)

	--for alter database, all we care is if database name has changed, skip everything else
	if (@event_type = N'ALTER_DATABASE')
	begin
		--if no cdc enabled db, don't do anything
		if not exists (select * from sys.databases where is_cdc_enabled = 1)
			return 0

		if db_id(@old_database_name) is not null
			return 0
		else
		begin
			-- the only reliable way to find new database name is through our jobs
			select @new_database_name = [sys].[fn_cdc_db_name_from_job_entry](@old_database_name)
		
			-- if jobs don't exists for whatever reason, we don't really need to do any thing	
			if @new_database_name is null
				return 0
			
			set @proc = quotename(@new_database_name) + N'.sys.sp_MScdccheck_ddladmin'
			EXEC @retcode = @proc
			IF @@ERROR <> 0 or @retcode <> 0
			BEGIN
				return 1
			END
		end
	end

	--only sa can drop cdc-enabled database, even if there's other way to grant non-sa right to do so,
	--ddl trigger is after effect so db is gone therefore we won't have anyway to evaluate what right you have in the dropped db.
	if (@event_type = N'DROP_DATABASE')
	begin
		--the whole purpose of hooking into DROP_DATABASE is to cleanup cdc jobs, if there is no job, no need to do anything
		if ([sys].[fn_cdc_jobs]() = 1)
		begin
				--again, if the dropped db has no job to clean, why bother
				if ([sys].[fn_cdc_job_entry](@old_database_name) = 1)
				begin
					if (isnull(is_srvrolemember('sysadmin'),0) = 0)
					begin
						raiserror(22902, 16, -1)
						return 1
					end
				end
				else
					return 0
		end
		else
			return 0
	end
	
	-- Remove obsolete entries from msdb jobs tables. This is handled as a best effort.
	-- Failure should not cause DROP or ALTER to fail.
	exec [sys].[sp_cdc_cleanup_job_entries] @event_type, @old_database_name, @new_database_name
	
	return 0
	
end

 
Last revision 2008RTM
See also

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