Erstellen der Funktion zum Abrufen der Änderungsdaten

Die gespeicherte Systemprozedur sys.sp_cdc_generate_wrapper_function generiert Skripts zur Erstellung von Wrapperfunktionen, die von Integration Services-Paketen verwendet werden.

Der folgende Code stellt die ersten Zeilen der Definition der gespeicherten Prozedur dar:

CREATE PROCEDURE sys.sp_cdc_generate_wrapper_function

(

@capture\_instance sysname = null

@closed\_high\_end\_point bit = 1,

@column\_list = null,

@update\_flag\_list = null

)

Alle Parameter für die gespeicherte Prozedur sind optional. Wenn Sie die gespeicherte Prozedur ohne die Bereitstellung von Werten für einen der Parameter aufrufen, erstellt die gespeicherte Prozedur Wrapperfunktionen für alle Aufzeichnungsinstanzen, auf die Sie Zugriff haben.

Hinweis
Weitere Informationen über die Syntax dieser gespeicherten Prozedur und ihre Parameter finden Sie unter sys.sp_cdc_generate_wrapper_function (Transact-SQL).

Die gespeicherte Funktion generiert immer eine Wrapperfunktion, um alle Änderungen aus allen Aufzeichnungsinstanzen zurückzugeben. Wenn der @supports\_net\_changes-Parameter während der Erstellung der Aufzeichnungsinstanz festgelegt wurde, generiert die gespeicherte Prozedur außerdem eine Wrapperfunktion, die die Nettoänderungen von jeder entsprechenden Aufzeichnungsinstanz zurückgibt.

Die gespeicherte Prozedur gibt ein Resultset mit zwei Spalten zurück:

• Den Namen der Wrapperfunktion, die von der gespeicherten Prozedur generiert wurde. Diese gespeicherte Prozedur ruft den Funktionsnamen vom Namen der Aufzeichnungsinstanz ab. (Der Funktionsname lautet „fn_all_changes_“, gefolgt vom Namen der Aufzeichnungsinstanz. Das Präfix, das für die Funktion für Nettoänderungen verwendet wird, lautet, wenn es erstellt wird, „fn_net_changes_“.)

• Die CREATE-Anweisung für die Wrapperfunktion.

Normalerweise verwendet ein Entwickler eine INSERT...EXEC-Anweisung, um die gespeicherte Prozedur sys.sp_cdc_generate_wrapper_function aufzurufen und die Skripts zu speichern, die die gespeicherte Prozedur in einer temporären Tabelle erstellt. Anschließend könnte jedes Skript einzeln ausgewählt und ausgeführt werden, um die entsprechende Wrapperfunktion zu erstellen. Ein Entwickler könnte jedoch auch einen Satz von SQL-Befehlen verwenden, um alle CREATE-Skripts auszuführen, wie im folgenden Beispielcode dargestellt:

create table #wrapper_functions
      (function_name sysname, create_stmt nvarchar(max))
insert into #wrapper_functions
exec sys.sp_cdc_generate_wrapper_function

declare @stmt nvarchar(max)
declare #hfunctions cursor local fast_forward for 
      select create_stmt from #wrapper_functions
open #hfunctions
fetch #hfunctions into @stmt
while (@@fetch_status <> -1)
begin
      exec sp_executesql @stmt
      fetch #hfunctions into @stmt
end
close #hfunctions
deallocate #hfunctions

Um die Zeitachse der aufgezeichneten Änderungsdaten systematisch abzuarbeiten, gehen die generierten Wrapperfunktionen davon aus, dass der @end\_time-Parameter für ein Intervall der @start\_time-Parameter für das folgende Intervall ist. Wenn diese Konvention eingehalten wird, kann die generierte Wrapperfunktion folgende Aufgaben ausführen:

• Zuordnung der Datums-/Zeitwerte zu den intern verwendeten LSN-Werten

• Sicherstellen, dass keine Daten verloren gehen oder wiederholt werden

Zur Vereinfachung der Abfrage aller Zeilen einer Änderungstabelle unterstützt die generierte Wrapperfunktion auch folgende Konventionen:

• Wenn der „@start_time“-Parameter NULL ist, verwendet die Wrapperfunktion den niedrigsten LSN-Wert in der Aufzeichnungsinstanz als untere Begrenzung der Abfrage.

• Wenn der „@end_time“-Parameter NULL ist, verwendet die Wrapperfunktion den höchsten LSN-Wert in der Aufzeichnungsinstanz als obere Begrenzung der Abfrage.

Die meisten Benutzer sollten die von der gespeicherten Systemprozedur sys.sp_cdc_generate_wrapper_function erstellte Wrapperfunktion verwenden können. Wenn Sie die Wrapperfunktion anpassen möchten, müssen Sie jedoch die CREATE-Skripts anpassen, bevor Sie diese ausführen.

Wenn Ihr Paket die Wrapperfunktion aufruft, muss das Paket Werte für drei Parameter bereitstellen. Diese drei Parameter entsprechen den drei Parametern, die von den Change Data Capture-Funktionen verwendet werden. Dabei handelt es sich um folgende drei Parameter:

• Die Werte für Startdatum und -uhrzeit sowie für Enddatum und -uhrzeit für das Intervall. Während die Wrapperfunktionen Datums-/Zeitwerte als Endpunkte für das Abfrageintervall verwenden, verwenden die Change Data Capture-Funktionen zwei LSN-Werte als Endpunkte.

• Den Zeilenfilter. Für die Wrapperfunktionen und die Change Data Capture-Funktionen ist der @row\_filter\_option-Parameter identisch. Weitere Informationen finden Sie unter cdc.fn_cdc_get_all_changes_<capture_instance> (Transact-SQL) und cdc.fn_cdc_get_net_changes_<Aufzeichnungsinstanz> (Transact-SQL).

Das von den Wrapperfunktionen zurückgegebene Resultset enthält folgende Daten:

• Alle angeforderten Spalten der Änderungsdaten

• Eine Spalte mit dem Namen __CDC_OPERATION, die ein Feld mit einem oder zwei Zeichen verwendet, um den der Zeile zugeordneten Vorgang zu kennzeichnen. Folgende Werte sind für dieses Feld gültig: „I“ für insert (einfügen), „D“ für delete (löschen), „UO“ für update old values (alte Werte aktualisieren) und „UN“ für update new values (neue Werte aktualisieren).

• Aktualisierungsflags, wenn Sie diese anfordern, die als bit-Spalten hinter dem Vorgangscode in der von dem @update\_flag\_list-Parameter festgelegten Reihenfolge angezeigt werden. Diese Spalten werden bezeichnet, indem an den zugeordneten Spaltennamen „_uflag“ angehängt wird.

Wenn Ihr Paket eine Wrapperfunktion aufruft, die alle Änderungen abfragt, gibt die Wrapperfunktion außerdem die Spalten __CDC_STARTLSN und __CDC_SEQVAL zurück. Diese beiden Spalten sind die erste bzw. die zweite Spalte des Resultsets. Die Wrapperfunktion sortiert das Resultset außerdem auf der Grundlage dieser beiden Spalten.

CREATE function CDCSample.uf_Customer (
     @start_time datetime
    ,@end_time datetime
)
returns @Customer table (
     CustomerID int
    ,TerritoryID int
    ,CustomerType nchar(1)
    ,rowguid uniqueidentifier
    ,ModifiedDate datetime
    ,CDC_OPERATION varchar(1)
) as
begin
    declare @from_lsn binary(10), @to_lsn binary(10)

    if (@start_time is null)
        select @from_lsn = sys.fn_cdc_get_min_lsn('Customer')
    else
        select @from_lsn = sys.fn_cdc_increment_lsn(sys.fn_cdc_map_time_to_lsn('largest less than or equal',@start_time))

    if (@end_time is null)
        select @to_lsn = sys.fn_cdc_get_max_lsn()
    else
        select @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal',@end_time)

    if (@from_lsn = sys.fn_cdc_increment_lsn(@to_lsn))
        return

    -- Query for change data
    insert into @Customer
    select 
        CustomerID,    
        TerritoryID, 
        CustomerType, 
        rowguid, 
        ModifiedDate, 
        case __$operation
                when 1 then 'D'
                when 2 then 'I'
                when 4 then 'U'
                else null
         end as CDC_OPERATION
    from 
        cdc.fn_cdc_get_net_changes_Customer(@from_lsn, @to_lsn, 'all')

    return
end 
go

Obwohl die zuvor gezeigte vom Benutzer erstellte Tabellenwert-Funktion nur die __$operation-Spalte verwendet, gibt die cdc.fn_cdc_get_net_changes_<capture_instance>-Funktion für jede Änderungszeile vier Metadatenspalten zurück. Wenn Sie diese Werte in Ihrem Datenfluss verwenden möchten, können Sie diese als zusätzliche Spalten aus der Tabellenwert-Wrapperfunktion zurückgeben.

Weitere Informationen finden Sie unter cdc.fn_cdc_get_net_changes_<Aufzeichnungsinstanz> (Transact-SQL).