DEFINITIONS

Definitions More Info.
Definition ID16.119
TitleSQL
CategoryNOTES
Definitionbuyuk tablo sync esitleme-mesaj tablosu
Definition DescriptionUSE [msdb]
GO

/****** Object: Job [MessageTransfer_Sync_Job] Script Date: 5/29/2024 4:32:14 PM ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 5/29/2024 4:32:14 PM ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'MessageTransfer_Sync_Job',
@enabled=0,
@notify_level_eventlog=0,
@notify_level_email=0,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'KNSVM\sqladmin', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [1] Script Date: 5/29/2024 4:32:14 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'1',
@step_id=1,
@cmdexec_success_code=0,
@on_success_action=3,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @MesajHareketID bigint DECLARE @operation int DECLARE Numara_cursor CURSOR FOR
SELECT
E.MesajHareketID,
E.__$operation
FROM
[AtlantisDB].[cdc].[dbo_MesajHareketDB_CT] E WITH(NOLOCK)
INNER JOIN
[Messaging].[dbo].[MessageTransfer] M WITH(NOLOCK) ON E.MesajHareketID = M.Id
WHERE
E.CDC_IsProcessed_Aktarim = 0
AND E.__$operation IN (1, 4)
ORDER BY
E.__$start_lsn,
E.__$command_id,
E.__$seqval,
E.__$operation;
OPEN Numara_cursor;
FETCH NEXT
FROM
Numara_cursor INTO @MesajHareketID,
@operation -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
/* DELETE ISLEMI */
IF @operation = 1
BEGIN
DELETE FROM
[Messaging].[dbo].[MessageTransfer]
WHERE
Id = @MesajHareketID
UPDATE
[AtlantisDB].[cdc].[dbo_MesajHareketDB_CT]
SET
CDC_IsProcessed_Aktarim = 1,
CDC_ProcessesDate = GETDATE()
WHERE
MesajHareketID = @MesajHareketID
AND __$operation = 1
AND [CDC_IsProcessed_Aktarim] = 0
PRINT ''DELETE EDILDI''
END
/* INSERT ISLEMI */
ELSE IF @operation = 2
BEGIN
SET
IDENTITY_INSERT [Messaging].[dbo].[MessageTransfer] ON;
INSERT INTO [Messaging].[dbo].[MessageTransfer] (
[Id], [MessageId], [CandidateId],
[ResumeId], [JobCode], [SendDate],
[ReadDate], [CompanyUserId], [CompanyId],
[IsDeleted], [IsCompanyDeleted],
[ReplyRequired], [AutoReply], [CreationDate],
[LastModifyDate]
)
SELECT
[MesajHareketID],
[MesajID],
[ALoginID],
[RefNumber],
[Clientjobrefno],
AtlantisDB.dbo.[ConvertDatetime_MertV3]([GonderimTarihi]) as GonderimTarihi,
AtlantisDB.dbo.[ConvertDatetime_MertV3]([OkunmaTarihi]) as OkunmaTarihi,
[CLoginId],
[Postinguser],
ISNULL([Silindi], 0),
ISNULL([FirmaSilindi], 0),
ISNULL([Cevaplasin], 0),
ISNULL([OtomatikCevap], 0),
[CreationDate],
ISNULL(
[LastModifyDate],
GETDATE()
)
FROM
[AtlantisDB].[cdc].[dbo_MesajHareketDB_CT]
WHERE
MesajHareketID = @MesajHareketID
AND __$operation = 2
AND [CDC_IsProcessed_Aktarim] = 0
SET
IDENTITY_INSERT [Messaging].[dbo].[MessageTransfer] OFF;
UPDATE
[AtlantisDB].[cdc].[dbo_MesajHareketDB_CT]
SET
CDC_IsProcessed_Aktarim = 1,
CDC_ProcessesDate = GETDATE()
WHERE
MesajHareketID = @MesajHareketID
AND __$operation = 2
AND [CDC_IsProcessed_Aktarim] = 0
PRINT ''INSERT EDILDI''
END

/* UPDATE ISLEMI */
ELSE IF @operation = 4
BEGIN
UPDATE
U
SET
U.MessageId = S.MesajID,
U.CandidateId = S.ALoginID,
U.ResumeId = S.RefNumber,
U.JobCode = S.Clientjobrefno,
U.SendDate = AtlantisDB.dbo.ConvertDatetime_MertV3(S.GonderimTarihi),
U.ReadDate = AtlantisDB.dbo.ConvertDatetime_MertV3(S.OkunmaTarihi),
U.CompanyUserId = S.CLoginId,
U.CompanyId = S.Postinguser,
U.IsDeleted = ISNULL(S.Silindi, 0),
U.IsCompanyDeleted = ISNULL(S.FirmaSilindi, 0),
U.ReplyRequired = ISNULL(S.Cevaplasin, 0),
U.AutoReply = ISNULL(S.OtomatikCevap, 0),
U.CreationDate = S.CreationDate,
U.LastModifyDate = S.LastModifyDate
FROM
[Messaging].[dbo].[MessageTransfer] U
INNER JOIN [AtlantisDB].[cdc].[dbo_MesajHareketDB_CT] S ON U.[Id] = S.MesajHareketID
WHERE
S.MesajHareketID = @MesajHareketID
AND S.__$operation = 4
AND S.[__$seqval] = (
SELECT
MAX([__$seqval])
FROM
cdc.[dbo_MesajHareketDB_CT] WITH(NOLOCK)
WHERE
MesajHareketID = @MesajHareketID
AND __$operation = 4
)

UPDATE
[AtlantisDB].[cdc].[dbo_MesajHareketDB_CT]
SET
CDC_IsProcessed_Aktarim = 1,
CDC_ProcessesDate = GETDATE()
WHERE
MesajHareketID = @MesajHareketID
AND __$operation = 4
AND [CDC_IsProcessed_Aktarim] = 0
PRINT ''UPDATE EDILDI''
END FETCH NEXT FROM Numara_cursor INTO @MesajHareketID, @operation
END
CLOSE Numara_cursor;
DEALLOCATE Numara_cursor;
',
@database_name=N'AtlantisDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [2_Insert] Script Date: 5/29/2024 4:32:14 PM ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'2_Insert',
@step_id=2,
@cmdexec_success_code=0,
@on_success_action=1,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'DECLARE @maxid BIGINT
SELECT @maxid = ISNULL(MAX(Id), 0) FROM [Messaging].[dbo].[MessageTransfer] WITH(NOLOCK)
SET IDENTITY_INSERT [Messaging].[dbo].[MessageTransfer] ON;
INSERT INTO [Messaging].[dbo].[MessageTransfer] ([Id], [MessageId], [CandidateId], [ResumeId], [JobCode], [SendDate], [ReadDate], [CompanyUserId], [CompanyId], [IsDeleted], [IsCompanyDeleted], [ReplyRequired], [AutoReply], [CreationDate], [LastModifyDate])
SELECT TOP 10000
[MesajHareketID]
,[MesajID]
,[ALoginID]
,[RefNumber]
,[Clientjobrefno]
,AtlantisDB.dbo.[ConvertDatetime_MertV3]([GonderimTarihi]) as GonderimTarihi
,AtlantisDB.dbo.[ConvertDatetime_MertV3]([OkunmaTarihi]) as OkunmaTarihi
,[CLoginId]
,[Postinguser]
,ISNULL([Silindi],0)
,ISNULL([FirmaSilindi],0)
,ISNULL([Cevaplasin],0)
,ISNULL([OtomatikCevap],0)
,ISNULL([CreationDate],GETDATE())
,ISNULL([LastModifyDate],GETDATE())
FROM [AtlantisDB].[dbo].[MesajHareketDB] WITH(NOLOCK)
WHERE [MesajID] IN (SELECT Id FROM Messaging.dbo.[Message])
AND [MesajHareketID] > @maxid
ORDER BY [MesajHareketID]
SET IDENTITY_INSERT [Messaging].[dbo].[MessageTransfer] OFF;
',
@database_name=N'AtlantisDB',
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'5',
@enabled=1,
@freq_type=4,
@freq_interval=1,
@freq_subday_type=4,
@freq_subday_interval=5,
@freq_relative_interval=0,
@freq_recurrence_factor=0,
@active_start_date=20240429,
@active_end_date=99991231,
@active_start_time=0,
@active_end_time=235959,
@schedule_uid=N'1f5470de-1d56-40e7-9014-f0de2b75b873'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO


RecordBycunay
Record Date08-02-2025 18:32:04
Düzenle
Kopyala
Sil