0

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.

High Plains Grifter
  • 1,357
  • 1
  • 12
  • 36
  • Not related to your question... Those horribly named SSRS Subscription jobs in SQL Agent are named with their Guids from the Subscriptions table. Have you confirmed what happens yet after renaming these jobs and restarting SSRS? I believe it recreates them which means you'll start getting duplicate jobs. – AlwaysLearning Jun 12 '20 at 23:12
  • I'm not going to be renaming those; it is as you suggest a pointless task. However, the names are not the only information I wish to return to my users, hence the desire to return the report name as well as the GUID (not attempt to edit anything). – High Plains Grifter Jun 14 '20 at 11:46
  • Yeah, sorry about that, how SSRS pollutes the SQL Agents jobs list is one of its most annoying tendencies. When we need to manage jobs on an SSRS-enabled server we right-click on Jobs > filter > like `' '` (space character) to eliminate all of the GUIDs. – AlwaysLearning Jun 14 '20 at 12:49
  • If it helps, you can join dbo.Subscriptions.Report_OID to dbo.Catalog.ItemID to get the report paths and also cast(dbo.Subscriptions.ParametersXml as xml) to extract email addresses, report parameters, etc. with XQuery shredding. – AlwaysLearning Jun 14 '20 at 12:55
  • I refer you to the query in my question ;) – High Plains Grifter Jun 15 '20 at 12:02

0 Answers0