I've run cdc for incremental loading in several projects and I've found that it a) has some bugs, plus b) it has performance issues with larger datasets. In a MS Connect item I've reported these issues to Microsoft, including a procedure generating alternative functions for the fn_cdc_get_net_changes_<capture_instance>() functions that do return correct results to illustrate the problems. The attachment does however not (yet) show in Connect, so I share it here too hoping you can all vote-up my connect item for MS to seriously consider fixing the log reader....
Below procedure is the attachment I've sent to MS. It generates alternative fn_cdc_get_net_changes_...() functions that will not return superfluous rows with __$operation = 1 generated from deferred updates. The generated functions are highly optimised to be as fast as, if no faster than, MS' "fixed" version. However if your data has a lot of 'deleted' rows (__$operation equals 1) the fix requires an additional scan on the _CT table MS' version does not require, so in these situations the fix may cost you a little performance. The better solution would be MS fixing the log reader to never put defered updates as pairs of 1 & 2 into the _CT tables.... In which case the fn_cdc_get_net_changes functions can get really blazing fast, even for large data sets!CREATE PROCEDURE [dbo].[spGenerateCustomCDCnetFunctions]
as
begin
set nocount on;
if object_id('tempdb..#definitions') is not null
drop table #definitions;
raiserror('Generating function bodies...', 0, 0) with nowait;
select
ct.object_id,
'[cdc].' + quotename('fn_rpr_get_net_changes_' + ct.capture_instance ) as functionName,
N'CREATE FUNCTION [cdc].' + quotename('fn_rpr_get_net_changes_' + ct.capture_instance)
+ s.crlf + N'( @from_lsn binary(10),'
+ s.crlf + N' @to_lsn binary(10),'
+ s.crlf + N' @row_filter_option nvarchar(30)'
+ s.crlf + N')'
+ s.crlf + N'returns table'
+ s.crlf + N'return'
+ s.crlf + N''
+ s.crlf + N' select'
+ s.crlf + N' NULL as __$start_lsn,'
+ s.crlf + N' NULL as __$operation,'
+ s.crlf + N' NULL as __$update_mask'
+ (
select N','
+ s.crlf + N' NULL as ' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' where ( [sys].[fn_cdc_check_parameters]( N' + quotename(ct.capture_instance, '''') + ', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 0)'
+ s.crlf + N''
+ s.crlf + N' union all'
+ s.crlf + N''
+ s.crlf + N''
+ s.crlf + N' select'
+ s.crlf + N' l.[__$start_lsn],'
+ s.crlf + N' case'
+ s.crlf + N' when l.__$operation = 1 then 1'
+ s.crlf + N' when l.__$operation = 2 then 5'
+ s.crlf + N' when l.__$operation = 4 then 5'
+ s.crlf + N' end as __$operation,'
+ s.crlf + N' NULL as [__$update_mask]'
+ (
select N','
+ s.crlf + N' l.' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' from ('
+ s.crlf + N' select top (1) NULL as c'
+ s.crlf + N' from ('
+ s.crlf + N' select'
+ s.crlf + N' lower(rtrim(ltrim(@row_filter_option))),'
+ s.crlf + N' N' + quotename( ct.capture_instance, '''')
+ s.crlf + N' ) p ([filter_option], [capture_instance])'
+ s.crlf + N' where p.[filter_option] = N''all with merge'''
+ s.crlf + N' and [sys].[fn_cdc_check_parameters]( p.[capture_instance], @from_lsn, @to_lsn, p.filter_option, 1) = 1'
+ s.crlf + N' ) c'
+ s.crlf + N' cross join ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l -- Last operation per key value.'
+ s.crlf + N' inner join ('
+ s.crlf + N' select'
+ (
select
s.crlf + N' m.' + quotename(ic.column_name) + ',' as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' max(m.__$seqval) as __$seqval_max, -- Sequence value for Last operation.'
+ s.crlf + N' min(m.__$seqval) as __$seqval_min -- Sequence value for First operation.'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' m'
+ s.crlf + N' where m.__$start_lsn >= @from_lsn'
+ s.crlf + N' and m.__$start_lsn <= @to_lsn'
+ s.crlf + N' and m.__$operation in (1,2,4)'
+ s.crlf + N' group by'
+ stuff((
select
N','
+ s.crlf + N' m.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 1, '')
+ s.crlf + N' ) x on ('
+ stuff((
select N' and x.' + quotename(ic.column_name) + ' = l.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 5, '') + N' and x.__$seqval_max = l.__$seqval)'
+ s.crlf + N' where l.__$start_lsn >= @from_lsn'
+ s.crlf + N' and l.__$start_lsn <= @to_lsn'
+ s.crlf + N' and (l.__$operation = 4 -- New values for updates.'
+ s.crlf + N' or l.__$operation = 2 -- Inserted values or New values for defered updates.'
+ s.crlf + N' or (l.__$operation = 1 -- Old values for defered updates or deletes.'
+ s.crlf + N' and not exists ('
+ s.crlf + N' -- Suppress all deletes that are the old values part of a defered update.'
+ s.crlf + N' -- We only use the other part of the defered update, the row with __$operation = 2.'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l3'
+ s.crlf + N' where'
+ stuff((
select
+ s.crlf + N' and l3.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 32, '')
+ s.crlf + N' and l3.__$seqval = x.__$seqval_max'
+ s.crlf + N' and l3.__$operation = 2'
+ s.crlf + N' )'
+ s.crlf + N' and not exists ('
+ s.crlf + N' -- If there is only 1 sequence for row ''l'', we don''t need to check for previous'
+ s.crlf + N' -- actions (hence the not x.__$seqval_max = x.__$seqval_min). But if multiple'
+ s.crlf + N' -- sequences do exist, we need to suppress those deletes for rows that were'
+ s.crlf + N' -- first inserted within this same range (__$seqval_min). Net result should'
+ s.crlf + N' -- look as if the row never existed at all. But again, we need to exclude'
+ s.crlf + N' -- deferred updates here too: if both __$operation = 1 and __$operation = 2'
+ s.crlf + N' -- exists it is a defered update instead of an insert.'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l4'
+ s.crlf + N' where not x.__$seqval_max = x.__$seqval_min'
+ (
select
+ s.crlf + N' and l4.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' and l4.__$seqval = x.__$seqval_min'
+ s.crlf + N' and l4.__$operation in (1, 2)'
+ s.crlf + N' having min(l4.__$operation) = 2'
+ s.crlf + N' )'
+ s.crlf + N' )'
+ s.crlf + N' )'
+ s.crlf + N''
+ s.crlf + N' union all'
+ s.crlf + N''
+ s.crlf + N' select'
+ s.crlf + N' l.[__$start_lsn],'
+ s.crlf + N' case'
+ s.crlf + N' when l.__$operation = 1 -- We have already excluded all'
+ s.crlf + N' then 1 -- deletes of rows that did not'
+ s.crlf + N' -- exist before the batch started'
+ s.crlf + N' -- in the where conditions. So any'
+ s.crlf + N' -- delete(1) __$operation here must'
+ s.crlf + N' -- be a net delete(1) of a row that'
+ s.crlf + N' -- existed before the batch.'
+ s.crlf + N' when exists ('
+ s.crlf + N' -- If the l.__$operation is not a delete(1),'
+ s.crlf + N' -- the net __$operation depends on what the'
+ s.crlf + N' -- first __$operation in this batch is. If'
+ s.crlf + N' -- the first __$operation is an insert(2), and'
+ s.crlf + N' -- not one that is part of a defered update,'
+ s.crlf + N' -- then the net __$operation is an insert(2).'
+ s.crlf + N' -- Otherwise the row existed before this batch'
+ s.crlf + N' -- started and thus the net __$operation is an'
+ s.crlf + N' -- update(4). For a defered update both an'
+ s.crlf + N' -- insert(2) and a delete(1) __$operation'
+ s.crlf + N' -- exists, for an insert only an insert(2)'
+ s.crlf + N' -- __$operation exists. So I''m looking for'
+ s.crlf + N' -- a single __$operation 2 on the first operation.'
+ s.crlf + N' -- I can''t optimize for the single operation'
+ s.crlf + N' -- situation here, because the last operation'
+ s.crlf + N' -- may be a defered update, giving'
+ s.crlf + N' -- l.__$operation = 2 whereas the net result'
+ s.crlf + N' -- should be update(4).'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l4'
+ s.crlf + N' where'
+ stuff((
select
+ s.crlf + N' and l4.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 26, '')
+ s.crlf + N' and l4.__$seqval = x.__$seqval_min'
+ s.crlf + N' and l4.__$operation in (1, 2)'
+ s.crlf + N' having min(l4.__$operation) = 2'
+ s.crlf + N' )'
+ s.crlf + N' then 2'
+ s.crlf + N' else 4'
+ s.crlf + N' end as __$operation,'
+ s.crlf + N' NULL as [__$update_mask]'
+ (
select N','
+ s.crlf + N' l.' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' from ('
+ s.crlf + N' select top (1) NULL as c'
+ s.crlf + N' from ('
+ s.crlf + N' select'
+ s.crlf + N' lower(rtrim(ltrim(@row_filter_option))),'
+ s.crlf + N' N' + quotename( ct.capture_instance, '''')
+ s.crlf + N' ) p ([filter_option], [capture_instance])'
+ s.crlf + N' where p.[filter_option] = N''all'''
+ s.crlf + N' and [sys].[fn_cdc_check_parameters]( p.[capture_instance], @from_lsn, @to_lsn, p.filter_option, 1) = 1'
+ s.crlf + N' ) c'
+ s.crlf + N' cross join ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l -- Last operation per key value.'
+ s.crlf + N' inner join ('
+ s.crlf + N' select'
+ (
select
+ s.crlf + N' m.' + quotename(ic.column_name) + ',' as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' max(m.__$seqval) as __$seqval_max, -- Sequence value for Last operation.'
+ s.crlf + N' min(m.__$seqval) as __$seqval_min -- Sequence value for First operation.'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' m'
+ s.crlf + N' where m.__$start_lsn >= @from_lsn'
+ s.crlf + N' and m.__$start_lsn <= @to_lsn'
+ s.crlf + N' and m.__$operation in (1,2,4)'
+ s.crlf + N' group by'
+ stuff((
select
N','
+ s.crlf + N' m.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 1, '')
+ s.crlf + N' ) x on ('
+ stuff((
select N' and x.' + quotename(ic.column_name) + ' = l.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 5, '') + N' and x.__$seqval_max = l.__$seqval)'
+ s.crlf + N' where l.__$start_lsn >= @from_lsn'
+ s.crlf + N' and l.__$start_lsn <= @to_lsn'
+ s.crlf + N' and (l.__$operation = 4 -- New values for updates.'
+ s.crlf + N' or l.__$operation = 2 -- Inserted values or New values for defered updates.'
+ s.crlf + N' or (l.__$operation = 1 -- Deleted values or Old values for defered updates.'
+ s.crlf + N' and not exists ('
+ s.crlf + N' -- Exclude all deletes that are the old values part of a defered update.'
+ s.crlf + N' -- We will only use the other part of the defered update, the row with'
+ s.crlf + N' -- l.__$operation = 2.'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l3'
+ s.crlf + N' where'
+ stuff((
select
+ s.crlf + N' and l3.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 32, '')
+ s.crlf + N' and l3.__$seqval = x.__$seqval_max'
+ s.crlf + N' and l3.__$operation = 2'
+ s.crlf + N' )'
+ s.crlf + N' and not exists ('
+ s.crlf + N' -- If there is only 1 operation for row ''l'', we don''t need to check for previous'
+ s.crlf + N' -- actions (hence the not x.__$seqval_max = x.__$seqval_min). But if multiple'
+ s.crlf + N' -- sequences do exist, we need to exclude those deletes for rows that were'
+ s.crlf + N' -- first inserted within this same range (__$seqval_min). Net result should'
+ s.crlf + N' -- look as if the row never existed at all. But again, we need to account for'
+ s.crlf + N' -- defered updates here too: if both __$operation = 1 and __$operation = 2'
+ s.crlf + N' -- exists the first operation is a defered update instead of an insert and'
+ s.crlf + N' -- we should include the delete(1) operation l.'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l4'
+ s.crlf + N' where not x.__$seqval_max = x.__$seqval_min'
+ (
select
+ s.crlf + N' and l4.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' and l4.__$seqval = x.__$seqval_min'
+ s.crlf + N' and l4.__$operation in (1, 2)'
+ s.crlf + N' having min(l4.__$operation) = 2'
+ s.crlf + N' )'
+ s.crlf + N' )'
+ s.crlf + N' )'
+ s.crlf + N''
+ s.crlf + N' union all'
+ s.crlf + N''
+ s.crlf + N' select'
+ s.crlf + N' l.[__$start_lsn],'
+ s.crlf + N' case'
+ s.crlf + N' when l.__$operation = 1'
+ s.crlf + N' then 1'
+ s.crlf + N' when exists ('
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l4'
+ s.crlf + N' where'
+ stuff((
select
+ s.crlf + N' and l4.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 26, '')
+ s.crlf + N' and l4.__$seqval = x.__$seqval_min'
+ s.crlf + N' and l4.__$operation in (1, 2)'
+ s.crlf + N' having min(l4.__$operation) = 2'
+ s.crlf + N' )'
+ s.crlf + N' then 2'
+ s.crlf + N' else 4'
+ s.crlf + N' end as __$operation,'
+ s.crlf + N' x.__$update_mask'
+ (
select N','
+ s.crlf + N' l.' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' from ('
+ s.crlf + N' select top (1) NULL as c'
+ s.crlf + N' from ('
+ s.crlf + N' select'
+ s.crlf + N' lower(rtrim(ltrim(@row_filter_option))),'
+ s.crlf + N' N' + quotename( ct.capture_instance, '''')
+ s.crlf + N' ) p ([filter_option], [capture_instance])'
+ s.crlf + N' where p.[filter_option] = N''all with mask'''
+ s.crlf + N' and [sys].[fn_cdc_check_parameters]( p.[capture_instance], @from_lsn, @to_lsn, p.filter_option, 1) = 1'
+ s.crlf + N' ) c'
+ s.crlf + N' cross join ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l -- Last operation per key value.'
+ s.crlf + N' inner join ('
+ s.crlf + N' select'
+ (
select
+ s.crlf + N' m.' + quotename(ic.column_name) + ',' as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' max(m.__$seqval) as __$seqval_max, -- Sequence value for Last operation.'
+ s.crlf + N' min(m.__$seqval) as __$seqval_min, -- Sequence value for First operation.'
+ s.crlf + N' [sys].[ORMask](m.__$update_mask) as __$update_mask -- bitwise OR of all update_masks.'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' m'
+ s.crlf + N' where m.__$start_lsn >= @from_lsn'
+ s.crlf + N' and m.__$start_lsn <= @to_lsn'
+ s.crlf + N' and m.__$operation in (1,2,4)'
+ s.crlf + N' group by'
+ stuff((
select
N','
+ s.crlf + N' m.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 1, '')
+ s.crlf + N' ) x on ('
+ stuff((
select N' and x.' + quotename(ic.column_name) + ' = l.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 5, '') + N' and x.__$seqval_max = l.__$seqval)'
+ s.crlf + N' where l.__$start_lsn >= @from_lsn'
+ s.crlf + N' and l.__$start_lsn <= @to_lsn'
+ s.crlf + N' and (l.__$operation = 4 -- New values for updates.'
+ s.crlf + N' or l.__$operation = 2 -- Inserted values or New values for defered updates.'
+ s.crlf + N' or (l.__$operation = 1 -- Old values for defered updates or deletes.'
+ s.crlf + N' and not exists ('
+ s.crlf + N' -- Exclude all deletes that are the old values part of a defered update.'
+ s.crlf + N' -- We only use the other part of the defered update, the row with __$operation = 2.'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l3'
+ s.crlf + N' where'
+ stuff((
select
+ s.crlf + N' and l3.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 32, '')
+ s.crlf + N' and l3.__$seqval = x.__$seqval_max'
+ s.crlf + N' and l3.__$operation = 2'
+ s.crlf + N' )'
+ s.crlf + N' and not exists ('
+ s.crlf + N' -- If there is only 1 sequence for row ''l'', we don''t need to check for previous'
+ s.crlf + N' -- actions (hence the not x.__$seqval_max = x.__$seqval_min). But if multiple'
+ s.crlf + N' -- sequences do exist, we need to exclude those deletes for rows that were'
+ s.crlf + N' -- first inserted within this same range (__$seqval_min). Net result should'
+ s.crlf + N' -- look as if the row never existed at all. But again, we need to account for'
+ s.crlf + N' -- defered updates here too: if both __$operation = 1 and __$operation = 2'
+ s.crlf + N' -- exists the first operation is a defered update instead of an insert and'
+ s.crlf + N' -- we should include the delete(1) operation l.'
+ s.crlf + N' select *'
+ s.crlf + N' from ' + quotename(object_schema_name(ct.object_id)) + '.' + quotename(object_name(ct.object_id)) + ' l4'
+ s.crlf + N' where not x.__$seqval_max = x.__$seqval_min'
+ (
select
+ s.crlf + N' and l4.' + quotename(ic.column_name) + ' = x.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ s.crlf + N' and l4.__$seqval = x.__$seqval_min'
+ s.crlf + N' and l4.__$operation in (1, 2)'
+ s.crlf + N' having min(l4.__$operation) = 2'
+ s.crlf + N' )'
+ s.crlf + N' )'
+ s.crlf + N' )' as [definition]
into #definitions
from (
select char(0x0d) + char(0x0a)
) s (crlf)
cross join cdc.change_tables ct
where ct.supports_net_changes = 1;
if object_id('tempdb..#todo') is not null
drop table #todo;
raiserror('Comparing to existing functions...', 0, 0) with nowait;
select
d.functionName,
case when object_id(d.functionName) is null
then 'Missing'
else
case when nullif(checksum(x.txtNew),checksum(x.txtOld)) is not null
then 'Different'
else 'OK'
end
end as [Status],
d.definition
into #todo
from #definitions d
cross apply (
select
replace(
replace(
replace(
replace(
replace(
replace(
d.definition,
char(0x0d), ' '),
char(0x0a), ' '),
char(9), ' '),
' ', ' ' + char(9)),
char(9) + ' ', ''),
char(9), '') as txtNew,
replace(
replace(
replace(
replace(
replace(
replace(
(select m.definition from sys.sql_modules m where object_id = object_id(d.functionName)),
char(0x0d), ' '),
char(0x0a), ' '),
char(9), ' '),
' ', ' ' + char(9)),
char(9) + ' ', ''),
char(9), '') as txtOld
) x;
declare cur cursor local fast_forward
for
select
t.functionName,
case t.Status
when 'Different' then replace(t.definition, 'CREATE FUNCTION', 'ALTER FUNCTION')
when 'Missing' then t.definition
end
from #todo t
where nullif('OK', t.Status) is not null
order by t.functionName;
open cur;
while 1 = 1
begin
declare @functionName sysname;
declare @stmt nvarchar(max);
fetch next from cur into @functionName, @stmt;
if @@fetch_status = -1
break;
if @@fetch_status = 0
begin
raiserror('Processing %s...', 0, 0, @FunctionName) with nowait;
exec sp_executesql @stmt;
end
end
close cur;
deallocate cur;
raiserror('Done.', 0, 0) with nowait;
end
exec [dbo].[spGenerateCustomCDCnetFunctions]