THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Greg Low (The Bit Bucket: IDisposable)

Ramblings of Greg Low (SQL Server MVP, MCM and Microsoft RD) - SQL Down Under

Data Driven Subscriptions in SQL Server 2005 Standard Edition

This blog has moved! You can find this content at the following new location:

Published Wednesday, August 13, 2008 7:28 PM by Greg Low

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Stephen Morris said:

wouldn't an application Lock be easier than the global temp table ?

August 13, 2008 8:56 AM

jerryhung said:

Nicely done, bookmarked for future usage

August 13, 2008 10:15 AM

Greg Low said:

Hi Stephen,

Yep, they would have been another option. At first I was targeting SQL Server 2000 as well though.



August 13, 2008 6:52 PM

Wile1one said:

Great bit of code Greg...

I wonder could you wrap this into a class object for recall by external procs...  rather than just a stored proc? Eg a remote data service could leverage this...


August 16, 2008 11:24 AM

loveSQL said:

Is there a bug to replace the new parameters?  I don't think the Parameter Token starts and ends with '|'.  

Why replace the token with value?i.e. SET @newParameters = REPLACE @newParameters,@ParameterToken,@ParameterValue);  

Should it not be replacing the old paramenter values with the new values?  I am lost.

August 19, 2008 8:16 PM

Greg Low said:

I have a feeling you might not have read the article that this refers to. What it suggested doing is making your parameter values be a token that wouldn't occur in the subscription ie: he set |TO| as the value for the EmailTo parameter. Then all we're doing is replacing these values before running the report.

August 19, 2008 9:21 PM

loveSQL said:

My bad.  I finally got it working.  Now, it works like a charm.  If a parameter value like '|CC|' is not supplied, it would show '|CC|'.  I replace all parameter values with '', if not supplied.  Thanks, Greg!

August 20, 2008 6:59 PM

Jeremy Finch said:


I believe one further improvement, which I have already posted on Jason's message, is the removal of the wait time. What if the report takes minutes to run? What if you want to run it from a trigger?

That was the issue for me, and making data entry wait, was not acceptable.

Here is the Pseudo code:

Originally this SP was written to

1- make all declarations

2- get data to replace (this was dependent on the text being |TO| or something similar- static

3- replace data with new data

4- reset data back to static values to use SP again

New method-

1-make all declarations

2-reset data (just using an update)

3-get data

4-replace data

// note* you could also use the UPDATETEXT method, instead of Update

Update Subscriptions

Set ExtensionSettings = ~OriginalValue~

Where (SubscriptionID = @subscriptionID)

The original value, is the value the field has when the job is first created, or after the original SP was run, and the values were reset.

This allowed me to be rid of the delay.

Any questions just let me know.



September 13, 2008 1:11 PM

Greg Low said:

Hi Jeremy,

The delay wasn't to run the report. It was just to give the report job time to start. I was going to do it by looking at the job status info but didn't get around to it.

And UPDATETEXT shouldn't be used now; it's deprecated.



September 13, 2008 9:56 PM

Laura said:

You have code here to force a wait until nothing else is using the subscription system. (see comment "we need to wait for our turn at using the subscription system").

What happens if 2 reports try to use the subscription system at the same time?  What can you do to fix whatever this does?

November 5, 2008 12:33 PM

Greg Low said:

Hi Laura,

That scenario is fine. That's what my comment #4 was about. I've used a global temp table to serialise access to it until it's launched.



November 5, 2008 11:27 PM

Paula said:


 got any updates on this?  i'm stuck using this, and the waitime is either too long or too short.  at times my reports show the SU because they got set to fast, or the subject line NEVER gets set back to SU.  any suggetions?



April 24, 2009 5:20 PM

Doug Rixmann said:

Maybe I missed something but because I didn't like leaving all my parameters as strings, I added this function that replaces the value of a certain parameter (ex. "DatabaseTableID").


SET @newParameters = REPLACE(@newParameters,@ParameterToken,@ParameterValue);

Would be replaced by:

SET @newParameters = dbo.ReplaceParameterValue(@newParameters,@ParameterToken,@ParameterValue);

CREATE FUNCTION [dbo].[ReplaceParameterValue]


-- Add the parameters for the function here

@ParameterString varchar(max),

@Parameter varchar(max),

@Value varchar(max)


RETURNS varchar(max)




@NPOS int,




SELECT @NPOS = CHARINDEX('<Name>' + @Parameter, @ParameterString, 0) + 6

SELECT @NPOSSTARTVALUE = CHARINDEX('<Value>', RIGHT(@ParameterString, LEN(@ParameterString) - @NPOS + 1), 0)

SELECT @NPOSENDVALUE = CHARINDEX('</Value>', RIGHT(@ParameterString, LEN(@ParameterString) - @NPOS + 1), 0)



+ @Value

+ RIGHT(@ParameterString, LEN(@ParameterString) - (@NPOS + @NPOSSTARTVALUE + @NLENGTH) - 5)


November 2, 2009 3:54 PM

Oliver Holloway said:

Greg, is the table usage different in SSRS 2008? Seems like the proc expects the first placeholders to be in the extensionsettings field, but they're all in the parameters field when I create the schedule.

Paula, there are various ways to resolve the waittime. One is to check for the most recent finished runtime, described in Jason's article's forum, in the last post at the bottom of page 1:

November 2, 2009 4:26 PM

Guillaume said:


Does your code work with SQL Server 2008 ?



December 15, 2009 9:06 AM

Guillaume said:

Yes, that's working :)


January 6, 2010 5:42 AM

Darko said:

Thank you Greg! And thank you Doug, too. Your solution made an excellent solution even better!

Few notes:

For Parameter Tokens (e.g. "Comment") to actually be present in the ExtensionSettings XML, one has to add some dummy values first into the appropriate fields in the RS Subscription Scheduler.

Speaking of the Comment field (in Reporting Services subscription), it can be fully HTML formatted, as long as angle brackets (and other symbols capable of confusing the XML parser) are properly substituted with &lt;, &gt; etc.

In my solution, I am passing additional arguments (such as Comment, Subject, Priority, RenderFormat, etc), passing them like this:

SET @newExtensionSettings = dbo.ReplaceParameterValue(@newExtensionSettings,'TO',@EmailTo);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'CC',@EmailCC);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'BCC',@EmailBCC);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'ReplyTo',@EmailReplyTo);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'Comment',@EmailBody);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'RenderFormat',@RenderFormat);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'Priority',@Priority);

SET @newExtensionSettings =  dbo.ReplaceParameterValue(@newExtensionSettings,'Subject',@Subject);


March 23, 2010 4:07 PM

Erik Scofield said:

Good job with the changes.

Thank you Jason L. Selburg and Greg Low

I have made some changes myself.  

First, I was not happy with the shortcomings of "Replace"

So I converted

@newParameters varchar(8000)  To @newParameters xml

And changed the parameter looping.

  WHILE @parameterCounter <= @numberOfParameters BEGIN

     SELECT @parameterToken = ParameterToken,

            @parameterValue = ParameterValue,

            @tokenLength = LEN(ParameterToken)

       FROM @parameters

       WHERE ParameterID = @parameterCounter;

SET @newParameters.modify('replace value of (/ParameterValues/ParameterValue/Name[text()=sql:variable("@ParameterToken")]/../Value/text())[1] with sql:variable("@ParameterValue")')        

SET @parameterCounter = @parameterCounter + 1;


Also, the locking just did not work for me, well the locking does, but we need a way to tell with the job has completed and I also changed from executing the job to triggering the event myself.

declare @repID uniqueidentifier

select @repID = reportid from ReportSchedule where SubscriptionID = @subscriptionID

declare @lastRunID bigint, @newRunID bigint

select top 1 @lastRunID = logentryid from ExecutionLogStorage where ReportID = @repID order by LogEntryId desc

print @lastRunID

 -- run the job

 --EXEC msdb..sp_start_job @job_name = @ScheduleID

exec ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@subscriptionID

--  WAITFOR DELAY '00:00:03';

 -- make enough delay for the report to have started

 WHILE NOT EXISTS(SELECT top 1 logEntryID FROM ExecutionLogStorage where logEntryID > @lastRunID and ReportID = @repID order by LogEntryId desc)

   WAITFOR DELAY '00:00:03';

Do the proc is complete.  It will exit once and finally the job is completely executed with in a 3 second window.


May 13, 2010 8:55 PM

Jimmy George said:


I am on an SQL2008 ENT SSRS deployment. More than 250 reports are to be delivered daily via fileshare and e-mail using data driven subscriptions. The reports are run based on multiple report parameter values ( say customer=subscription, fromdatetime and todatetime). The recurring reports need to check the last run status. If failed, it has to be run for the period covering the failed interval else the normal interval.

Can multiple report parameter values ( Customer, starttime, endtime) be derived using a TSQL stored proc and adjust the parameters and passed on to subscriptions at run time directly from the ReportServer databases ?

Cheers !


May 18, 2010 2:14 AM

Silvia said:

Thanks very much, Greg and Jason.

It works perfectly!!!!!!!!!

A great help.

November 29, 2010 2:39 PM

Isabel said:

Hi Greg,

I would like to pass multivalue parameters.

Is this possible?

Thx in advance.

January 27, 2011 10:25 AM

Victor said:

Does anyone have a solution for sending the report to a file share as well ?

February 14, 2011 2:20 PM

Kanaka said:

Thanks a lot.

February 2, 2012 4:47 PM

Sanjay said:

For some reason the parameter is not picked from the variable when executing the sp. Can anyone help?

April 18, 2012 11:58 PM

MGisonda said:

Anyone having a problem where the slows down. I am utilizing this to send out invoice. After about 50 invoices, the job slows down to a crawl. To pump out 140 invoices, it can take up to 9 hours to run. What's funny is that when running it manually (not through a job) it does the same thing. Visually though, it appears to get hung up after a few, then it suddenly bursts through with a few.

Anyone else having something like this happening? Any ideas where to look?


April 25, 2013 9:12 AM

Greg Low said:

Subscriptions can write out to file shares. No difference with this type of subscription.

April 25, 2013 9:18 PM

Tony Z said:

Thanks for this great work.

I have one problem though....

If my parameter is a list for use in a WHERE FLD1 IN ('1','2','3')

my parameter string wound look like


This isn't handled and I don't think it can be.  The only solution I can think of is to load a pvalue table and use it in the report...

WHERE FLD1 IN (select fld1vals from pvalues) and don't pass a parameter to the report.

Is that about it?


May 19, 2013 4:00 PM

Greg Low said:

Hi Tony,

That would work. An alternative would be to use XML for the parameters, and then split them out of there.



May 19, 2013 6:18 PM

Megha said:

Hi Greg,

I've used this code to send out weekly report emails.

I have a dummy |To| field which gets replaced by database email address.

Every week when the report executes, and if there are 5 distnct people who should receive email, 1 person gets the reminder twice while 1 person is completely missed out and 3 others get it fine as expected.

Any ideas/pointers what could be going wrong?

I have an agent job which triggers this report execution SP. From my logs I can see that exactly 5 distinct emails were set.

I'm really stuck at this instance and dont have an idea!!



May 21, 2013 8:35 PM

Greg Low said:

Hi Megha,

It just sounds like the first one isn't getting sent before the data gets updated. That's what the delay was for. In your case it might not be long enough.



June 4, 2013 1:16 AM

Peter Z said:

Hi Greg:

What would it take to modify your code so that the name of a report could be changed? For example, if a report runs daily or weekly, append a date stamp to the end of the report name?



June 20, 2013 12:46 PM

Greg Low said:

Hi Peter,

Not quite sure I totally follow how you would want the report name to change. Is this in relation to reports that are sent to shares, etc. and you're talking about the filename?

(Mind you it's 3AM and I'm up for a SQL 2014 session so I might not be awake enough to understand the issue)



June 20, 2013 12:59 PM

Peter Z said:

Wow, 3AM... I'm truly grateful you replied so quickly given your own work at the moment.

Essentially, I'd like to change the name of a report (filename) when delivered either via email attachment or as a file stored in a file directory. The report name in report manager remains unaltered.

I came across another article by Jason Selburg "Dynamically name report/file export in SSRS" ( which addresses this need, but his report name change article serves to modify the code from his original article that you cite at the beginning of your blog.

Given that your article is an improvement of Jason's data-driven subscription approach, I found myself wondering how your code could be altered to allow for a report name change when the output is either an email attachment or fileshare.

Many thanks in advance for your assistance!


June 20, 2013 1:36 PM

Eugene said:

Very cool thanks for this!  Have modified it for a SharePoint delivery subscription and works a charm!

October 21, 2013 5:49 AM

Eugene said:

Hi Greg, I have one suggestion, just wondering if you see any problems:

In the Select statement to get the Extension Setting and Parameters from the Subscriptions table I've added "@LastRunTime = LastRunTime" and then instead of the wait for 30 seconds to let the job start I've now got:

WHILE (SELECT LastRunTime FROM dbo.Subscriptions WHERE SubscriptionID = @subscriptionID) = @LastRunTime

WAITFOR DELAY '00:00:01'

Insodoing it waits until the job's done before moving on.  I have one subscription that will run on a couple thousand records (saving PDFs to SharePoint) so just looking for ways to save some time.

Thanks again for this!

October 21, 2013 10:58 AM

Greg Low said:

Hi Eugene,

That sounds like a reasonable plan.

Glad to hear that it helped.


October 21, 2013 5:43 PM

Sameer said:

Greg, Thanks for the wonderful solution.

I have the same issue as of Peter. I want to have the report name based on the parameter provided to the Stored Procedure.

For parameters below, I want the report name on the file server to be


EXEC dbo.ExecuteSubscribedReport

  @ScheduleID = '4CE38C83-6A03-4780-895A-92FD6F8FD5B0',

  @EmailTo = '',

  @EmailCC = '',

  @EmailBCC = '',

  @EmailReplyTo = '',

  @EmailBody = 'Hello Greg',

  @ParameterList = '|StartDate|,20071231,|Salesperson|,GE,|Region|,NW';

If I provided Fidelity instead of GE in the parameter the report name should change to Fidelity_NewReport.

February 5, 2014 6:24 PM

Greg Low said:

Hi Sameer, do you have a number of reports with exactly the same parameters and you want to pass which report you want as a parameter? Am I understanding that correctly?

If that's the case, why wouldn't it just be one report with a parameter in the first place?

If that's not the case, I might need you to explain further what you are trying to achieve.

February 6, 2014 11:00 PM

Brian said:

Hi Greg,

Thank you for providing this information - I have been searching for something like this for a long time.

I have an issue where the stored procedure seems to be in a perpetual 'wait' state.  When executed, it would never complete so I checked the Activity Monitor and see that it is in a 'Suspended' status with a 'WaitFor' Wait Type.

What would be causing this?  I copied the SP as it is in the article and created it in the MSDB database since it references the reporting tables.

I apologize if this is a rookie question but that's what I am when it comes to SSRS.  : )



February 12, 2014 3:21 PM

Ajay said:

Hi Greg, Thanks for providing the SP.

I have an issue. Although the SP runs without any errors, I do not get a mail.

I am testing with just one parameter (its a phone number). I have a report that distributes monthly phone bills to users based on the number (as parameter).

My parameterlist has just one value passing.

February 12, 2014 7:02 PM

Greg Low said:

Hi Brian,

Can you check that you entered the waitfor delay correctly? In the original code I had it set for 30 seconds. Sounds like you have it waiting much longer.

February 12, 2014 7:11 PM

Greg Low said:

Hi Ajay, does it work correctly for the job when you first set it up ie: if you run the job manually using your initial parameter?

February 12, 2014 7:12 PM

Ajay said:

Hi Greg. I have Data Driven Sub working in Enterprise Edition (EE) for Test only. So I replicated the same but in Licensed Standard Edition of SQL and created template as TO, CC, BC etc.

The report runs fine in Reporting Services when entering the phone number.

However when I Exec procedure in sql query, it does not send a mail. But I get confirmation that the job started successfully with 1 row affected.

February 12, 2014 8:16 PM

Greg Low said:

If the job ran but didn't do what you needed, right-click the job and check the agent history. Make sure to drill into the step, and note any info down the bottom of the page.

February 12, 2014 8:52 PM

Eric said:

Dear Greg,

I am new at this so I probably do something wrong... but I an stuck.

I have created a report with only one parameter øuser

This report is different for each user (it is a sales pipeline)

I have created a subscription to get the scheduleID (I had to select 'Sandy' ID as user parameter for the report)

When I call the proc the report is generated and the email is sent but whichever userID I enter in the parameter list I am getting the report with Sandy's data (the exact one I created in SSRS)

EXEC dbo.ExecuteSubscribedReport

  @ScheduleID = 'F8EC8EDA-1F3F-48E7-899F-C90BBC09293F',

  @EmailTo = '',

  @EmailCC = '',

  @EmailBCC = '',

  @EmailReplyTo = '',

  @EmailBody = '',

  @ParameterList = '|user|,3428CD9C-78FB-E211-B8D1-00155D6CD610';

Thank you for your help

March 21, 2014 6:34 AM

Greg Low said:

Is your parameter definitely called "user" all lower-case? Does the report work ok with different GUID values if you run it manually? That seems like an odd way to select a user.

March 24, 2014 1:51 AM

Carl Nickol said:

Dear Greg,

I'm trying to Implement Data Driven Feature in SQL Server 2012 Standard Edition, My Requirement is to create Multiple PDF files using same RDL and locate it into the common shared drive. I have a Stored Procedure which will return the value based on the input from another Store Procedure and the Name of the files should also be retrieved from the same procedure for the pdf files. i can able to do it in developer edition as the data driven is available and not in standard edition,

can you please help on this?

March 25, 2014 6:59 AM

Greg Low said:

Hi Carl,

The method shown would do most of that for you, except that you don't get to name the PDF. If you need to do that, you could add a step to the Agent job to rename the file, based on data returned from a proc.

March 25, 2014 5:46 PM

Dan said:

Hi Greg, apologies for the really dumb question, but how do I execute the stored procedure so it loops through the results of a query as a data driven sub would (with my email addresses and parameter for the main report within it) and parse these as @EmailTo = EMAIL_FIELDNAME etc  ?

April 4, 2014 5:23 PM

Greg Low said:

Hi Dan, the easiest for you will probably be to just open a cursor (forward only, read only) for the query, and just keep fetching results into variables. Call the procs using the variables.

April 4, 2014 8:01 PM

Squeaks said:

Hi Greg

Managed to get this working for half of my email addresses and then started getting a sharepoint oswstimer error /locking error and it has refused to work since :(  Have you come across this and know any way of getting around it?

April 7, 2014 12:57 PM

Greg Low said:

No but sounds very much a sharepoint issue rather than a sql server one.

April 8, 2014 5:48 PM

Brian said:

Hello Greg - thanks again for putting this out there.  It's been great for a non-Enterprise client of mine.

One issue I'm having though is with the parameters.  When I run the stored proc as-is it doesn't seem to pass the parameters to the subscription.  As you know, a default value has to be stored for any parameters on the subscription page.  I got the parameter "passing" section of the SP to work when I changed the @ParameterToken variable in the REPLACE statement:  


with the actual hard-coded parameter value stored in the subscription page.  This works for the one report that's passing parameters but now that i have another parameter report to use this on, I need to figure out what's going on.

FYI, I don't know if this has any impact but since I do not need the CC, BCC, or Reply-To on my reports I am leaving those blank on the subscription page and am populating those variables when executing the SP with blanks ('').

I know some have modified the REPLACE statement for other reasons but should the code work as it is in the SP?

Thanks for your help.


May 6, 2014 5:58 PM

Greg Low said:

Hi Brian,

Yes, it does work as written. The REPLACE is just doing a string replacement so if it's not working, and replacing the @ParameterToken value with a hard-coded value works, that can only mean that the variable isn't getting its value set in your version. You might need to just step through and debug the code around the WHILE loop to see why it's not getting set.

May 8, 2014 11:41 PM

Namnami said:

I need to send report with dynamic title in email subject (SSRS 2008). Any help?

June 8, 2014 5:15 AM

Greg Low said:

Hi Namnami, that should be pretty straightforward:

1. Add an extra parameter to the sproc:

@Subject varchar (1000) = NULL,

2. Set the Subject of the report to |SUBJECT| in the original subscription.

3. Add the following line after the other REPLACE options:

IF COALESCE(@Subject,'') <> ''

   SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|SUBJECT|',@Subject);

I haven't tried it but can't see why that wouldn't work the same as for the other fields.

June 15, 2014 12:36 AM

David Richardson said:


Great article and procedure.  And from the looks of the comments a number of users have been tweaking and enhancing..

Its now almost 7 years since your original post.. I would love to see a follow up with a "new and improved" version.

David R.

February 3, 2015 4:34 PM

Greg Low said:

Thanks David. I agree. I was thinking just the other day that I need to build one with all the best features and ideas. I'll add it to my to-do list.

February 3, 2015 5:36 PM

Jwover said:


Can you help me understand clearly whether or not this solution will work in a 2005 to 2008 scenario?  We want to use this capability in 2008 without buying the Enterprise license.  Need to have dynamic parameters.

Can it be done?

February 5, 2015 5:19 PM

Greg Low said:

The original versions works fine in 2005 and with the changes discussed in the comments, also works fine in 2008. The only question I have is about what requirements you have for dynamic parameters. What this supports is standard report parameters. The parameters need to be defined in the report already, but each execution can pass different values.

February 5, 2015 9:08 PM

Agni said:

It is great work, lovely. a genius work

February 16, 2015 12:59 PM

LauraMc said:

I love what you have done with this.  But I am having an issue with the replace as written "SET @newParameters = REPLACE @newParameters,@ParameterToken,@ParameterValue)".  I looked at what this does, and it is replacing the Name of my parameter with the Value of my parameter.  This causes the process to fail.  Any ideas?  I'm not sure what I am doing wrong.

If I hard code a value in the subscription set up and look for that hard coded value instead of the @ParameterToken it works fine. But since I cannot use the same hard coded value for EVERY parameter, that solution will not work dynamically.

Furthermore, if I have the "use default" checked on the report I can no longer modify that parameter (because the parameters in the subscription are NULL in that case).  I assume this is intended?

February 20, 2015 5:17 PM

LauraMc said:

I found a solution!  I had to find the original values for the parameters, then find those values in my parameter string.  I modified the code right after the extension settings are set:

-- Get the original parameter values so they can be replaced later


EXEC sp_xml_preparedocument @idoc OUTPUT, @OriginalParameters



   [id] INT,

   [parentid] INT,

   [nodetype] INT,

   [localname] VARCHAR(500),

   [prefix] VARCHAR(500),

   [namespaceuri] VARCHAR(500),

   [datatype] VARCHAR(500),

   [prev] VARCHAR(500),

   [text] VARCHAR(500)  



       ( id ,

         parentid ,

         nodetype ,

         localname ,

         prefix ,

         namespaceuri ,

         datatype ,

         prev ,




FROM   OPENXML (@idoc, 'ParameterValues',1)



   [Record] INT IDENTITY(1,1),

   [Value_id] INT


INSERT INTO @ValueTextIDTable( Value_id )

SELECT id FROM @OParamTable WHERE localname = 'Value'


 IF @numberOfParameters > 0 BEGIN

   -- process each parameter in turn

   SET @parameterCounter = 1;

   WHILE @parameterCounter <= @numberOfParameters BEGIN

     SELECT @parameterToken = ParameterToken,

            @parameterValue = ParameterValue,

            @tokenLength = LEN(ParameterToken)

       FROM @parameters

       WHERE ParameterID = @parameterCounter;

     SET @ValueText = (SELECT text FROM @OParamTable AS p JOIN @ValueTextIDTable AS v ON p.parentid = v.Value_Id WHERE v.record = @parameterCounter)

     SET @newParameters = REPLACE(@newParameters,@ValueText,@ParameterValue)

     SET @parameterCounter = @parameterCounter + 1;



February 20, 2015 8:39 PM

Yves Rausch said:

Great post, helped a lot to start into it.

BTW: I just use

EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData=@subscriptionID

to start report execution directly, no need to start the SQL Agent job.

March 5, 2015 8:30 AM

Vishal said:


Could you please advice on how to use your code to enter the email address as a select statement as I want to use the email addresses in the database to send the emails.

Also, do I write the EXEC stored procedure query in the sql server job ?

Thanks in advance.

June 11, 2015 5:56 AM

Greg Low said:

Hi Vishal, the idea is to make the email address be one of the parameters ie: put a placeholder there and replace it when executing. And yes, you can make the job run a proc.

June 15, 2015 5:08 AM

Farhad said:

Hi Greg. Firstly, awesome work!

I need to send about 65 pdf reports to 65 email addresses everyday morning.

For example,

report A will go to

report B will go to

report C will go to

Please let me know what changes I need to make in order to get this done?

Thank you.

November 8, 2015 6:40 PM

Greg Low said:


Am I right in guessing that this isn't really 65 different reports but 65 copies of the same report, based on different parameters? If so, what you need to do is to create a table to hold the parameters and the recipient emails, then use the code above (dbo.ExecuteSubscribedReport) in a loop to just issue each of the required reports.

November 9, 2015 12:14 AM

barney said:

Found this really handy thanks for your hard work

Used it as a base and inspiration to send out invoices where I work.

Used a while loop to hold things up to stop reports going out wrong

And a curser to send repeated jobs

Code below

declare@lastrun datetime

<in the curser loop>

(update and parameters needed using info from curser loop)

EXEC msdb..sp_start_job @job_name = @sched

select @lastrun = (select lastruntime from [sweetcaroline].reportserver.dbo.Subscriptions where subscriptionid = @sub)

while (select lastruntime from

reportserver.dbo.Subscriptions where subscriptionid = <Subscriptionid>)=@lastrun




reset paremters

<end of curser loop>

the final code does pretty much what Farhad needed

November 24, 2015 3:21 AM

Priscilla said:

Hi Greg,

I followed all the steps to create my data driven email subscription. However, whenever I enter the parameter called email In the stored procedure to my email address the report is still using the settings in the subscription. Do you know why it is not adding my email address?  

February 22, 2016 11:22 AM

Greg Low said:

Hard to tell from your comment but I'd suggest just commenting out the line to start the job, running the code, then going into the RS database and looking at that subscription manually, to see what parameters you have (or have not) managed to set. It's basically just doing a text replacement so the fact that it's the email address shouldn't be any different to the other parameters.

February 22, 2016 4:25 PM

Priscilla said:

I figured it out yesterday. Thank you so much for answering me back.

February 23, 2016 8:45 AM

Greg Low said:

Great. But what was wrong?

February 23, 2016 5:34 PM

Marcius said:

This is by far still the best solution that I have been using for years to simulate a data driven subscription without Enterprise. Thanks Greg!

December 20, 2016 11:51 AM

Leave a Comment


This Blog



No tags have been created or used yet.


Privacy Statement