USE [GURMEN] GO /****** Object: Table [dbr].[DBA_CDClog] Script Date: 14.11.2016 17:25:14 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbr].[DBA_CDClog] ( [DBA_CDClogID] [bigint] IDENTITY(1, 1) NOT NULL, [SeqValue] [binary](10) NOT NULL, [SchemaName] [nvarchar](50) NOT NULL, [TableName] [nvarchar](100) NOT NULL, [RowGuid] [uniqueidentifier] NOT NULL, [PKeyColumnName] [nvarchar](100) NOT NULL, [PKeyNewValue] [nvarchar](150) NULL, [PKeyOldValue] [nvarchar](150) NULL, [Operation] [char](1) NOT NULL, [Status] [char](1) NOT NULL, [LastUpdatedUserName] [nvarchar](100) NOT NULL, [CdcLogDate] [datetime] NOT NULL, [CDCInsertedDate] [datetime] NULL, [NebimLastUpdatedDate] [datetime] NULL, [NebimCreatedDate] [datetime] NULL, [IsLateInsertedRecord] [bit] NULL, CONSTRAINT [PK_DBA_CDClog] PRIMARY KEY CLUSTERED ( [DBA_CDClogID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 90) ON [CDCTBLOGFG], CONSTRAINT [UNQ_SeqValue] UNIQUE NONCLUSTERED ( [SeqValue] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , FILLFACTOR = 90) ON [NONCLUSTEREDIDXFG] ) ON [CDCTBLOGFG] GO ALTER TABLE [dbr].[DBA_CDClog] ADD CONSTRAINT [DF_DBA_CDClog_Status] DEFAULT ('P') FOR [Status] GO ALTER TABLE [dbr].[DBA_CDClog] ADD CONSTRAINT [DF_DBA_CDClog_CdcLogDate] DEFAULT (getdate()) FOR [CdcLogDate] GO ALTER TABLE [dbr].[DBA_CDClog] ADD CONSTRAINT [DF_IsLate] DEFAULT ((0)) FOR [IsLateInsertedRecord] GO -- Sonra dynamic sp mizi yazalım USE [GURMEN] GO /****** Object: StoredProcedure [dbr].[dba_ReplicatorAuto] Script Date: 14.11.2016 17:24:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbr].[dba_ReplicatorAuto] AS DECLARE @tableName NVARCHAR(100) , @sql NVARCHAR(MAX) DECLARE ReplicaTbls CURSOR FOR SELECT TableName FROM [dbr].[DBA_CaptureInstance] WHERE IsCDCEnabled = 1 OPEN ReplicaTbls; -- Perform the first fetch. FETCH NEXT FROM ReplicaTbls INTO @tableName; -- Check @@FETCH_STATUS to see if there are any more rows to fetch. WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @Primarkey NVARCHAR(150) = NULL , @Primarykey2 NVARCHAR(150) = NULL , @RowGuid NVARCHAR(50) = NULL , @operationValues NVARCHAR(10)
SELECT @RowGuid = RowGuidName FROM [dbr].[DBA_CaptureInstance] WHERE TableName = @tableName
SELECT @Primarkey = COALESCE(@Primarkey + '||', '') + CAST((LTRIM(RTRIM(column_name))) AS NVARCHAR(150)) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND ku.table_name = @tableName ORDER BY KU.ORDINAL_POSITION ASC;
SELECT @Primarykey2 = COALESCE(+ @Primarykey2 + '+' + '''||''' + '+', '') + CAST((LTRIM(RTRIM('CAST((' + column_name + ') AS VARCHAR(150))'))) AS NVARCHAR(150)) FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU ON TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND ku.table_name = @tableName ORDER BY KU.ORDINAL_POSITION ASC; -- 12 trBankLineCurrency tablosunda, Umit sadece UPDATE olan kayitlari istedi. 31-05-2016 -- Faturada ki ödeme kalemleri değiştirilen kayıtları alıyoruz. C.[__$operation] IN (4). IF @tableName = 'trBankLineCurrency' BEGIN SET @operationValues = '(4)' END ELSE BEGIN SET @operationValues = (SELECT '(1,2,4)') END SET @sql = 'INSERT INTO [dbr].[DBA_CDClog] (SeqValue, SchemaName, TableName, RowGuid,PKeyColumnName,PkeyNewValue,PkeyOldValue, Operation, LastUpdatedUserName, NebimCreatedDate, NebimLastUpdatedDate, CDCInsertedDate ) SELECT C.[__$seqval] AS SeqValue ,''dbo'' AS SchameName ,' + '''' + @tableName + '''' + ' AS TableName ,C.' + @RowGuid + ' AS RowGuid ,' + '''' + @Primarkey + '''' + ' AS PKeyColumnName ,CASE C.[__$operation] WHEN 1 THEN ' + @PrimaryKey2 + ' WHEN 2 THEN ' + @PrimaryKey2 + ' WHEN 4 THEN '''' END AS PkeyNewValue ,CASE C.[__$operation] WHEN 1 THEN '''' WHEN 2 THEN '''' WHEN 4 THEN ' + @PrimaryKey2 + ' END AS PkeyOldValue ,CASE C.[__$operation] WHEN 1 THEN ''D'' WHEN 2 THEN ''I'' WHEN 4 THEN ''U'' END AS Operation ,C.[LastUpdatedUserName] ,C.[CreatedDate] AS NebimCreatedDate ,C.[LastUpdatedDate] AS NebimLastUpdatedDate ,C.[CDCInsertedDate] AS CDCTableInsertedDate FROM [cdc].[dbo_' + @tableName + '_CT] C WITH(NOLOCK) INNER JOIN [cdc].[lsn_time_mapping] T WITH(NOLOCK) ON C.[__$start_lsn]=T.[start_lsn] WHERE C.[__$seqval] > ( SELECT MAX(SeqValue) FROM dbr.DBA_CDClog WHERE TableName = ' + '''' + @tableName + '''' + ' ) AND C.[__$operation] IN ' + @operationValues + ' AND C.[LastUpdatedUserName] NOT IN (''uates'',''adminREPADMIN'',''admine'',''REPKETM'',''REPRETM'') AND T.[tran_end_time] < DateAdd(minute, -1, getDate()) ' FETCH NEXT FROM ReplicaTbls INTO @tableName ; --PRINT (@sql+CHAR(13)+CHAR(10)+'GO') EXEC (@sql) END CLOSE ReplicaTbls; DEALLOCATE ReplicaTbls;
