Generating Rollback Proc with CDC

  • matt6288

    SSCertifiable

    Points: 5058

    Comments posted to this topic are about the item Generating Rollback Proc with CDC[/url]

    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

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script.

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply