Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_cdc_vupgrade

  No additional text.


Syntax
create procedure [sys].[sp_cdc_vupgrade]
as
begin

	declare	@db_name sysname
			,@raised_error int
			,@raised_message nvarchar(4000)
			,@action nvarchar(1000)
			,@trancount int

	set nocount on

	
 	-- Authorization check.
	if (isnull(is_srvrolemember('sysadmin'),0) = 0) and (isnull(is_member('db_owner'),0) = 0)
	begin
		raiserror(22904, 16, -1)
		return(1)
	end

	set @raised_error = 0
	set @db_name = db_name()
	
	create table #index_column_names(column_name sysname, index_ordinal int)

	-- Encapsulate transaction logic in TRY/CATCH.
	begin try

		-- Open a transaction to begin metadata updates.
		set @trancount = @@trancount
		
		begin tran
		save tran tr_cdc_vupgrade

		if object_id(N'[cdc].[change_tables]') is not null
		begin
			if not exists (select * from sys.columns where object_id = object_id(N'[cdc].[change_tables]') and name = N'partition_switch')
			begin

				set @action = N'add column partition_switch'
				alter table [cdc].[change_tables] add partition_switch bit default 0 not null
			end
			
			if not exists (select * from sys.indexes where object_id = object_id(N'[cdc].[change_tables]') and name = N'source_object_id_idx')
			begin
			
				set @action = N'create index source_object_id_idx on cdc.change_tables'
				create index [source_object_id_idx] on [cdc].[change_tables] (  source_object_id ASC )
			end
			
			-- Drop index change_table_nonunique_idx if it exists
			if exists (select * from sys.indexes where object_id = object_id(N'[cdc].[change_tables]') and name = N'change_tables_nonunique_idx')
			begin
			
				set @action = N'drop cdc.change_tables index change_tables_nonunique_idx'
				drop index [change_tables_nonunique_idx] on [cdc].[change_tables]
			end
			
			-- create index change_table_unique_idx if it doesn't exist otherwise create with drop_existing option
			if not exists (select * from sys.indexes where object_id = object_id(N'[cdc].[change_tables]') and name = N'change_tables_unique_idx')
			begin
			
				set @action = N'create cdc.change_tables index change_tables_unique_idx'
				create unique index [change_tables_unique_idx] on [cdc].[change_tables] (  capture_instance ASC )
			end
			else
			begin
			
				set @action = N'alter cdc.change_tables index change_tables_unique_idx with (drop_existing = on)'
				create unique index [change_tables_unique_idx] on [cdc].[change_tables] (  capture_instance ASC )
				with (drop_existing = ON)
			end
			
			-- Regenerate the enumeration TVFs to use the modified definition of fn_cdc_check_parameters
			declare @source_schema sysname, @source_name sysname, @capture_instance sysname,
				@supports_net_changes bit, @role_name sysname, @index_name nvarchar(1000),
				@fn_all_changes nvarchar(1000), @fn_net_changes nvarchar(1000), @change_table nvarchar(1000),
				@stmt nvarchar(max), @pk_column_list nvarchar(max), @column_name sysname, @ddl_action nvarchar(10)

			declare #hinstance cursor local fast_forward for
				select object_schema_name(source_object_id) as source_schema,
					   object_name(source_object_id) as source_name,
					   capture_instance, supports_net_changes, role_name
				from cdc.change_tables
				where object_schema_name(source_object_id) is not null and
					  object_name(source_object_id) is not null

			open #hinstance
			fetch #hinstance into @source_schema, @source_name, @capture_instance,
				@supports_net_changes, @role_name

			while (@@fetch_status <> -1)
			begin
				-- Drop the enumeration TVFs if they exist
				set @fn_all_changes = N'[cdc].' + quotename( N'fn_cdc_get_all_changes' +  N'_' + @capture_instance)
				set @fn_net_changes = N'[cdc].' + quotename( N'fn_cdc_get_net_changes' +  N'_' + @capture_instance)
				set @index_name = quotename(@capture_instance + N'_CT_idx')
				set @change_table = N'[cdc].' + quotename(@capture_instance + N'_CT')
	
				if object_id (@fn_all_changes, 'IF') is not null
				begin
					set @action = N'drop function fn_cdc_get_all_changes_'
					set @stmt = N'drop function ' + @fn_all_changes
					exec (@stmt)
				end
		
				if object_id (@fn_net_changes, 'IF') is not null
				begin
					set @action = N'drop function fn_cdc_get_net_changes_'
					set @stmt = N'drop function ' + @fn_net_changes
					exec (@stmt)
				end
		
				-- Generate the enumeration functions.
				set @action = N'sp_cdc_create_change_enumeration_functions'
				exec [sys].[sp_cdc_create_change_enumeration_functions]
	 				@source_schema, @source_name, @capture_instance, @supports_net_changes

				-- Grant select permission on the enumeration functions.
				set @action = N'sp_cdc_grant_select_on_change_enumeration_functions'
				exec [sys].[sp_cdc_grant_select_on_change_enumeration_functions]
					@capture_instance, @supports_net_changes, @role_name
					
				-- Drop the change table non-clustered index if it exists and then recreate it
				if (@supports_net_changes = 1)
				begin

					if exists (select * from sys.indexes where object_id = object_id(@change_table) and quotename(name) = @index_name)
					begin
						set @action = N'drop non-clustered change table index'
						set @stmt = N'drop index ' + @index_name + N' on ' + @change_table
						exec (@stmt)
					end	
								
					set @pk_column_list = N' '
					
					delete #index_column_names
		
					insert into #index_column_names
					select column_name, index_ordinal
					from [cdc].[index_columns] where object_id = object_id(@change_table)

   					declare #hidxcolumns cursor local fast_forward for
						select column_name
						from #index_column_names
						order by index_ordinal
		
					open #hidxcolumns
					fetch #hidxcolumns into @column_name
	
					while (@@fetch_status <> -1)
					begin
						set @pk_column_list = @pk_column_list + quotename(@column_name) +  N' ASC, '

						fetch #hidxcolumns into @column_name
					end
	
					close #hidxcolumns
					deallocate #hidxcolumns
	
					set @action = N'create unique nonclustered index for change table'
					set @stmt =  N'create unique nonclustered index ' +
						quotename(@capture_instance + N'_CT_idx') +
						N' on ' + @change_table + N'
						( '  + @pk_column_list + N'
							[__$seqval] ASC,
							[__$operation] ASC
						) INCLUDE ( [__$start_lsn], [__$update_mask] )'	 	
	
					exec (@stmt)
				end

				-- Alter the stored procedures to populate the change table
				-- and mark them as system procs
				set @action = N'sp_cdc_create_populate_stored_procs'
				exec [sys].[sp_cdc_create_populate_stored_procs]
					@capture_instance, N'alter'
					
				-- Mark change table as a system object
				set @action = N'sp_MS_marksystemobject @change_table'
				exec sp_MS_marksystemobject @change_table

				fetch #hinstance into @source_schema, @source_name, @capture_instance,
					@supports_net_changes, @role_name
			end		

			close #hinstance
			deallocate #hinstance
		
		end

		if object_id(N'[cdc].[lsn_time_mapping]') is not null
		begin
			if not exists (select * from sys.columns where object_id = object_id(N'[cdc].[lsn_time_mapping]') and name = N'tran_begin_lsn')
			begin

				set @action = N'add column tran_begin_lsn'
				alter table [cdc].[lsn_time_mapping] add tran_begin_lsn binary(10) null

				set @action = N'upgrade/create procs for lsn_time_mapping'
				exec [sys].[sp_cdc_lsn_time_mapping_procs] @action = N'alter'
				
			end
		end
		
		-- Mark CDC tables as system objects
		set @action = N'sp_MS_marksystemobject cdc.change_tables'
 		exec sp_MS_marksystemobject N'cdc.change_tables'

		set @action = N'sp_MS_marksystemobject cdc.ddl_history'
		exec sp_MS_marksystemobject N'cdc.ddl_history'

		set @action = N'sp_MS_marksystemobject cdc.captured_columns'
		exec sp_MS_marksystemobject N'cdc.captured_columns'

		set @action = N'sp_MS_marksystemobject cdc.index_columns'
		exec sp_MS_marksystemobject N'cdc.index_columns'
		
		set @action = N'sp_MS_marksystemobject dbo.systranschemas'
		exec sp_MS_marksystemobject N'dbo.systranschemas'
		
		set @action = N'sp_MS_marksystemobject cdc.lsn_time_mapping'
		exec sp_MS_marksystemobject N'cdc.lsn_time_mapping'
		
		set @action = N'sp_MS_marksystemobject cdc.[fn_cdc_get_all_changes_...]'
		exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_all_changes_...]'
		
		set @action = N'sp_MS_marksystemobject cdc.[fn_cdc_get_net_changes_...]'
		exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_net_changes_...]'
		
		set @action = N'sp_MS_marksystemobject cdc.[fn_cdc_get_all_changes_ ... ]'
		exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_all_changes_ ... ]'
		
		set @action = N'sp_MS_marksystemobject cdc.[fn_cdc_get_net_changes_ ... ]'
		exec sp_MS_marksystemobject N'cdc.[fn_cdc_get_net_changes_ ... ]'

		commit tran


	end try
	begin catch

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

		-- Save the error number and associated message raised in the TRY block
		select @raised_error = ERROR_NUMBER()
		select @raised_message = N'line ' + cast(ERROR_LINE() as nvarchar) + N', state ' + cast(ERROR_STATE() as nvarchar) + N', ' + ERROR_MESSAGE()
	end catch
	
	if @raised_error = 0
		return (0)
		
	raiserror(22841, 16, -1, @db_name, @action, @raised_error, @raised_message)
	return (1)

end

 
Last revision 2008RTM
See also

  sp_cdc_restoredb_internal (Procedure)
sp_cdc_vupgrade_databases (Procedure)
sp_MSdrop_cdc (Procedure)
sp_vupgrade_replication (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