How to monitor replication count using T-SQL code

  • I Want to monitor Replication count of object (Table )if it is not equal to Publication (Table ) and subscriber (Table ), It have to send mail with count difference.

    Please let me know if any one you have this code.

    Thank you in Advance.

  • Perhaps something like this would be useful for you. Seems like it's doing nearly exactly what you want.

    /****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]

    AS

    /* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"

    - meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs

    It will produce a list of the offending

    DBs that will require attention and also pass the query results of the non-synced tables in the email

    */

    --Create a threshold for how much of a difference we can tolerate between indentity gaps:

    --We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:

    Declare @Threshold INT

    Set@Threshold = 10

    --Drop our temp processing table if it exists

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

    --Build the table:

    Create Table #TempProcessing (

    DatabaseName varchar(255)

    ,[Table] varchar(255)

    ,[ProductionValue] INT

    ,[ReplicatedValue] INT

    )

    ---------------------------------------------------------------------------------------------------------------------------

    -------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------

    SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a userid discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User''as [Table]

    ,uup.UserID as ProductionValue

    ,uuR.UserID as ReplicatedValue

    from [Prod].['+sys.Name+'].[User]. uuP

    --Bring in the top userID from replicated data

    cross apply (select top 1 UserID from ['+sys.Name+'].[User]. order by UserID desc) uuR

    order by uup.UserID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.EventID as ProductionValue

    ,uuR.EventID as ReplicatedValue

    from [Prod].['+sys.Name+'].[Event].[Event] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 EventID from ['+sys.Name+'].[Event].[Event] order by EventID desc) uuR

    order by uup.EventID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.InteractionID as ProductionValue

    ,uuR.InteractionID as ReplicatedValue

    from [Prod].['+sys.Name+'].[Interaction].[Interaction] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 InteractionID from ['+sys.Name+'].[Interaction].[Interaction] order by InteractionID desc) uuR

    order by uup.InteractionID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ExclusionID as ProductionValue

    ,uuR.ExclusionID as ReplicatedValue

    from [Prod].['+sys.Name+'].[User].[Exclusion] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ExclusionID from ['+sys.Name+'].[User].[Exclusion] order by ExclusionID desc) uuR

    order by uup.ExclusionID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb')

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

    -------------------------------------------------------------------------------------------------------

    ------------Populate an exceptions table---------------------------------------------------------------

    select *

    into ##TempExceptions_uspReplicationSyncMonitor

    from (

    select *

    ,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]

    from #TempProcessing

    )sub

    where [Difference] = 1

    -----------------------------------------------------------------------------------------------

    -------------Fire off an email warning with the exception DatabaseNames:-----------------------------

    --Only fire the email if we actually have any exceptions:

    IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1

    Begin

    --Variable to hold DatabaseName list string

    Declare @ExceptionDatabaseName varchar(max)

    --Set to a concatenated list of DatabaseName may be out of sync:

    Set @ExceptionDatabaseName =

    ('The following potential replication issues have been discovered for DatabaseName(s):

    ' +

    STUFF((SELECT Distinct ',' + DatabaseName

    from ##TempExceptions_uspReplicationSyncMonitor

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)')

    ,1,1,'')

    +

    '.

    Please investigate ASAP.'

    )

    --Let us build our query of issues an html table to insert into the email:

    --Build the body of the email

    Declare @BodyQueryvarchar(MAX)

    Set @BodyQuery = cast( (

    select td =

    cast(DatabaseNameas varchar(300)) + '</td><td>' +

    cast([Table]as varchar(300)) + '</td><td>' +

    cast(ProductionValueas varchar(25)) + '</td><td>' +

    cast(ReplicatedValue as varchar(25))

    from

    (

    select

    DatabaseName

    ,[Table]

    ,ProductionValue

    ,ReplicatedValue

    from ##TempExceptions_uspReplicationSyncMonitor

    ) sub

    for xml path( 'tr' ), type ) as varchar(max) )

    set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>

    <table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'

    + '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'

    + replace( replace( @BodyQuery, '<', '<' ), '>', '>' )

    + '</table>'

    Set @ExceptionDatabaseName = @ExceptionDatabaseName + '

    ' +@BodyQuery

    --Print @ExceptionDatabaseName

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='YourProfileNameHere',

    --Change Email addresses here:

    @recipients='Test123@Test.com ;Test456@Test.com',

    @subject='Urgent!: Replication may be out of sync',

    @body= @ExceptionDatabaseName,

    @body_format = 'HTML'

    END

    Else

    Begin

    Print 'No Issues.'

    END

    --Drop our temp processing table when we are done:

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

  • Hi.. Thank you for your reply and your code.

    I ran the code in below steps but i didn't get any result.

    1. I change email address.

    2. I ran the code from Publisher server - But no result found.

    3. I stooped my subscriber server then i ran the code from Publisher side - But no result found.

    4. I ran the code from Subscriber server - But no result found.

    5. I stooped the Publisher server then i ran the code from Subscriber server - But no result found.

    Please help me on this.

  • What was the result when you ran the code?

    A couple things to consider...

    Did you change the name of the linked server mentioned in the code to the linked server you have?

    ..This ran in the subscriber DB, hence my linked server to the publishing DB was named 'PROD'

    Did you adjust the code in the dynamic SQL portion of the procedure to point to your specific tables?

    Do you have a mail profile set up and configured for db.sendmail to work?

  • Hi Thanks for reply.

    My result is - Command(s) completed successfully.

    Yes i have changed link server name , My link server name is [FOR_PROD]

    - I ran this code in subscriber DB

    -- the dynamic SQL portion of the procedure to point to your specific tables ----> I have not change this part because i am not sure what to change. Please help me on this part

    -- mail profile - IS already setup and is working.

  • ---->>>>>> Please find my code which i am using <<<<<<<< -----------------

    /****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF OBJECT_ID('uspReplicationSyncMonitor') IS NOT NULLDROP PROCEDURE uspReplicationSyncMonitor;

    go

    CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]

    AS

    /* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"

    - meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs

    It will produce a list of the offending

    DBs that will require attention and also pass the query results of the non-synced tables in the email

    */

    --Create a threshold for how much of a difference we can tolerate between indentity gaps:

    --We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:

    Declare @Threshold INT

    Set@Threshold = 10

    --Drop our temp processing table if it exists

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

    --Build the table:

    Create Table #TempProcessing (

    DatabaseName varchar(255)

    ,[Table] varchar(255)

    ,[ProductionValue] INT

    ,[ReplicatedValue] INT

    )

    ---------------------------------------------------------------------------------------------------------------------------

    -------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------

    SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a userid discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User''as [Table]

    ,uup.UserID as ProductionValue

    ,uuR.UserID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[User]. uuP

    --Bring in the top userID from replicated data

    cross apply (select top 1 UserID from ['+sys.Name+'].[User]. order by UserID desc) uuR

    order by uup.UserID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.EventID as ProductionValue

    ,uuR.EventID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[Event].[Event] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 EventID from ['+sys.Name+'].[Event].[Event] order by EventID desc) uuR

    order by uup.EventID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.InteractionID as ProductionValue

    ,uuR.InteractionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[Interaction].[Interaction] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 InteractionID from ['+sys.Name+'].[Interaction].[Interaction] order by InteractionID desc) uuR

    order by uup.InteractionID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ExclusionID as ProductionValue

    ,uuR.ExclusionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[User].[Exclusion] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ExclusionID from ['+sys.Name+'].[User].[Exclusion] order by ExclusionID desc) uuR

    order by uup.ExclusionID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb')

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

    -------------------------------------------------------------------------------------------------------

    ------------Populate an exceptions table---------------------------------------------------------------

    select *

    into ##TempExceptions_uspReplicationSyncMonitor

    from (

    select *

    ,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]

    from #TempProcessing

    )sub

    where [Difference] = 1

    -----------------------------------------------------------------------------------------------

    -------------Fire off an email warning with the exception DatabaseNames:-----------------------------

    --Only fire the email if we actually have any exceptions:

    IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1

    Begin

    --Variable to hold DatabaseName list string

    Declare @ExceptionDatabaseName varchar(max)

    --Set to a concatenated list of DatabaseName may be out of sync:

    Set @ExceptionDatabaseName =

    ('The following potential replication issues have been discovered for DatabaseName(s):

    ' +

    STUFF((SELECT Distinct ',' + DatabaseName

    from ##TempExceptions_uspReplicationSyncMonitor

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)')

    ,1,1,'')

    +

    '.

    Please investigate ASAP.'

    )

    --Let us build our query of issues an html table to insert into the email:

    --Build the body of the email

    Declare @BodyQueryvarchar(MAX)

    Set @BodyQuery = cast( (

    select td =

    cast(DatabaseNameas varchar(300)) + '</td><td>' +

    cast([Table]as varchar(300)) + '</td><td>' +

    cast(ProductionValueas varchar(25)) + '</td><td>' +

    cast(ReplicatedValue as varchar(25))

    from

    (

    select

    DatabaseName

    ,[Table]

    ,ProductionValue

    ,ReplicatedValue

    from ##TempExceptions_uspReplicationSyncMonitor

    ) sub

    for xml path( 'tr' ), type ) as varchar(max) )

    set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>

    <table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'

    + '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'

    + replace( replace( @BodyQuery, '<', '<' ), '>', '>' )

    + '</table>'

    Set @ExceptionDatabaseName = @ExceptionDatabaseName + '

    ' +@BodyQuery

    --Print @ExceptionDatabaseName

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='YourProfileNameHere',

    --Change Email addresses here:

    @recipients='mssqlserverdba87@gmail.com;mssqlserverdba87@gmail.com',

    @subject='Urgent!: Replication may be out of sync',

    @body= @ExceptionDatabaseName,

    @body_format = 'HTML'

    END

    Else

    Begin

    Print 'No Issues.'

    END

    --Drop our temp processing table when we are done:

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULLDROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULLDROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

  • This is the code you're using:

    /****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF OBJECT_ID('uspReplicationSyncMonitor') IS NOT NULL DROP PROCEDURE uspReplicationSyncMonitor;

    go

    CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]

    AS

    /* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"

    - meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs

    It will produce a list of the offending

    DBs that will require attention and also pass the query results of the non-synced tables in the email

    */

    --Create a threshold for how much of a difference we can tolerate between indentity gaps:

    --We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:

    Declare @Threshold INT

    Set @Threshold = 10

    --Drop our temp processing table if it exists

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

    --Build the table:

    Create Table #TempProcessing (

    DatabaseName varchar(255)

    ,[Table] varchar(255)

    ,[ProductionValue] INT

    ,[ReplicatedValue] INT

    )

    ---------------------------------------------------------------------------------------------------------------------------

    -------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------

    SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a userid discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User'' as [Table]

    ,uup.UserID as ProductionValue

    ,uuR.UserID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[User]. uuP

    --Bring in the top userID from replicated data

    cross apply (select top 1 UserID from ['+sys.Name+'].[User]. order by UserID desc) uuR

    order by uup.UserID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.EventID as ProductionValue

    ,uuR.EventID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[Event].[Event] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 EventID from ['+sys.Name+'].[Event].[Event] order by EventID desc) uuR

    order by uup.EventID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.InteractionID as ProductionValue

    ,uuR.InteractionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[Interaction].[Interaction] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 InteractionID from ['+sys.Name+'].[Interaction].[Interaction] order by InteractionID desc) uuR

    order by uup.InteractionID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ExclusionID as ProductionValue

    ,uuR.ExclusionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[User].[Exclusion] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ExclusionID from ['+sys.Name+'].[User].[Exclusion] order by ExclusionID desc) uuR

    order by uup.ExclusionID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb')

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

    -------------------------------------------------------------------------------------------------------

    ------------Populate an exceptions table---------------------------------------------------------------

    select *

    into ##TempExceptions_uspReplicationSyncMonitor

    from (

    select *

    ,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]

    from #TempProcessing

    )sub

    where [Difference] = 1

    -----------------------------------------------------------------------------------------------

    -------------Fire off an email warning with the exception DatabaseNames:-----------------------------

    --Only fire the email if we actually have any exceptions:

    IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1

    Begin

    --Variable to hold DatabaseName list string

    Declare @ExceptionDatabaseName varchar(max)

    --Set to a concatenated list of DatabaseName may be out of sync:

    Set @ExceptionDatabaseName =

    ( 'The following potential replication issues have been discovered for DatabaseName(s):

    ' +

    STUFF((SELECT Distinct ',' + DatabaseName

    from ##TempExceptions_uspReplicationSyncMonitor

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)')

    ,1,1,'')

    +

    '.

    Please investigate ASAP.'

    )

    --Let us build our query of issues an html table to insert into the email:

    --Build the body of the email

    Declare @BodyQuery varchar(MAX)

    Set @BodyQuery = cast( (

    select td =

    cast(DatabaseName as varchar(300)) + '</td><td>' +

    cast([Table] as varchar(300)) + '</td><td>' +

    cast(ProductionValue as varchar(25)) + '</td><td>' +

    cast(ReplicatedValue as varchar(25))

    from

    (

    select

    DatabaseName

    ,[Table]

    ,ProductionValue

    ,ReplicatedValue

    from ##TempExceptions_uspReplicationSyncMonitor

    ) sub

    for xml path( 'tr' ), type ) as varchar(max) )

    set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>

    <table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'

    + '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'

    + replace( replace( @BodyQuery, '<', '<' ), '>', '>' )

    + '</table>'

    Set @ExceptionDatabaseName = @ExceptionDatabaseName + '

    ' +@BodyQuery

    --Print @ExceptionDatabaseName

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='YourProfileNameHere',

    --Change Email addresses here:

    @recipients='mssqlserverdba87@gmail.com;mssqlserverdba87@gmail.com',

    @subject='Urgent!: Replication may be out of sync',

    @body= @ExceptionDatabaseName,

    @body_format = 'HTML'

    END

    Else

    Begin

    Print 'No Issues.'

    END

    --Drop our temp processing table when we are done:

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

    In the dynamic SQL portion of the code (the code that's in red) you must change the structure of the select statements to point to the tables you want to monitor. I'm sure you don't have a table called User.User, Event.Event, Interaction.Interaction, and User.Exclusion...those were just sample table names I've provided for you. This particular select statement is dynamic and wrapped in a cursor because it loops through multiple databases (that all have an identical schema) running those select statements and inserting the results into a Temp Table each time.

    You also need to use your profile name for the dbo.sp_send_dbmail stored procedure to work.

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='YourProfileNameHere', <<<<-----Change this to your Database Mail profile name***

    --Change Email addresses here:

    @recipients='mssqlserverdba87@gmail.com;mssqlserverdba87@gmail.com',

    @subject='Urgent!: Replication may be out of sync',

    @body= @ExceptionDatabaseName,

    @body_format = 'HTML'

  • I have changed my code dynamic SQL portion.

    Database Mail profile = DB_MAIL

    I have 4 table = [dbo].[t1],

    [dbo].[t2],

    [dbo].[t3],

    [dbo].[t4]

    I have changed the code and result is - Command(s) completed successfully.

    Please see the code below.

  • /****** Object: StoredProcedure [dbo].[uspReplicationSyncMonitor] Script Date: 06/04/2015 11:37:40 ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    IF OBJECT_ID('uspReplicationSyncMonitor') IS NOT NULL DROP PROCEDURE uspReplicationSyncMonitor;

    go

    CREATE PROCEDURE [dbo].[uspReplicationSyncMonitor]

    AS

    /* This proc will fire an alert to whoever is specified in the SendMail sproc below if replication is "out of date"

    - meaning that there is a discrepency between transactional data and replicated data in one of the DatabaseName DBs

    It will produce a list of the offending

    DBs that will require attention and also pass the query results of the non-synced tables in the email

    */

    --Create a threshold for how much of a difference we can tolerate between indentity gaps:

    --We expect replication to have a lag of several seconds so we don't want to keep firing off false alerts:

    Declare @Threshold INT

    Set @Threshold = 10

    --Drop our temp processing table if it exists

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

    --Build the table:

    Create Table #TempProcessing (

    DatabaseName varchar(255)

    ,[Table] varchar(255)

    ,[ProductionValue] INT

    ,[ReplicatedValue] INT

    )

    ---------------------------------------------------------------------------------------------------------------------------

    -------------------------Start the cursor that will populate the table with results from each DatabaseName table:----------------

    SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a userid discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User'' as [Table]

    ,uup.UserID as ProductionValue

    ,uuR.UserID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP

    --Bring in the top userID from replicated data

    cross apply (select top 1 UserID from ['+sys.Name+'].[dbo].[t1] order by UserID desc) uuR

    order by uup.UserID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.EventID as ProductionValue

    ,uuR.EventID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 EventID from ['+sys.Name+'].[dbo].[t2] order by EventID desc) uuR

    order by uup.EventID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.InteractionID as ProductionValue

    ,uuR.InteractionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 InteractionID from ['+sys.Name+'].[dbo].[t3] order by InteractionID desc) uuR

    order by uup.InteractionID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ExclusionID as ProductionValue

    ,uuR.ExclusionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ExclusionID from ['+sys.Name+'].[dbo].[t4] order by ExclusionID desc) uuR

    order by uup.ExclusionID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb')

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

    -------------------------------------------------------------------------------------------------------

    ------------Populate an exceptions table---------------------------------------------------------------

    select *

    into ##TempExceptions_uspReplicationSyncMonitor

    from (

    select *

    ,case when (ProductionValue - ReplicatedValue) >= @Threshold Then 1 else 0 End as [Difference]

    from #TempProcessing

    )sub

    where [Difference] = 1

    -----------------------------------------------------------------------------------------------

    -------------Fire off an email warning with the exception DatabaseNames:-----------------------------

    --Only fire the email if we actually have any exceptions:

    IF (Select count(*) from ##TempExceptions_uspReplicationSyncMonitor) >= 1

    Begin

    --Variable to hold DatabaseName list string

    Declare @ExceptionDatabaseName varchar(max)

    --Set to a concatenated list of DatabaseName may be out of sync:

    Set @ExceptionDatabaseName =

    ( 'The following potential replication issues have been discovered for DatabaseName(s):

    ' +

    STUFF((SELECT Distinct ',' + DatabaseName

    from ##TempExceptions_uspReplicationSyncMonitor

    FOR XML PATH(''), TYPE

    ).value('.', 'NVARCHAR(MAX)')

    ,1,1,'')

    +

    '.

    Please investigate ASAP.'

    )

    --Let us build our query of issues an html table to insert into the email:

    --Build the body of the email

    Declare @BodyQuery varchar(MAX)

    Set @BodyQuery = cast( (

    select td =

    cast(DatabaseName as varchar(300)) + '</td><td>' +

    cast([Table] as varchar(300)) + '</td><td>' +

    cast(ProductionValue as varchar(25)) + '</td><td>' +

    cast(ReplicatedValue as varchar(25))

    from

    (

    select

    DatabaseName

    ,[Table]

    ,ProductionValue

    ,ReplicatedValue

    from ##TempExceptions_uspReplicationSyncMonitor

    ) sub

    for xml path( 'tr' ), type ) as varchar(max) )

    set @BodyQuery = '<font face="verdana" color: rgb(79, 93, 86)>The table below shows replicated Primary Keys that are out of sync, indicating Replication may require attention:</font>

    <table style=" border-collapse:collapse;margin-top: 2px; margin-bottom: 2px; margin-left: 2px; margin-right: 2px; font-family: Arial; font-size: 11px; color: rgb(79, 93, 86)" cellpadding="5" cellspacing="2" border="1">'

    + '<tr><th>DatabaseName</th><th>Table</th><th With="75">ProductionValue</th><th>ReplicatedValue</th></tr>'

    + replace( replace( @BodyQuery, '<', '<' ), '>', '>' )

    + '</table>'

    Set @ExceptionDatabaseName = @ExceptionDatabaseName + '

    ' +@BodyQuery

    --Print @ExceptionDatabaseName

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name='DB_MAIL',

    --Change Email addresses here:

    @recipients='mssqlserverdba87@gmail.com;rajeshjaiswalraj@gmail.com',

    @subject='Urgent!: Replication may be out of sync',

    @body= @ExceptionDatabaseName,

    @body_format = 'HTML'

    END

    Else

    Begin

    Print 'No Issues.'

    END

    --Drop our temp processing table when we are done:

    IF OBJECT_ID('tempdb..#TempProcessing') IS NOT NULL DROP TABLE #TempProcessing;

    IF OBJECT_ID('tempdb..##TempExceptions_uspReplicationSyncMonitor') IS NOT NULL DROP TABLE ##TempExceptions_uspReplicationSyncMonitor;

  • Again...you need to adjust to code for YOUR database.

    The code below in the dynamic SQL portion needs to be altered to match YOUR schema. Transactional replication is primary key based so this part below is just comparing the latest primary key for each respective table in each database. The select statements need to be adjusted to select your primary keys from each table (t1, t2, t3, t4). I'm sure your tables don't have 'UserID', 'InteractionID', etc... as primary keys so you need to change this to match your tables.

    Once you've updated the code below to match your table structures...execute this select * from #TempProcessing (with your changes)

    and run this and let me know what you see...this is the Temp Table that the code below should populate with your table primary key values across multiple databases.

    SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a userid discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User'' as [Table]

    ,uup.UserID as ProductionValue

    ,uuR.UserID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP

    --Bring in the top userID from replicated data

    cross apply (select top 1 UserID from ['+sys.Name+'].[dbo].[t1] order by UserID desc) uuR

    order by uup.UserID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.EventID as ProductionValue

    ,uuR.EventID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 EventID from ['+sys.Name+'].[dbo].[t2] order by EventID desc) uuR

    order by uup.EventID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.InteractionID as ProductionValue

    ,uuR.InteractionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 InteractionID from ['+sys.Name+'].[dbo].[t3] order by InteractionID desc) uuR

    order by uup.InteractionID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ExclusionID as ProductionValue

    ,uuR.ExclusionID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ExclusionID from ['+sys.Name+'].[dbo].[t4] order by ExclusionID desc) uuR

    order by uup.ExclusionID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb')

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

  • Hi... I changed my code

    i have four table t1,t2,t3 and t4 and in all table Primary key is ID.

    When i changed the code and ran it again its giving below error.

    Msg 7314, Level 16, State 1, Line 3

    The OLE DB provider "SQLNCLI10" for linked server "FOR_PROD" does not contain the table ""ReportServer$PROD_DR"."dbo"."t1"". The table either does not exist or the current user does not have permissions on that table.[/color]

    Please see my code below ... and help me.

  • Hi...

    In my case i have four table t1,t2,t3, and t4 for all primary key is (ID)

    I have changed the code again as you have given.

    But code executed with error.

    Msg 7314, Level 16, State 1, Line 3

    The OLE DB provider "SQLNCLI10" for linked server "FOR_PROD" does not contain the table ""ReportServer$PROD_DR"."dbo"."t1"". The table either does not exist or the current user does not have permissions on that table.

    Please find my code below and help me again.

  • SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a id discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP

    --Bring in the top ID from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t1] order by ID desc) uuR

    order by uup.ID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t2] order by ID desc) uuR

    order by uup.ID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t3] order by ID desc) uuR

    order by uup.ID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t4] order by ID desc) uuR

    order by uup.ID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb')

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

  • The problem is most likely that you're trying to run this script in DBs that don't have those tables. Again...like I said, this script is dynamic so it runs in all DBs except for the ones specific in the where clause...you need to adjust it to what you want. See my new comment in in the code below.

    SET NOCOUNT ON

    DECLARE @procName VARCHAR(MAX)

    DECLARE @getprocName CURSOR

    SET @getprocName = CURSOR FOR

    --Build our dynamic insert statements to populate the table: we want to get the top indentity value for certain tables in

    --prod and in the replicated DB so we can compare them

    --PROD is the NAME of the linked server that links to Production data

    --This will compare 4 sampe table named User.User, Event.Event, Interaction.Interaction, User.Exclusion

    --You can add more tables to the code or you can use system tables/view to fetch a whole list of tables for each DB

    SELECT

    '

    --Select on the user table in prod and replicated data and see if there is a id discrepencay

    ;With UserBucket as

    (

    select top 1

    ''User'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t1] uuP

    --Bring in the top ID from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t1] order by ID desc) uuR

    order by uup.ID desc

    )

    --Select on the event table in prod and replicated data and see if there is a eventID discrepencay

    ,EventBucket as

    (

    select top 1

    ''Event'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t2] uuP

    --Bring in the top eventid from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t2] order by ID desc) uuR

    order by uup.ID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,InteractionBucket as

    (

    select top 1

    ''Interaction'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t3] uuP

    --Bring in the top interactionid from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t3] order by ID desc) uuR

    order by uup.ID desc

    )

    --Select on the interaction table in prod and replicated data and see if there is a interactionid discrepencay

    ,ExclusionBucket as

    (

    select top 1

    ''Exclusion'' as [Table]

    ,uup.ID as ProductionValue

    ,uuR.ID as ReplicatedValue

    from [FOR_PROD].['+sys.Name+'].[dbo].[t4] uuP

    --Bring in the top exclusionid from replicated data

    cross apply (select top 1 ID from ['+sys.Name+'].[dbo].[t4] order by ID desc) uuR

    order by uup.ID desc

    )

    --Combine all the previous buckets into our last select:

    ,CombineSelect as

    (

    select * from UserBucket

    union

    select * from EventBucket

    union

    select * from InteractionBucket

    union

    select * from ExclusionBucket

    )

    Insert into #TempProcessing (

    DatabaseName

    ,[Table]

    ,[ProductionValue]

    ,[ReplicatedValue]

    ) ' +

    'Select ' +''''+sys.Name+'''' + ' ,[Table] ,[ProductionValue] , [ReplicatedValue]

    from CombineSelect'

    from sys.databases sys

    --Exclude the DBs you do not care about here:

    where name not in ('master','tempdb','model','msdb') --Change this to 'where name in' and list the DBs you want

    OPEN @getprocName

    FETCH NEXT

    FROM @getprocName INTO @procName

    WHILE @@FETCH_STATUS = 0

    BEGIN

    EXEC (@procName)

    --Print @procName

    FETCH NEXT

    FROM @getprocName INTO @procName

    END

    CLOSE @getprocName

    DEALLOCATE @getprocName;

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply