Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

sys.sp_cdc_enable_table does not consider additions or removal of columsn to the table Expand / Collapse
Author
Message
Posted Wednesday, October 09, 2013 12:51 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:48 AM
Points: 112, Visits: 48
I am building a CDC on my tables. However, I noticed, if I add a new column to the table being audited, the Audit table doesnot have this column. Instead, the only way I can see is to disable the CDC on the table and re-enable it, which menas the data sitting in the audit table is Lost.

Is there any way this can be done seamlessly in CDC?
--EXEC sys.sp_cdc_enable_db
--EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'AuditTest', @role_name = NULL

/*
Operation 1 = Delete
Operation 2 = Insert
Operation 3 = Old value before the operation
Operation 3 = New Value after the Operation
*/

CREATE TABLE dbo.AuditTest(RowID INT IDENTITY(1,1),VALUE VARCHAR(50),UserName VARCHAR(50), CreationDate DATETIME DEFAULT GETDATE(),ModifiedDate DATETIME)

EXEC sys.sp_cdc_enable_table @source_schema = 'dbo', -- sysname
@source_name = 'AuditTest', -- sysname
@capture_instance = NULL, -- sysname
@role_name = NULL -- sysname


INSERT INTO dbo.AuditTest
( VALUE ,UserName )
VALUES ( 'QQQ' ,'User123' )

-------THIS NEW COLUMN ADDED IS NOT AVAILABLE IN "dbo_AuditTest_CT"--------
ALTER TABLE AuditTest add TESTCol varchar(10)

UPDATE AuditTest SET Value='TESTINGzzz' WHERE Value='QQQ'

DELETE from AuditTest

select CASE
WHEN [__$operation]=1 THEN 'DELETE'
WHEN [__$operation]=2 THEN 'INSERT'
WHEN [__$operation]=3 THEN 'OLD VALUE'
WHEN [__$operation]=4 THEN 'NEW VALUE'
END AS OPERATION
,*
from cdc.dbo_AuditTest_CT

Also, what are the other ways we can Audit transactional changes to the tables?

Cheers
Ram
Post #1503267
Posted Friday, October 11, 2013 6:31 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 5:48 AM
Points: 112, Visits: 48
bump
Post #1503978
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse