iVend Database Database : ReplicationMonitor Stored Procedure

Creation Date

4/13/2015 12:00 PM

Encrypted

Ansi Nulls

Parameter

Direction

Description

Data Type

Size

@pDestinationSiteID

In

The SiteID which is to receive data.

Integer

4

@pOutRecordCount

Out

Integer

4

@RETURN_VALUE

Return Value

Integer

4

--The status can be Not Processed or In Process if the record has been prepared.

--##SUMMARY_END

CREATE PROCEDURE [dbo].[ReplicationMonitor]

@pDestinationSiteID INT,

@pOutRecordCount INT OUT

AS

BEGIN

SET NOCOUNT ON

CREATE TABLE #PendingRecords

(

 SeqenceNo BIGINT,

 ReplicationKey nvarchar(50),

 SourceKey nvarchar(50),

 ProcessDate DateTime,

[Status] nvarchar(200),

 Error nvarchar(2000),

 SourceType int,

 OperationType int

)

insert into #PendingRecords

Select A.SeqenceNo, A.ReplicationTransactionKey [ReplicationKey], A.SourceKey,

  A.ProcessDate,

'Waiting for Acknowledgement'  Status,

'' Error, A.SourceType, A.OperationType

From RepReplicationTransaction A with(nolock) INNER Join RepReplicationTransactionDetail B with(nolock)

on A.ReplicationTransactionKey = B.ReplicationTransactionKey

And B.DestinationSiteId = @pDestinationSiteID

And ISNULL(A.StoreId, 1) != B.DestinationSiteId

Where B.Status = 0 And B.SentTime != null

insert into #PendingRecords

Select A.SeqenceNo, A.ReplicationTransactionKey [ReplicationKey], A.SourceKey,

  A.ProcessDate,

'Pending'  Status,

'' Error, A.SourceType, A.OperationType

From RepReplicationTransaction A with(nolock) INNER Join RepReplicationTransactionDetail B with(nolock)

on A.ReplicationTransactionKey = B.ReplicationTransactionKey

And B.DestinationSiteId = @pDestinationSiteID

And ISNULL(A.StoreId, 1) != B.DestinationSiteId

Where B.Status = 0 And B.SentTime = null

insert into #PendingRecords

Select distinct A.SeqenceNo, A.ReplicationTransactionKey [ReplicationKey], A.SourceKey,

  A.ProcessDate,

'Pending for process'  Status,

'' Error, A.SourceType, A.OperationType

From RepReplicationTransaction A

Where SendDumpSequenceNumber is null

And ISNULL(A.StoreId, 1) != @pDestinationSiteID

Select @pOutRecordCount = Count(1) From #PendingRecords

Select top 300 *

from #PendingRecords

Order By ProcessDate

END