sqlshots: Delivering subscriptions outside the Organization
Recently I faced an issue with reporting services 2005 when attempting to deliver subscriptions to addresses outside of the organization. Internal addresses received the email based subscription deliveries without any questions. I must have checked and re-checked the settings using RSConfigTool about million times, looking for anything I might have overlooked. The error message I received was, "The e-mail address of one or more recipients is not valid". After some research (which lead me to a lot of dead ended forums) I read the phrase "email relay", that's when the gears started spinning.
I realized that the issue had nothing to do with the configuration of SQL Server Reporting Services; rather, the SMTP server! In order for the messages to be delivered outside of the organization the Reporting Services Server needed to be authorized so-to-speak. Unfortunately I don't have access to Exchange 2003 so I cannot provide screen shots, but for 2007 all you need to do is add the server's IP Address to the SMTP server’s receiver group in the HUB Transport configs.
Then to test your subscription without tweaking the schedule execution time just run the SQL job! First find out the name of the job by using the attached sql script, then execute the sp_start_job. If you have a named instance append $instancename to all three of ReportServer occurrences within the sql script (i.e. ReportServer$InstanceName) for MSSQL 2005. I believe for MSSQL 2008 you would append _InstanceName (i.e. ReportServer_InstanceName), but I am not certain. You should get the results similar to the screen shot attached.
Using the script with a default instance
SELECT sj.[name] AS [Job Name], c.[Name] AS [Report Name], c.[Path], su.Description, su.EventType, su.LastStatus, su.LastRunTime FROM msdb..sysjobs AS sj INNER JOIN ReportServer..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN ReportServer..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID INNER JOIN ReportServer..[Catalog] c ON su.Report_OID = c.ItemID
Using the script with a named instance
SELECT sj.[name] AS [Job Name], c.[Name] AS [Report Name], c.[Path], su.Description, su.EventType, su.LastStatus, su.LastRunTime FROM msdb..sysjobs AS sj INNER JOIN ReportServer$InstanceName..ReportSchedule AS rs ON sj.[name] = CAST(rs.ScheduleID AS NVARCHAR(128)) INNER JOIN ReportServer$InstanceName..Subscriptions AS su ON rs.SubscriptionID = su.SubscriptionID INNER JOIN ReportServer$InstanceName..[Catalog] c ON su.Report_OID = c.ItemID
Execute the report
USE [msdb] EXEC sp_start_job @job_name = 'AF015D8B-D80D-4D2A-9808-CD1D519B3332'
Correction
In the attached file listed below I have identified a typo. Currently it states on
Line 19: sp_start_sp (which is incorrect) it should be
Line 19: sp_start_job
Comments [0]