Getting Error while executing the stored procedure

balu_pendyala · May 17, 2022, 3:48pm

Hi All

I am getting below error while execute the script to monitor the SQL Server replication on SQL - 2019. Please provide with the updated code . i am not familiar with T-SQL Code. The second SP is pointing to first SP.

Error:
Msg 8164, Level 16, State 1, Procedure distribution.dbo.usp_GetReplicationMonitorData, Line 403 [Batch Start Line 2]
An INSERT EXEC statement cannot be nested.
(1 row affected)
Completion time: 2022-05-04T03:44:51.8048548-05:00

SP Details:

CREATE PROCEDURE [dbo].[usp_GetReplicationStatus]@mode BIT = 0
AS
BEGIN

SET NOCOUNT ON
DECLARE @Recipients VARCHAR(275)
DECLARE @MailSubject VARCHAR(275)
DECLARE @Xml NVARCHAR(MAX)
DECLARE @Mailtext NVARCHAR(MAX)
DECLARE @Server VARCHAR(25)
DECLARE @Curdate DATETIME
DECLARE @MailString NVARCHAR(MAX)
DECLARE @Note NVARCHAR(1000)
DECLARE @Sql VARCHAR (200)
DECLARE @Distributor SYSNAME

SET @Recipients = 'Atul.Gaikwad@SQLDBAExperts.com'

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

CREATE TABLE #REPLICATIONMONITOR
(
SN INT IDENTITY(1,1)
,PUBLISHER SYSNAME NULL
,PUBLISHER_DB SYSNAME NULL
,PUBLICATION SYSNAME NULL
,DISTRIBUTION_DB SYSNAME NULL
,SUBSCRIBER SYSNAME NULL
,SUBSCRIBER_DB SYSNAME NULL
,LATENCY INT NULL
,LAST_DISTSYNC DATETIME NULL
,PERFORMANCE VARCHAR(20) NULL
,WARNING VARCHAR(20) NULL
,LOGREADERAGENTSTATUS VARCHAR(50) NULL
,DISTRIBUTIONAGENTSTATUS VARCHAR(50) NULL
)

DECLARE DistServer CURSOR FOR
SELECT DISTRIBUTOR FROM Distributor_Servers;

OPEN DistServer

FETCH NEXT FROM DistServer
INTO @Distributor

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Sql = @Distributor + '.DBA.dbo.usp_GetReplicationMonitorData'
INSERT INTO #REPLICATIONMONITOR
EXEC (@Sql)

FETCH NEXT FROM DistServer
INTO @Distributor
END

CLOSE DistServer;
DEALLOCATE DistServer;

SET @Mailtext ='

table.gridtable { font-family: verdana,arial,sans-serif; font-size:11px; color:#000000; border-width: 1px; border-color: #666666; border-collapse: collapse; } table.gridtable th { border-width: 1px; font-size:10px; border-style: solid; border-color: #666666; } table.gridtable td { border-width: 1px; font-size:10px; border-style: solid; border-color: #666666; } '

SET @Note = 'Note:

LogReader Agent and Distributor Agent status should always be either Idle or In Progress.
If agent status has different value then verify it manually and rectify the issue.
Performance values are based on latency ratio (Latency/LatencyThreshold):
Excellent (0-34%), Good (35-59%), Fair (60-84%), Poor (85-99%), Critical (100%+).
We have used 120 sec as latency threshold.
Warning values: - i) Expiration - subscription not synchronized within retention period
threshold
ii) Latency - Publisher to Subscriber latency has exceeded threshold iii) NA - No warnings

'

IF @mode = 0
BEGIN

SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE PERFORMANCE WHEN 'Excellent'
THEN '#90EE90' WHEN 'Good' THEN '#90EE90' WHEN 'Fair' THEN '#FFA500'
ELSE '#FF4500' END,
td = PUBLISHER, '', td = PUBLISHER_DB , '', td = PUBLICATION, '',
td = DISTRIBUTION_DB, '', td = SUBSCRIBER, '',
td = SUBSCRIBER_DB, '', td = LATENCY, '', td = PERFORMANCE, '',
td = WARNING, '', td = LOGREADERAGENTSTATUS, '',
td = DISTRIBUTIONAGENTSTATUS, '', td = LAST_DISTSYNC, ''
FROM #REPLICATIONMONITOR ORDER BY PUBLISHER, LATENCY DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @Mailtext = @Mailtext + @Xml +'

Publisher	Publisher Database	Publication	Distribution Database	Subscriber	Subscriber Database	Latency	Performance	Warning	LogReader Agent Status	Distributor Agent Status	Last Distribution Sync

' + @Note

SET @MailSubject = 'Replication Status Snapshot'
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name =
''SQLDBAExpertsmail'', @recipients = ''' + @Recipients + ''',
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''',
@body_format = ''HTML'' '
EXEC (@MailString)

END
ELSE
BEGIN

IF EXISTS(SELECT 1 FROM #REPLICATIONMONITOR WHERE LOGREADERAGENTSTATUS NOT IN
('In Progress', 'Idle')
OR DISTRIBUTIONAGENTSTATUS NOT IN ('In Progress', 'Idle') OR WARNING = 'Expiration'
OR PERFORMANCE IN ('Poor', 'Critical'))
BEGIN

SET @Xml = CAST(( SELECT 'tr/@bgcolor' = CASE 1 WHEN 1 THEN '#FF4500' ELSE '#FF4500' END,
td = PUBLISHER, '', td = PUBLISHER_DB , '', td = PUBLICATION, '', td = DISTRIBUTION_DB, '',
td = SUBSCRIBER, '',
td = SUBSCRIBER_DB, '', td = LATENCY, '', td = PERFORMANCE, '', td = WARNING, '',
td = LOGREADERAGENTSTATUS, '',
td = DISTRIBUTIONAGENTSTATUS, '', td = LAST_DISTSYNC, ''
FROM #REPLICATIONMONITOR WHERE LOGREADERAGENTSTATUS NOT IN ('In Progress', 'Idle')
OR DISTRIBUTIONAGENTSTATUS NOT IN ('In Progress', 'Idle') OR WARNING = 'Expiration'
OR PERFORMANCE IN ('Poor', 'Critical')
ORDER BY PUBLISHER, LATENCY DESC
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX))

SET @Mailtext = @Mailtext + @Xml +'
' + @Note

SET @MailSubject = 'Replication Error - Please check Status for Publications and
Subscriptions listed in email'
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name =
''SQLDBAExpertsmail'', @recipients = ''' + @Recipients + ''',
@subject = ''' + @MailSubject + ''', @body = ''' + @Mailtext + ''',
@body_format = ''HTML'' '
EXEC (@MailString)

END

DROP TABLE #REPLICATIONMONITOR

=========

USE [distribution]
GO
/****** Object: StoredProcedure [dbo].[usp_GetReplicationMonitorData] Script Date: 5/17/2022 10:37:57 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_GetReplicationMonitorData]

--@Mailtext VARCHAR(5000) OUTPUT

AS

BEGIN

SET NOCOUNT ON

DECLARE @Sqltext NVARCHAR(MAX)

DECLARE @Publisher SYSNAME, @PublisherDB SYSNAME, @Publication SYSNAME, @Distdb SYSNAME, @Subscriber SYSNAME, @SubscriberDB SYSNAME

IF OBJECT_ID('tempdb..#REPLMONITORPUBLISHER') IS NOT NULL

DROP TABLE #REPLMONITORPUBLISHER

CREATE TABLE #REPLMONITORPUBLISHER

(

SN INT IDENTITY(1,1)

,PUBLISHER SYSNAME NULL

,DISTRIBUTION_DB SYSNAME NULL

,STATUS INT NULL

,WARNING INT NULL

,PUBLICATIONCOUNT INT NULL

,RETURNSTAMP VARCHAR(100) NULL

)

IF OBJECT_ID('tempdb..#REPLMONITORSUBSCRIPTION') IS NOT NULL

DROP TABLE #REPLMONITORSUBSCRIPTION

CREATE TABLE #REPLMONITORSUBSCRIPTION

(

SN INT IDENTITY(1,1)

,PUBLISHER SYSNAME NULL

,DISTRIBUTION_DB SYSNAME NULL

,STATUS INT NULL

,WARNING INT NULL

,SUBSCRIBER SYSNAME NULL

,SUBSCRIBER_DB SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,PUBLICATION SYSNAME NULL

,PUBLICATION_TYPE INT NULL

,SUBTYPE INT NULL

,LATENCY INT NULL

,LATENCYTHRESHOLD FLOAT NULL

,AGENTNOTRUNNING INT NULL

,AGENTNOTRUNNINGTHRESHOLD INT NULL

,TIMETOEXPIRATION INT NULL

,EXPIRATIONTHRESHOLD INT NULL

,LAST_DISTSYNC DATETIME NULL

,DISTRIBUTION_AGENTNAME SYSNAME NULL

,MONITORRANKING INT NULL

,DISTRIBUTIONAGENTJOBID BINARY(16) NULL

,DISTRIBUTIONAGENTID INT NULL

,DISTRIBUTIONAGENTPROFILEID INT NULL

,LOGREADERAGENTNAME SYSNAME NULL

)

IF OBJECT_ID('tempdb..#REPLLOGREADERAGENT') IS NOT NULL

DROP TABLE #REPLLOGREADERAGENT

CREATE TABLE #REPLLOGREADERAGENT

(

SN INT IDENTITY(1,1)

,DBNAME SYSNAME NULL

,NAME SYSNAME NULL

,STATUS INT NULL

,PUBLISHER SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,START_TIME DATETIME NULL

,TIME DATETIME NULL

,DURATION INT NULL

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT NULL

,DELIVERED_TRANSACTIONS INT NULL

,DELIVERED_COMMANDS INT NULL

,AVERAGE_COMMANDS INT NULL

,DELIVERY_RATE INT NULL

,DELIVERY_LATENCY INT NULL

,ERROR_ID INT NULL

,JOB_ID BINARY(16) NULL

,LOCAL_JOB INT NULL

,PROFILE_ID INT NULL

,AGENT_ID INT NULL

,LOCAL_TIMESTAMP BINARY(8) NULL

)

IF OBJECT_ID('tempdb..#REPLDISTRIBUTORAGENT') IS NOT NULL

DROP TABLE #REPLDISTRIBUTORAGENT

CREATE TABLE #REPLDISTRIBUTORAGENT

(

SN INT IDENTITY(1,1)

,DBNAME SYSNAME NULL

,NAME SYSNAME NULL

,STATUS INT NULL

,PUBLISHER SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,PUBLICATION SYSNAME NULL

,SUBSCRIBER SYSNAME NULL

,SUBSCRIBER_DB SYSNAME NULL

,SUBSCRIPTION_TYPE INT NULL

,START_TIME DATETIME NULL

,TIME DATETIME NULL

,DURATION INT NULL

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT NULL

,DELIVERED_TRANSACTIONS INT NULL

,DELIVERED_COMMANDS INT NULL

,AVERAGE_COMMANDS INT NULL

,DELIVERY_RATE INT NULL

,DELIVERY_LATENCY INT NULL

,ERROR_ID INT NULL

,JOB_ID BINARY(16) NULL

,LOCAL_JOB INT NULL

,PROFILE_ID INT NULL

,AGENT_ID INT NULL

,LOCAL_TIMESTAMP BINARY(8) NULL

,OFFLOAD_ENABLED BIT NULL

,OFFLOAD_SERVER VARCHAR(50)

,SUBSCRIBER_TYPE INT NULL

)

INSERT INTO #REPLMONITORPUBLISHER

SELECT * FROM OPENROWSET

('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',

' Exec sp_replmonitorhelppublisher WITH RESULT SETS

(

Publisher SYSNAME ,

distribution_db SYSNAME,

status INT,

warning INT,

publicationcount INT,

returnstamp VARCHAR(100)

)

' )

DECLARE CURSORPUBLISHER CURSOR FOR

SELECT PUBLISHER, DISTRIBUTION_DB

FROM #REPLMONITORPUBLISHER;

OPEN CURSORPUBLISHER

FETCH NEXT FROM CURSORPUBLISHER

INTO @Publisher, @Distdb

WHILE @@FETCH_STATUS = 0

BEGIN

SET @Sqltext = 'SELECT ''' + @Publisher + ''', ''' + @Distdb + ''', STATUS, WARNING, SUBSCRIBER, SUBSCRIBER_DB,PUBLISHER_DB,

PUBLICATION ,PUBLICATION_TYPE, SUBTYPE, LATENCY, LATENCYTHRESHOLD,AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD, TIMETOEXPIRATION,

EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME, MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID,

DISTRIBUTIONAGENTPROFILEID, LOGREADERAGENTNAME

FROM OPENROWSET

(''SQLOLEDB'',''Server=(local);TRUSTED_CONNECTION=YES;'',

''EXEC ' + @Distdb + '.dbo.sp_replmonitorhelpsubscription @publisher = '''''+ @Publisher +''''', @publication_type = 0

WITH RESULT SETS

(

STATUS INT NULL

,WARNING INT NULL

,SUBSCRIBER SYSNAME NULL

,SUBSCRIBER_DB SYSNAME NULL

,PUBLISHER_DB SYSNAME NULL

,PUBLICATION SYSNAME NULL

,PUBLICATION_TYPE INT NULL

,SUBTYPE INT NULL

,LATENCY INT NULL

,LATENCYTHRESHOLD FLOAT NULL

,AGENTNOTRUNNING INT NULL

,AGENTNOTRUNNINGTHRESHOLD INT NULL

,TIMETOEXPIRATION INT NULL

,EXPIRATIONTHRESHOLD INT NULL

,LAST_DISTSYNC DATETIME NULL

,DISTRIBUTION_AGENTNAME SYSNAME NULL

,MERGEAGENTNAME SYSNAME NULL

,MERGESUBSCRIPTIONFRIENDLYNAME SYSNAME NULL

,MERGEAGENTLOCATION SYSNAME NULL

,MERGECONNECTIONTYPE SYSNAME NULL

,MERGEPERFORMANCE SYSNAME NULL

,MERGERUNSPEED SYSNAME NULL

,MERGERUNDURATION SYSNAME NULL

,MONITORRANKING INT NULL

,DISTRIBUTIONAGENTJOBID BINARY(16) NULL

,MERGEAGENTJOBID SYSNAME NULL

,DISTRIBUTIONAGENTID INT NULL

,DISTRIBUTIONAGENTPROFILEID INT NULL

,MERGEAGENTID SYSNAME NULL

,MERGEAGENTPROFILEID SYSNAME NULL

,LOGREADERAGENTNAME SYSNAME NULL

,PUBLISHER SYSNAME NULL

)

'') '

--PRINT (@Sqltext)

INSERT INTO #REPLMONITORSUBSCRIPTION (PUBLISHER, DISTRIBUTION_DB, STATUS, WARNING, SUBSCRIBER, SUBSCRIBER_DB,PUBLISHER_DB, PUBLICATION ,PUBLICATION_TYPE, SUBTYPE, LATENCY, LATENCYTHRESHOLD,

AGENTNOTRUNNING, AGENTNOTRUNNINGTHRESHOLD, TIMETOEXPIRATION, EXPIRATIONTHRESHOLD, LAST_DISTSYNC, DISTRIBUTION_AGENTNAME, MONITORRANKING, DISTRIBUTIONAGENTJOBID, DISTRIBUTIONAGENTID, DISTRIBUTIONAGENTPROFILEID, LOGREADERAGENTNAME)

EXEC (@Sqltext)

FETCH NEXT FROM CURSORPUBLISHER

INTO @Publisher, @Distdb

END

CLOSE CURSORPUBLISHER;

DEALLOCATE CURSORPUBLISHER;

INSERT INTO #REPLLOGREADERAGENT

SELECT * FROM OPENROWSET

('SQLOLEDB ','Server=(local);TRUSTED_CONNECTION=YES;',

'EXEC sp_MSenum_replication_agents @type = 2 WITH RESULT SETS

(

dbname SYSNAME

,name SYSNAME

,status INT

,PUBLISHER SYSNAME

,PUBLISHER_DB SYSNAME

,START_TIME DATETIME

,TIME DATETIME

,DURATION INT

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT

,DELIVERED_TRANSACTIONS INT

,DELIVERED_COMMANDS INT

,AVERAGE_COMMANDS INT

,DELIVERY_RATE INT

,DELIVERY_LATENCY INT

,ERROR_ID INT

,JOB_ID BINARY(16)

,LOCAL_JOB INT

,PROFILE_ID INT

,AGENT_ID INT

,LOCAL_TIMESTAMP BINARY(8)

)

')

INSERT INTO #REPLDISTRIBUTORAGENT

SELECT * FROM OPENROWSET

('SQLOLEDB','Server=(local);TRUSTED_CONNECTION=YES;',

'EXEC sp_MSenum_replication_agents @type = 3 WITH RESULT SETS

(

DBNAME SYSNAME

,NAME SYSNAME

,STATUS INT

,PUBLISHER SYSNAME

,PUBLISHER_DB SYSNAME

,PUBLICATION SYSNAME

,SUBSCRIBER SYSNAME

,SUBSCRIBER_DB SYSNAME

,SUBSCRIPTION_TYPE INT

,START_TIME DATETIME

,TIME DATETIME

,DURATION INT

,COMMENTS NVARCHAR(1000)

,DELIVERY_TIME INT

,DELIVERED_TRANSACTIONS INT

,DELIVERED_COMMANDS INT

,AVERAGE_COMMANDS INT

,DELIVERY_RATE INT

,DELIVERY_LATENCY INT

,ERROR_ID INT

,JOB_ID BINARY(16)

,LOCAL_JOB INT

,PROFILE_ID INT

,AGENT_ID INT

,LOCAL_TIMESTAMP BINARY(8)

,OFFLOAD_ENABLED BIT

,OFFLOAD_SERVER VARCHAR(50)

,SUBSCRIBER_TYPE INT

)

')

-- Default latency threshold is 30 sec. It is common to have 1-2 minute Publisher to Subscriber latency in replication.

-- With default threshold, we are getting frequent latency alerts. Hence as per discussion with Praveen, changing latency threshold to 120 sec for monitoring solution.

UPDATE #REPLMONITORSUBSCRIPTION SET LATENCYTHRESHOLD = 120

SELECT S.PUBLISHER, S.PUBLISHER_DB, S.PUBLICATION, S.DISTRIBUTION_DB, S.SUBSCRIBER, S.SUBSCRIBER_DB, S.LATENCY, S.LAST_DISTSYNC,

CASE WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 34 THEN 'Excellent' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 59 THEN 'Good' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 84 THEN 'Fair' WHEN (S.LATENCY/S.LATENCYTHRESHOLD)* 100 < 99 THEN 'Poor' ELSE 'Critical' END AS PERFORMANCE,

CASE S.WARNING WHEN 0 THEN 'NA' WHEN 1 THEN 'Expiration' WHEN 2 THEN 'Latency' ELSE 'Unknown' END AS WARNING,

CASE L.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS LOGREADERAGENTSTATUS,

CASE D.STATUS WHEN 1 THEN 'Started' WHEN 2 THEN 'Stopped' WHEN 3 THEN 'In Progress' WHEN 4 THEN 'Idle' WHEN 5 THEN 'Retrying' WHEN 6 THEN 'Failed' ELSE 'Unknown' END AS DISTRIBUTIONAGENTSTATUS

FROM #REPLMONITORSUBSCRIPTION S

LEFT OUTER JOIN #REPLLOGREADERAGENT L ON S.LOGREADERAGENTNAME = L.NAME AND S.DISTRIBUTION_DB = L.DBNAME AND S.PUBLISHER = L.PUBLISHER AND S.PUBLISHER_DB = L.PUBLISHER_DB

LEFT OUTER JOIN #REPLDISTRIBUTORAGENT D ON S.DISTRIBUTION_AGENTNAME = D.NAME AND S.DISTRIBUTION_DB = L.DBNAME

AND S.PUBLISHER = L.PUBLISHER AND S.PUBLISHER_DB = L.PUBLISHER_DB

/**Code comment by aslam

--AND S.PUBLICATION = D.PUBLICATION

Code comment by aslam **/

AND S.SUBSCRIBER = D.SUBSCRIBER AND S.SUBSCRIBER_DB = D.SUBSCRIBER_DB

DROP TABLE #REPLMONITORPUBLISHER

DROP TABLE #REPLMONITORSUBSCRIPTION

DROP TABLE #REPLLOGREADERAGENT

DROP TABLE #REPLDISTRIBUTORAGENT

END

=======================

mike01 · May 17, 2022, 6:34pm

Wow, this is very long. The error you are getting is when you are trying to insert into a temp table while executing a stored proc like below. You can't do this more than once in you procedure call

INSERT INTO #REPLICATIONMONITOR
EXEC (@Sql)

balu_pendyala · May 26, 2022, 5:54pm

Thank you for you reply.

If possible can you test it and give the updated script