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

Generating Rollback Proc with CDC Expand / Collapse
Author
Message
Posted Monday, October 12, 2009 6:28 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, November 14, 2014 2:01 PM
Points: 1,207, Visits: 1,277
Comments posted to this topic are about the item Generating Rollback Proc with CDC

I forgot to include the script to create a function that is used. So here it is:

/****** Object:  UserDefinedFunction [dbo].[fn_CombiningRowsForCDC]    Script Date: 10/12/2009 15:49:08 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_CombiningRowsForCDC]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[fn_CombiningRowsForCDC]
GO

CREATE FUNCTION [dbo].[fn_CombiningRowsForCDC]
(
@schema_name VARCHAR(30),
@table_name VARCHAR(100)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
--This function is used to get a comma separated list for the script.
--Once this has been ran once you can remove it if you like.

DECLARE @temp AS VARCHAR(MAX)
DECLARE @cdc_object_id AS INT

SELECT @cdc_object_id = CT.object_id
FROM cdc.change_tables CT
INNER JOIN sys.objects O ON CT.source_object_id = O.object_id
WHERE SCHEMA_NAME(O.schema_id) = @schema_name
AND OBJECT_NAME(O.object_id) = @table_name

SELECT @temp = COALESCE(@temp+',','')+[column_name] FROM cdc.captured_columns CC
WHERE object_id = @cdc_object_id

RETURN @temp
END
GO

Post #801522
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse