help me with a quick guide

May 8, 2012 at 6:56 PM

i have no idea how it works this standardcdc,

 

any help , any idea, any quick guide to configure this cdc will be appreciate

 

many thanks in advanced

 

May 8, 2012 at 7:24 PM
Edited May 8, 2012 at 7:27 PM

Hi there,

I'll be writing some documentation for the project I'm using this in the near future and I'd be happy to share it.

My version is customised for the database I've designed, so it will vary from the source here but it's still close enough to be useful and I'd be happy to contribute back to the project.

To get you started; It all works using triggers and associated tables, one each per table being CDC'd (plus one table called scdc.change_tables containing a list of tables being tracked), which is high I/O impact but I've not found anything else to do the job for Standard Edition. I'm away from work at the moment so I can't access any source but ploughing through the code is a start. From memory, assuming you've installed it OK (exec sp_StandardCDC_enable_db first) . you exec a proc naming the table you want to track changes.

Your table will need to have a primary key defined, which is only good practice really. IMHO, the split_array function and table is clumsy and unnecessary, but stick with it for now. I'm not sure that the site version support the newer geo datatypes in SS2008 but again, not too difficult if needed.

I'm not using the DDL side of things, so no comment from me on that.

I hope this helps.  I can see there's not been much activity other than some of my old posts on here, which is a shame as it's a worthwhile project.

Regards

Derek

May 9, 2012 at 8:10 PM
Edited May 9, 2012 at 8:10 PM

thanks for your reply , but im newbie and im confused

 

could you give me an example

 

if i have a database called "test"   and a table called "cars"

CREATE TABLE [dbo].[cars]( [idcars] [int] IDENTITY(1,1) NOT NULL, [make] [varchar](50) NULL, [codeline] [int] NULL, [year] [int] NULL, [fecha] [datetime] NULL) ON [PRIMARY]
GO

 

lets put one record

INSERT INTO [Pruebas].[dbo].[cars2]           ([make]           ,[codeline]           ,[year])     VALUES           ('audi',            315,           2010)GO

 

 

how could i use your code to track the changes made to my table "cars"

 

how coul configure?

ill really apprecite your help, if you can give me an example with this table called   test.dbo.cars

 

many thanks in advanced

 

kind regards

May 11, 2012 at 6:10 PM

hi

 

i have this table

 

CREATE TABLE [dbo].[test]( [IDCDC] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [IDCDC] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

 

i tried to exec the sp "

 

EXEC sp_StandardCDC_enable_table

@SourceSchema=N'dbo',

@SourceName=N'test',

@RoleName =NUll,

@CapturedColumnList =N'idcdc,name' ,

@FileGroupName =Null,

@CaptureUser =1

GO 

 

and i get this error

"Msg 50000, Level 16, State 1, Procedure sp_StandardCDC_enable_table, Line 364Error on line 13: Invalid column name 'AuditName'."

 

 

thanks for reading

 

i hope you can help me

 

kind regards

May 16, 2012 at 1:26 PM

HELP ME PLEASE

 

thanks in advanced

May 23, 2012 at 8:35 PM
sebastian11c wrote:

hi

 

i have this table

 

CREATE TABLE [dbo].[test]( [IDCDC] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [IDCDC] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

 

i tried to exec the sp "

 

EXEC sp_StandardCDC_enable_table

@SourceSchema=N'dbo',

@SourceName=N'test',

@RoleName =NUll,

@CapturedColumnList =N'idcdc,name' ,

@FileGroupName =Null,

@CaptureUser =1

GO 

 

and i get this error

"Msg 50000, Level 16, State 1, Procedure sp_StandardCDC_enable_table, Line 364Error on line 13: Invalid column name 'AuditName'."

 

 

thanks for reading

 

i hope you can help me

 

kind regards

 I believe your issue there is that you are setting the CaptureUser parameter to true.  It appears to me that in order to use that feature you need to create a field on the table you're tracking that way (the source table) called AuditName.  I am unsure of this - I am right now planning on tracking users with a different method.  What I do know is that I had that same error when I started trying to use this, but when I set the CaptureUser parameter to 0 I was able to setup StandardCDC for tables.

Hope this helps. 

May 23, 2012 at 10:03 PM
hi
many many thanks for your reply

ill try to fix the issue with your answer



kind reagards

2012/5/23 todtrexler <notifications@codeplex.com>

From: todtrexler

sebastian11c wrote:

hi

i have this table

CREATE TABLE [dbo].[test]( [IDCDC] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, CONSTRAINT [PK_test] PRIMARY KEY CLUSTERED ( [IDCDC] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO

i tried to exec the sp "

EXEC sp_StandardCDC_enable_table

@SourceSchema=N'dbo',

@SourceName=N'test',

@RoleName =NUll,

@CapturedColumnList =N'idcdc,name' ,

@FileGroupName =Null,

@CaptureUser =1

GO

and i get this error

"Msg 50000, Level 16, State 1, Procedure sp_StandardCDC_enable_table, Line 364Error on line 13: Invalid column name 'AuditName'."

thanks for reading

i hope you can help me

kind regards

I believe your issue there is that you are setting the CaptureUser parameter to true. It appears to me that in order to use that feature you need to create a field on the table you're tracking that way (the source table) called AuditName. I am unsure of this - I am right now planning on tracking users with a different method. What I do know is that I had that same error when I started trying to use this, but when I set the CaptureUser parameter to 0 I was able to setup StandardCDC for tables.

Hope this helps.

Read the full discussion online.

To add a post to this discussion, reply to this email (StandardEditionCDC@discussions.codeplex.com)

To start a new discussion for this project, email StandardEditionCDC@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com




--

Sebastian Correa

Sistemas
AJUSTEV S.A.S
Cel. Sistemas: 3006470129
PBX. Ajustev: 381 77 77 Ext. 104
Calle 31 # 43A - 72 Barrio Clombia, Medellín

May 28, 2012 at 5:22 PM

WHAT A FANTASTIC CODE!!!

 

thankf for sharing with us

 

 

kind regards

May 28, 2012 at 9:18 PM
Edited May 28, 2012 at 9:19 PM

hi, the code work in some tables, but i have a problem with this

 

"
CREATE TABLE [dbo].[Inspeccion_Resultado]( [IdInspeccionResultado] [int] IDENTITY(1,1) NOT NULL, [IdInspeccion] [int] NOT NULL, [IdServicio] [int] NOT NULL, [IdCodigoRechazo] [int] NOT NULL, [Estado] [int] NOT NULL, [Version] [int] NOT NULL, [IdTercero] [int] NULL, [Observaciones] [varchar](1000) NULL, CONSTRAINT [PK_Inspeccion_Resultado] PRIMARY KEY CLUSTERED ( [IdInspeccionResultado] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]
GO

 

 

so i tried to exec this

 

EXEC sp_StandardCDC_enable_table

@SourceSchema=N'dbo',

@SourceName=N'inspeccion_resultado',

@RoleName =NUll,

@CapturedColumnList =N'IdInspeccionResultado,IdInspeccion,IdServicio,IdCodigoRechazo,Estado,Version,IdTercero,Observaciones' ,

@FileGroupName =Null,

@CaptureUser =0

GO 

 

i get this error

"Msg 50000, Level 16, State 3, Procedure sp_StandardCDC_enable_table, Line 365 Error on line 1: Column names in each table must be unique. Column name 'IdInspeccionResultado' in table 'inspeccion_resultado' is specified more than once."

 

any help please

Coordinator
May 29, 2012 at 3:03 AM

By default the primary key is added to the capture table.  Remove IdInspeccionResultado from @CapturedColumnList and all should work as expected.

May 29, 2012 at 1:08 PM
thanks for your reply again


i already did that (remove idinspeccionresultado from capturedcolumnlist):

EXEC sp_StandardCDC_enable_table
@SourceSchema=N'dbo'
,@SourceName=N'inspeccion_resultado'
,@RoleName =NUll
,@CapturedColumnList =N'IdInspeccion,IdServicio,IdCodigoRechazo,Estado,Version,IdTercero,Observaciones'
,@FileGroupName =Null
,@CaptureUser =0
GO

but i get this error now
Msg 50000, Level 16, State 1, Procedure sp_StandardCDC_enable_table, Line 365
Error on line 136: No primary key included in @CapturedColumnList

any idea to fix that??

thanks in advanced

2012/5/28 wmweaver <notifications@codeplex.com>

From: wmweaver

By default the primary key is added to the capture table. Remove IdInspeccionResultado from @CapturedColumnList and all should work as expected.

Read the full discussion online.

To add a post to this discussion, reply to this email (StandardEditionCDC@discussions.codeplex.com)

To start a new discussion for this project, email StandardEditionCDC@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com




--

Sebastian Correa

Sistemas
AJUSTEV S.A.S
Cel. Sistemas: 3006470129
PBX. Ajustev: 381 77 77 Ext. 104
Calle 31 # 43A - 72 Barrio Clombia, Medellín

Aug 15, 2012 at 8:52 AM

I've managed to fix the error, though not elegantly but it works. 

1st you'll need to modify the storedproc "sp_StandardCDC_enable_table" with the following SQL

/****** Object:  StoredProcedure [dbo].[sp_StandardCDC_enable_table]    Script Date: 08/15/2012 16:51:58 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- Batch submitted through debugger: SQLQuery11.sql|7|0|C:\Users\josephjansen\AppData\Local\Temp\~vs806F.sql/*Name: sp_StandardCDC_enable_tableDescription: Creates table level objects needed to  support Standard Edition Change Data CaptureCreated By: Whitney Weaver, Magenic TechnologiesCreated On: 2009-06-25History:Initials Date Description======== ========== =====================WMW 2009-06-25 Initial versionWMW 2011-08-24 Corrected bug in @CapturedColumnList handling Added existance check for columns passed into @CapturedColumnList Added check for primary key existance in @CapturedColumnList*/ALTER PROCEDURE [dbo].[sp_StandardCDC_enable_table](@SourceSchema sysname,@SourceName sysname,@RoleName sysname,@CapturedColumnList nvarchar(max),@FileGroupName sysname,@CaptureUser bit)ASBEGIN SET NOCOUNT ON;
    -- Restrict enabling of change data capture for the table to sysadmin or db_owner    IF (ISNULL(is_srvrolemember('sysadmin'),0) = 0)    BEGIN IF (ISNULL(is_srvrolemember('db_owner'),0) = 0) BEGIN RAISERROR(22902, 16, -1); RETURN 1; END    END;        --Check for filegroup and exit if not existing    IF @FileGroupName IS NOT NULL AND NOT EXISTS (SELECT 1 FROM sys.filegroups WHERE name = @FileGroupName)    BEGIN RAISERROR('Filegroup does not exist, please check requested name', 16, 1);    END;        --Check existance of source object    IF OBJECT_ID((@SourceSchema + '.' + @SourceName), 'U') IS NULL    BEGIN RAISERROR('Table does not exist in current database', 16, 1); RETURN 1;    END;        --Check existance of scdc schema object    IF OBJECT_ID(('scdc.' + @SourceName), 'U') IS NOT NULL    BEGIN RAISERROR('Change table already exists for source object', 16, 1); RETURN 1;    END;
DECLARE @SourceTableDetail table ( ColumnName sysname ,Datatype sysname ,NullSpecification nvarchar(8) ,ColumnID int ,columnRowNumber int ,isPrimaryKey bit );
DECLARE @scdcTableTSQL nvarchar(max); DECLARE @scdcTriggerTSQL nvarchar(max); DECLARE @scdcSourceColumnList nvarchar(max); DECLARE @ErrorMessage nvarchar(4000); DECLARE @ErrorSeverity int; DECLARE @ErrorState int;        --Check existance of role and create if not existing (and not NULL)    IF @RoleName IS NOT NULL    BEGIN IF NOT EXISTS(SELECT 1 FROM sys.database_principals WHERE name = @RoleName AND type_desc = 'DATABASE_ROLE') BEGIN DECLARE @CreateRoleTSQL nvarchar(256) = 'CREATE ROLE ' + QUOTENAME(@RoleName) + ' AUTHORIZATION [dbo]'; EXECUTE sp_executeSQL @CreateRoleTSQL; END;    END;     BEGIN TRY BEGIN TRANSACTION    --Get column meta data details for creation of tracking table and trigger INSERT @SourceTableDetail (columnName, datatype, nullSpecification, ColumnID, columnRowNumber, isPrimaryKey) SELECT TQ.columName ,CASE WHEN TQ.dataTypeName = 'datetimeoffset' THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.scale) + ')' WHEN TQ.dataTypeName IN ('decimal', 'numeric') THEN TQ.dataTypeName + ' (' + CONVERT(varchar(10), TQ.[precision]) + ', ' + CONVERT(varchar(10), TQ.scale) + ')' WHEN TQ.dataTypeName IN('nchar', 'nvarchar') AND TQ.max_length >= 2 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length/2) + ')' WHEN TQ.dataTypeName = 'varchar' AND TQ.max_length > 0 THEN TQ.dataTypeName + '(' + CONVERT(varchar(10), TQ.max_length) + ')' WHEN TQ.dataTypeName IN('nvarchar', 'varchar', 'varbinary') AND TQ.max_length = -1 THEN TQ.dataTypeName + '(max)' ELSE TQ.dataTypeName END AS dataType ,TQ.nullSpecification ,TQ.column_id ,ROW_NUMBER() OVER (ORDER BY TQ.column_id ASC) ,TQ.isPrimaryKey FROM( SELECT c.name AS columName ,t.name AS dataTypeName ,c.max_length ,c.[precision] ,c.scale ,CASE  WHEN c.is_nullable = 0 THEN 'NOT NULL' ELSE 'NULL' END AS nullSpecification ,c.column_id ,CASE WHEN c.column_id = ic.column_id THEN 1 ELSE 0 END AS isPrimaryKey FROM sys.columns AS c INNER JOIN sys.objects AS o ON o.[object_id] = c.[object_id] INNER JOIN sys.types AS t ON t.system_type_id = c.system_type_id LEFT JOIN sys.indexes AS i ON i.[object_id] = c.[object_id]  LEFT JOIN sys.index_columns AS ic ON ic.[object_id] = c.[object_id]  AND ic.column_id = c.column_id AND ic.index_id = i.index_id AND i.is_primary_key = 1 WHERE o.name = @SourceName AND SCHEMA_NAME(o.[schema_id]) = @SourceSchema AND t.name NOT IN('sysname', 'timestamp') AND (c.name IN(SELECT cc.Element FROM utility.SplitArray(@CapturedColumnList, ',') AS cc) OR @CapturedColumnList IS NULL) )AS TQ ORDER BY TQ.column_id; --Confirm existance of primary key IF NOT EXISTS (SELECT 1 FROM @SourceTableDetail WHERE isPrimaryKey = 1) BEGIN RAISERROR('No primary key included in @CapturedColumnList', 16, 1); END; --Confirm all requested capture columns exist in table IF EXISTS( SELECT 1 FROM utility.SplitArray(@CapturedColumnList, ',') AS cc LEFT JOIN @SourceTableDetail AS td ON td.ColumnName = cc.Element WHERE td.columnRowNumber IS NULL ) BEGIN RAISERROR('@CapturedColumnList contains non-existent column(s)', 16, 1); END; --Create the tracking table SET @scdcTableTSQL = 'CREATE TABLE scdc.' + @SourceName + CHAR(10) +  '(' + CHAR(10)  + 'AuditType nvarchar(15) NOT NULL' + CHAR(10) + ',AuditDate datetime NOT NULL'; --Add column for tracking user if selected IF @CaptureUser = 1 BEGIN SET @scdcTableTSQL = @scdcTableTSQL + CHAR(10) + ',AuditUser nvarchar(500) NOT NULL'; END;
DECLARE tempC CURSOR LOCAL READ_ONLY FOR SELECT ColumnName ,DataType ,NullSpecification ,ColumnID ,columnRowNumber FROM @SourceTableDetail OPEN tempC DECLARE @ColumnName sysname, @DataType sysname, @NullSpecification nvarchar(8), @ColumnID int, @ColumnRowNumber int; FETCH NEXT FROM tempC INTO @ColumnName, @DataType, @NullSpecification, @ColumnID, @ColumnRowNumber WHILE @@FETCH_STATUS = 0 BEGIN IF @ColumnRowNumber = 1 BEGIN SET @scdcSourceColumnList = @ColumnName; END ELSE  BEGIN IF CHARINDEX(@ColumnName,@scdcSourceColumnList,1) = 0    BEGIN    SET @scdcSourceColumnList = @scdcSourceColumnList + ', ' + @ColumnName;    END END
IF CHARINDEX(@ColumnName,@scdcTableTSQL,1) = 0   BEGIN         SET @scdcTableTSQL = @scdcTableTSQL + CHAR(10) + ',' + @ColumnName + SPACE(4) + @DataType + SPACE(4) + @NullSpecification;   END  FETCH NEXT FROM tempC INTO @ColumnName, @DataType, @NullSpecification, @ColumnID, @ColumnRowNumber END CLOSE tempC DEALLOCATE tempC;
--Build the CREATE TABLE statement appending file group SET @scdcTableTSQL = @scdcTableTSQL + CHAR(10) + ') ON' + SPACE(1) + QUOTENAME(COALESCE(@FileGroupName, 'PRIMARY')) + ';';
--Execute the CREATE TABLE statement EXECUTE sp_executeSQL @scdcTableTSQL;
--Check existance of trigger and drop if existing IF EXISTS(SELECT 1 FROM sys.triggers WHERE name = 'tr' + @SourceName) BEGIN DECLARE @DropTriggerTSQL nvarchar(4000) = 'DROP TRIGGER ' + @SourceSchema + '.tr' + @SourceName + ';'; --Execute the DROP TRIGGER statement EXECUTE sp_executesql @DropTriggerTSQL; END; --Create the trigger, inserting into tracking table SET @scdcTriggerTSQL = 'CREATE TRIGGER ' + @SourceSchema + '.tr' + @SourceName + CHAR(10) + 'ON ' + @SourceSchema + '.' + @SourceName + CHAR(10) +  + 'FOR INSERT, UPDATE, DELETE' + CHAR(10) + 'AS' + CHAR(10) + 'BEGIN' + CHAR(10) + ' SET NOCOUNT ON;' + CHAR(10) + ' DECLARE @inserted int = (SELECT COUNT(1) FROM inserted);' + CHAR(10) + ' DECLARE @deleted int = (SELECT COUNT(1) FROM deleted);' + CHAR(10) + ' DECLARE @auditTime datetimeoffset(7) = sysdatetimeoffset()' + CHAR(10) + CHAR(10) + ' IF @inserted > 0 AND @deleted = 0' + CHAR(10) + ' BEGIN' + CHAR(10) + ' INSERT scdc.' + @SourceName + '(AuditType, AuditDate, ' + CASE WHEN @CaptureUser = 1 THEN 'AuditName, ' ELSE '' END + @scdcSourceColumnList + ')' + CHAR(10) + ' SELECT ' + QUOTENAME('Inserted', '''') + ', GETDATE(), '  + CASE WHEN @CaptureUser = 1 THEN 'SYSTEM_USER, ' ELSE '' END + @scdcSourceColumnList + CHAR(10) + ' FROM inserted;' + CHAR(10) + ' END' + CHAR(10) + CHAR(10) + ' IF @inserted > 0 AND @deleted > 0' + CHAR(10) + ' BEGIN' + CHAR(10) + ' INSERT scdc.' + @SourceName + '(AuditType, AuditDate, '  + CASE WHEN @CaptureUser = 1 THEN 'AuditName, ' ELSE '' END + @scdcSourceColumnList + ')' + CHAR(10) + ' SELECT ' + QUOTENAME('Update (before)', '''') + ', GETDATE(), '  + CASE WHEN @CaptureUser = 1 THEN 'SYSTEM_USER, ' ELSE '' END + @scdcSourceColumnList + CHAR(10) + ' FROM deleted;' + CHAR(10) + CHAR(13) + CHAR(10) + ' INSERT scdc.' + @SourceName + '(AuditType, AuditDate, '  + CASE WHEN @CaptureUser = 1 THEN 'AuditName, ' ELSE '' END + @scdcSourceColumnList + ')' + CHAR(10) + ' SELECT ' + QUOTENAME('Update (after)', '''') + ', GETDATE(), '  + CASE WHEN @CaptureUser = 1 THEN 'SYSTEM_USER, ' ELSE '' END + @scdcSourceColumnList + CHAR(10) + ' FROM inserted;' + CHAR(10) + ' END' + CHAR(10) + CHAR(13) + ' IF @inserted = 0 AND @deleted > 0' + CHAR(10) + ' BEGIN' + CHAR(10) + ' INSERT scdc.' + @SourceName + '(AuditType, AuditDate, '  + CASE WHEN @CaptureUser = 1 THEN 'AuditName, ' ELSE '' END + @scdcSourceColumnList + ')' + CHAR(10) + ' SELECT ' + QUOTENAME('Delete', '''') + ', GETDATE(), '  + CASE WHEN @CaptureUser = 1 THEN 'SYSTEM_USER, ' ELSE '' END + @scdcSourceColumnList + CHAR(10) + ' FROM deleted;' + CHAR(10) + ' END' + CHAR(10) + 'END;';
--Execute the CREATE TRIGGER statement EXECUTE sp_executeSQL @scdcTriggerTSQL;     --Insert the the source object into tracking meta data table INSERT INTO scdc.change_tables (name, [filegroup_name], create_date) VALUES (@SourceName, @FileGroupName, GETDATE()); --Insert the source object tracked columns into meta data table INSERT INTO scdc.change_table_columns (table_name, column_name, column_id, data_type, create_date) SELECT @SourceName ,d.ColumnName ,d.ColumnID ,d.Datatype ,GETDATE() FROM @SourceTableDetail AS d ORDER BY d.ColumnID; --Check for existance of clean job and create if not existing IF  NOT EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'SCDC_ClearTrackingData') BEGIN DECLARE @DatabaseName sysname = DB_NAME(); DECLARE @ReturnCode INT SELECT @ReturnCode = 0
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Standard Edition Change Data Capture]' AND category_class=1) BEGIN EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Standard Edition Change Data Capture]' END
DECLARE @jobId BINARY(16) EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'SCDC_ClearTrackingData',  @enabled=1,  @notify_level_eventlog=0,  @notify_level_email=0,  @notify_level_netsend=0,  @notify_level_page=0,  @delete_level=0,  @description=N'The job clears the tables found in scdc.change_tables for a given threshold.',  @category_name=N'[Uncategorized (Local)]',  @owner_login_name=N'sa', @job_id = @jobId OUTPUT
--Add job step calling sp_StandardCDC_cleanup_table EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'ClearTable',  @step_id=1,  @cmdexec_success_code=0,  @on_success_action=1,  @on_success_step_id=0,  @on_fail_action=2,  @on_fail_step_id=0,  @retry_attempts=0,  @retry_interval=0,  @os_run_priority=0, @subsystem=N'TSQL',  @command=N'EXECUTE dbo.sp_StandardCDC_cleanup_table NULL --Table name (Default == all) ,NULL --Hours threshold to retain (Default == 6)',  @database_name=@DatabaseName,  @flags=0
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'SCDC_CleanupSchedule',  @enabled=1,  @freq_type=4,  @freq_interval=2,  @freq_subday_type=1,  @freq_subday_interval=0,  @freq_relative_interval=0,  @freq_recurrence_factor=0,  @active_start_date=20090626,  @active_end_date=99991231,  @active_start_time=230000,  @active_end_time=235959,  @schedule_uid=N'be53ed91-31e8-47d5-9d82-022cc81bf9ab'
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)' END; COMMIT TRANSACTION; END TRY BEGIN CATCH IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION; SELECT @ErrorMessage = 'Error on line ' + CONVERT(nvarchar(10), ERROR_LINE()) + ': ' + ERROR_MESSAGE() ,@ErrorSeverity = ERROR_SEVERITY() ,@ErrorState = ERROR_STATE(); RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState); END CATCH;END;

Aug 17, 2012 at 1:46 AM
Edited Aug 17, 2012 at 1:46 AM

hi first of all thanks for your reply

 

but i copied and pasted in sql and i couldnt understand, may be you can send me the code by email, or send me the .sql file

 

i really appreciatte your help

 

many many thanks in advanced

 

my email is sebastian.correa@ajustev.com or sebastian11c@gmail.com

 

kind regards

Aug 22, 2012 at 2:39 PM

The error "Invalid column name 'AuditName'." is caused by a bug in sp_StandardCDC_enable_table

If you pass in @CaptureUser=1, the create table script that gets fired creates a column called "AuditUser", but the part of the script that creates the trigger tries to refer to a column called "AuditName".  Standardising the name to one or the other will fix the problem.  "AuditUser" occurs once in the procedure, "AuditName" appears four times.  A quick search and replace will fix this.

May 27, 2014 at 2:58 PM
Hi,

I have executed sp_StandardCDC_enable_table, But getting error.


exec sp_StandardCDC_enable_table
@SourceSchema='dbo',
@SourceName='CashOrderTrn',
@RoleName=null,
@CapturedColumnList=null,
@FileGroupName='primary',
@CaptureUser=0



Msg 50000, Level 15, State 1, Procedure sp_StandardCDC_enable_table, Line 366
Error on line 67: Incorrect syntax near the keyword 'VALUES'.

any help please.


Any document on this would be great for us.