SQL SERVER 2008

[jonhbt]

Hi, I need to use the

sys.sp_cdc_enable_table procedure

with the

@schema, @source_name, @source_name

parameters in a

cursor

but I can't find an example to follow. Can someone guide me with this with an example please? Thanks for your help. Regards

[Tim Carmichael]

Can you give a better description of what you are trying to do? Use some pseudo-code if necessary. Also, you listed your inputs as @schema, @source_name, @source_name (@source_name twice). Tim

[jonhbt]

Hi, I have a list of table that I need to make them available for auditing by using that particular procedure. I had in mind to call that procedure in a cursor which identifies the table names, builds the script for that procedure by passing the table name and the source to the procedure and executes it. This procedure will be repeated for all the tables. This will avoid hard coding the procedure for all the tables (which I have over 150). Yes yu're right they should be as listed below;

DECLARE Alter_tables_cursor CURSOR FAST_FORWARD
FOR
select table_name from information_schema.tables where table_name<>'dtProperties' and table_type<>'VIEW'
OPEN Alter_tables_cursor
DECLARE @tablename sysname
FETCH NEXT FROM Alter_tables_cursor INTO @tablename
WHILE ( @@FETCH_STATUS = 0 )
BEGIN

				EXECUTE 'sys.sp\_cdc\_enable\_table
				@source\_schema = N''dbo'',
				@source\_name = N''TBL\_ACTIVITY\_ACT'',
				@role\_name = NULL'	
			FETCH NEXT FROM Alter\_tables\_cursor INTO @tablename
			END

DEALLOCATE Alter_tables_cursor

Thanks for your reply.