I have a stored proc that returns details of SQL Agent Jobs on the local server. There is a master script that calls this proc, using OPENQUERY, against every SQL server in the ecosystem. In pseudocode, the master script looks something like this:
FOR EACH @LinkedServer in the list
SET @SQL = 'INSERT #Results SELECT * FROM OPENQUERY(' + @LinkedServer + ',''EXEC ScriptToGetAgentJobInfo'')'
EXEC sp_executesql @SQL
NEXT @LinkedServer
Some of the agent jobs are created from SSRS report subscriptions, so they have horrible looking names. In order to replace them with the name of the report that is the target of the subscription, I appeal to the ReportServer database on the @LinkedServer
, as part of the ScriptToGetAgentJobInfo
.
However, not every server contains a ReportServer database, so sometimes this appeal would fail. To get round that failure, I have the following lines of script:
DECLARE @Reports TABLE
( AgentJob SYSNAME
,Reportname NVARCHAR(128));
IF EXISTS(SELECT 1 FROM [master].[dbo].sysdatabases WHERE [name] = 'ReportServer')
BEGIN;
INSERT @Reports(AgentJob,Reportname)
SELECT Job.job_id, Report.[Name]
FROM
ReportServer.dbo.ReportSchedule AS ReportSched
INNER JOIN dbo.sysjobs AS Job ON CONVERT(SYSNAME,ReportSched.ScheduleID) = Job.[name]
INNER JOIN ReportServer.dbo.Subscriptions AS Subscription ON ReportSched.SubscriptionID = Subscription.SubscriptionID
INNER JOIN ReportServer.dbo.[Catalog] AS Report ON Subscription.report_oid = Report.itemid;
END;
The idea is that if the reportserver database doesn't exist, I can avoid any calls to it, which would error, but if it does, I can get data from it. I then join the @Reports
table to my SQL Agent job query with a LEFT JOIN
to show the name of the relevant report if there is one.
All this works fine when I run the script locally, but when it is called through the master procedure, I get an error saying Invalid object name 'ReportServer.dbo.ReportSchedule'.
I can get round this problem by making the reportserver select statement "dynamic" (although it is totally static) and calling it with another sp_executesql
call, but I really hate doing this!
So my question is this: Why does the error only occur when calling the script remotely and how can I avoid it without recourse to dynamic sql?
The master script is written and run in SQL Server 14.0, while the linked server that is causing the problem is only on SQL Server 10.50.