SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Need help with Dynamic Sequel
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NguyenL71
Posting Yak Master

USA
208 Posts

Posted - 10/02/2013 :  12:44:50  Show Profile  Reply with Quote
I need to capture only certain columns to enable CDC and it must use dynamic sequel because they want to enable different databases
and tables without a lot of changes.  How can I capture only columns below to put into the Capture_Column_list.
SQL 2012

Thanks so much in advance.

Declare @Capture_Col_List VARCHAR(8000) = '',
        @cap_col_list VARCHAR(8000),
        @sqlCommand VARCHAR(MAX),
        @DBname  VARCHAR(50) = 'Dev',
		@SchemaName VARCHAR(50) = 'dbo',
	    @TableName  VARCHAR(50) = 'MyPlan_' --'dbo_TaskDocument'

SET @Capture_Col_List = '
  SELECT SUBSTRING( ( SELECT '', '' + COLUMN_NAME
                        FROM INFORMATION_SCHEMA.COLUMNS
					   WHERE Table_Name = ''' + @TableName + '''
					     AND COLUMN_NAME NOT IN ( ''Benefits'', ''BenefitsRaw'')
					     FOR XML PATH( '''') ), 2, 8000);'
--PRINT @Capture_Col_List

SET @cap_col_list = @Capture_Col_List
EXECute (@Capture_Col_List)

--  Plan_ID, CarrierOrganization_ID, ProductLineType_ID, PlanType_ID, PlanName, Metal

Result wants:
-------------
@cap_col_list = 'Plan_ID, CarrierOrganization_ID, ProductLineType_ID, PlanType_ID, PlanName, Metal'

SET @sqlCommand = '
EXEC sys.sp_cdc_enable_table
                   ,@source_schema = ''' + @SchemaName + '''
                   ,@source_name   =  ''' + @TableName + '''
				   ,@role_name     = Null
                   ,@supports_net_changes = 0 --cdc.fn_cdc_get_net_changes_<capture_instance> is not created
				   ,@capture_column_list = ''' + @cap_col_list + '''

'
print @sqlCommand
EXECute (@sqlCommand)

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 10/02/2013 :  15:49:52  Show Profile  Reply with Quote
quote:
Originally posted by NguyenL71

I need to capture only certain columns to enable CDC and it must use dynamic sequel because they want to enable different databases
and tables without a lot of changes.  How can I capture only columns below to put into the Capture_Column_list.
SQL 2012

Thanks so much in advance.

Declare @Capture_Col_List VARCHAR(8000) = '',
        @cap_col_list VARCHAR(8000),
        @sqlCommand VARCHAR(MAX),
        @DBname  VARCHAR(50) = 'Dev',
		@SchemaName VARCHAR(50) = 'dbo',
	    @TableName  VARCHAR(50) = 'MyPlan_' --'dbo_TaskDocument'

SET @Capture_Col_List = '
  SELECT SUBSTRING( ( SELECT '', '' + COLUMN_NAME
                        FROM INFORMATION_SCHEMA.COLUMNS
					   WHERE Table_Name = ''' + @TableName + '''
					     AND COLUMN_NAME NOT IN ( ''Benefits'', ''BenefitsRaw'')
					     FOR XML PATH( '''') ), 2, 8000);'
--PRINT @Capture_Col_List

SET @cap_col_list = @Capture_Col_List
EXECute (@Capture_Col_List)

--  Plan_ID, CarrierOrganization_ID, ProductLineType_ID, PlanType_ID, PlanName, Metal

Result wants:
-------------
@cap_col_list = 'Plan_ID, CarrierOrganization_ID, ProductLineType_ID, PlanType_ID, PlanName, Metal'

SET @sqlCommand = '
EXEC sys.sp_cdc_enable_table
                   ,@source_schema = ''' + @SchemaName + '''
                   ,@source_name   =  ''' + @TableName + '''
				   ,@role_name     = Null
                   ,@supports_net_changes = 0 --cdc.fn_cdc_get_net_changes_<capture_instance> is not created
				   ,@capture_column_list = ''' + @cap_col_list + '''

'
print @sqlCommand
EXECute (@sqlCommand)


The result of the "EXECute (@Capture_Col_List)" seems to be exactly the same as what you have indicated as "Result wants:". So it seems to be doing exactly what you want to do. Or, am I misinterpreting this?
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.25 seconds. Powered By: Snitz Forums 2000