2

I am trying to mimic the functionality of a data driven subscription. We are current running a version of report server that does not allow for Data Driven Subscripts. Current this is what I am working with

use ReportServer 

declare @datetime varchar(29)
set @datetime = convert(varchar(16),dateadd(MINUTE,2,GETDATE()), 126) +  ':00.000-05:00'


update Subscriptions

set MatchData = '<ScheduleDefinition xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><StartDateTime xmlns="http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">' + @datetime + '</StartDateTime></ScheduleDefinition>',

modifieddate = convert(varchar(23),GETDATE(), 121)

where SubscriptionID = '48FB6FE9-969B-4E8D-AD37-E80CE20FDDE5'



update Schedule

set StartDate = CONVERT(VARCHAR(16), dateadd(minute, 2, GETDATE()), 121) +   ':00.000',

NextRunTime = null

where Name = '3664a678-af17-4a05-9ff4-5666ea727e91'

It updates the report server however when the time comes it does not execute. Does anyone know how I can trigger this?

nikolifish
  • 503
  • 1
  • 5
  • 18
  • You need to create the matching SQL Agent job as well. – Jamie F Nov 14 '11 at 23:41
  • actually i don't think that will be necessary. we use a third party program to import our data into our database. We can choose to use a post processing procedure. In that procedure I would simply exec the sp to set up the next report a minute or two later. for now i'm trying to understand what other tables need to be updated in order for this to work. – nikolifish Nov 15 '11 at 00:41
  • If you are trying to send SSRS subscriptions, I think so. SSRS subscriptions are actually triggered by a SQL Agent job, so if you want to create a subscription from scratch, you need to create the appropriate job. Your code above doesn't create these: http://blogs.msdn.com/b/deanka/archive/2009/01/13/diagnosing-and-troubleshooting-subscriptions.aspx – Jamie F Nov 15 '11 at 03:14

1 Answers1

0

I was short on time when I left my comment, but I just took the time to find a good explanation. From an MSDN blog:

When you create a subscription several things are added to the RS server:

  • A row is placed in the Subscriptions table identifying the name of the report, along with parameter settings, data driven query info and so on to process the subscription

  • A row is placed in the Schedule and ReportSchedule tables with the timing of the subscription

  • A SQL Server Agent job is created to control the scheduled execution of the report, and this is stored in the sysjobs and sysjobsteps of the MSDB database. The agent job name is a guid value that represents the ScheduleId (yes, in case you've not yet noticed, this makes your Agent Job list messy with a bunch of guids that make it hard to work with your non-subscription jobs, we are hearing you:).

It looks like you are making entries to two of the three needed tables, and you still need to create a SQL Agent job.

As the author points out, these aren't supported for direct manipulation, so test heavily, and don't expect much support if things break.

Alternatively you could create these subscriptions dynamically coding against the SSRS Web Services, such as the Subscription and Delivery methods

Jamie F
  • 23,189
  • 5
  • 61
  • 77
  • Thank you very much. Right now I just have it hooked up with create the SQL Server Agent Job and that seems to be working fine. It almost seems as though i dont need the rows added to the SChedule, ReportSchedule and SUbscription tables. For those looking to do the same thing you can script the job as drop/create and from there its a matter of hooking up a few parameters to deal with the dynamic nature of the UIDs. Thanks again – nikolifish Nov 16 '11 at 13:56