Generating Rollback Proc with CDC

• matt6288

  SSCertifiable

  Points: 5058

  More actions

  October 12, 2009 at 6:28 am

  #210770

  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

  More actions

  May 23, 2016 at 4:40 pm

  #1880903

  Thanks for the script.