May 10, 2016 at 1:52 pm
The below T-SQL Code works good on SQL server 2008R2 server and it fails on SQL 2014 with below error message. can anyone from the group can help this t-sql code and suggest. the code checks for delay in subscribers and send an email alert.
The metadata could not be determined because statement 'exec @result = sys.xp_userlock 0, @dbid, @DbPrincipal, @Resource, @mode, @owner, @LockTimeout' in procedure 'sp_getapplock' invokes an extended stored procedure. [SQLSTATE 42000] (Error 11520). The step failed.
T-SQL statement:
DECLARE @min-2 INT, @max-2 INT, @sql NVARCHAR(4000)
DECLARE @repl_server_list TABLE(id INT IDENTITY, srvname sysname)
SET NOCOUNT ON
--build a stripped down temp table; the OPENROWSET allows us to skip fields we don't care about.
IF OBJECT_ID('tempdb..#tmp_subscriptiondata') IS NOT NULL
DROP TABLE #tmp_subscriptiondata
create table #tmp_subscriptiondata (
[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 int 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 int null ,
mergePerformance int null ,
mergerunspeed float null ,
mergerunduration int null ,
monitorranking int null ,
distributionagentjobid binary(30) null ,
mergeagentjobid binary(30) null ,
distributionagentid int null ,
distributionagentprofileid int null ,
mergeagentid int null ,
mergeagentprofileid int null ,
logreaderagentname sysname null
)
--list every server that our current server is handling distribution duties for.
--we do this since you can tell a different server to be the distributor.
INSERT INTO @repl_server_list
SELECT DISTINCT srvname --b.srvname,a.publisher_db,a.publication
FROM distribution.dbo.MSpublications a, master.dbo.sysservers b
WHERE a.publisher_id=b.srvid
--------------------------------
--Get list of all replications--
--------------------------------
SELECT @min-2 = MIN(id), @max-2 = MAX(id) FROM @repl_server_list
BEGIN
--Transactional Replication
select @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@servername + ';Trusted_Connection=yes'','
+ ' ''set fmtonly off; exec distribution..sp_replmonitorhelpsubscription @Publisher="' + srvname + '",@publication_type=0'')a'
FROM @repl_server_list WHERE id = @min-2
Insert Into #tmp_subscriptiondata
EXEC sp_executesql @sql
--Snapshot Replication
select @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@servername + ';Trusted_Connection=yes'','
+ ' ''set fmtonly off; exec distribution..sp_replmonitorhelpsubscription @Publisher="' + srvname + '",@publication_type=1'')a'
FROM @repl_server_list WHERE id = @min-2
Insert Into #tmp_subscriptiondata
EXEC sp_executesql @sql
--Merge Replication
select @sql = 'SELECT * FROM OPENROWSET(''SQLNCLI'', ''Server=' + @@servername + ';Trusted_Connection=yes'','
+ ' ''set fmtonly off; exec distribution..sp_replmonitorhelpsubscription @Publisher="' + srvname + '",@publication_type=2'')a'
FROM @repl_server_list WHERE id = @min-2
Insert Into #tmp_subscriptiondata
EXEC sp_executesql @sql
END
SELECT * FROM #tmp_subscriptiondata
-------------------
--Reporting Email--
-------------------
--variables and tablevar defined here to more easily add/modify/test rules
DECLARE @tableHTML NVARCHAR(MAX)
,@MailSubject VARCHAR(100)
, @rowcount INT
DECLARE @final_error_list TABLE (
subscriber sysname null ,
subscriber_db sysname null ,
publisher_db sysname null ,
publication sysname null ,
warning int null ,
last_distsync datetime null ,
hours_delayed INT,
distribution_agentname sysname null
)
SELECT @MailSubject = '[Replication] Delays/Errors on ' + @@servername
--using an interim table so that we can query it to see how many they are;
--we could use a CTE but then we have no easy way, short of checking the HTML length,
--of verifying there are records that need to be emailed.
INSERT INTO @final_error_list
SELECT subscriber, subscriber_db, publisher_db, publication, warning, last_distsync,
DATEDIFF(hh,last_distsync, GETDATE()) AS Hours_Delayed,distribution_agentname
FROM #tmp_subscriptiondata WHERE warning > 0
UNION ALL
SELECT subscriber, subscriber_db, publisher_db, publication, warning, last_distsync,
DATEDIFF(hh,last_distsync, GETDATE()) AS Hours_Delayed, distribution_agentname
FROM #tmp_subscriptiondata
--rule 1 - ignore publications that are current in the last hour or are currently running.
DELETE FROM @final_error_list
WHERE last_distsync > DATEADD(mi,-60, GETDATE()) OR last_distsync IS null
--rule 2 - ignore subscriptions that only run once a day, after midnight
DELETE FROM @final_error_list
WHERE (publication = 'eRxDEA' AND last_distsync > CONVERT(CHAR(8),GETDATE(),112))
IF (SELECT COUNT(*) FROM @final_error_list)>0
BEGIN
select @tableHTML = N'<H3>Replication Delays and Errors</H3>'
+ N'<table border="1">' + N'<tr>'
+ N'<th>Subscriber</th>' +
+ N'<th> Subscriber_DB </th>'
+ N'<th> Publisher_DB </th>'
+ N'<th> Publication </th>'
+ N'<th>Warning</th>'
+ N'<th> Last_Distsync </th>'
+ N'<th> Hours</th>'
+ N'<th> Distribution_AgentName</th>'
+ N'</tr>' + CAST((SELECT td = RTRIM(LTRIM(T.Subscriber))
,''
,td = RTRIM(LTRIM(T.Subscriber_DB))
,''
,td = RTRIM(LTRIM(T.Publisher_DB))
,''
,td = RTRIM(LTRIM(T.Publication))
,''
,td = RTRIM(LTRIM(T.Warning))
,''
,td = CONVERT(VARCHAR(16), T.Last_Distsync, 120)
,''
,td = CONVERT(VARCHAR(3), T.Hours_Delayed)
,''
,td = RTRIM(LTRIM(T.Distribution_AgentName))
FROM @final_error_list T
ORDER BY T.[Warning] DESC, T.last_distsync ASC
FOR
XML PATH('tr')
,TYPE
) AS NVARCHAR(MAX)) + N'</table>';
--PRINT @tableHTML
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'TEST@TEST.com',
@subject = @MailSubject, @body = @tableHTML, @body_format = 'HTML';
END
DROP TABLE #tmp_subscriptiondata
May 10, 2016 at 2:06 pm
Not sure exactly which line is causing the error, but I expect that adding WITH RESULT SETS will fix it.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
May 10, 2016 at 2:06 pm
It includes a workaround posted in here: https://blogs.msdn.microsoft.com/sqlagent/2012/07/12/workaround-sql-server-2012-openrowset-on-sp_help_job-throws-the-metadata-could-not-be-determined/
May 10, 2016 at 2:08 pm
Luis Cazares (5/10/2016)
Check this: https://connect.microsoft.com/SQLServer/feedback/details/737341/sql-server-2012-openrowset-on-msdb-dbo-sp-help-job-throws-errorIt includes a workaround posted in here: https://blogs.msdn.microsoft.com/sqlagent/2012/07/12/workaround-sql-server-2012-openrowset-on-sp_help_job-throws-the-metadata-could-not-be-determined/
Blast you are fast Luis, I was still typing an identical message when I saw yours
😎
Even have the connect item bookmarked.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply