Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Need help with Dynamic Sequel

Author  Topic 

NguyenL71
Posting Yak Master

228 Posts

Posted - 2013-10-02 : 12:44:50
[code]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)[/code]

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-02 : 15:49:52
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
   

- Advertisement -