Recently, I was working at a client's site where SQL Server 2000 was still in use. They wanted to upgrade to SQL Server 2005. The only reason they were looking at the Enterprise Edition was for data-driven subscriptions in Reporting Services. The CEO certainly wasn't interested in paying the sort of dollars required for that, just to be able to automatically email out a set of monthly reports to about 30 or so folk within the company.
A quick bit of research led me to Jason L. Selburg's article at Code Project on how he achieved this by modifying the subscription details in the ReportServer database. You'll find his original post here: http://www.codeproject.com/KB/database/DataDrivenSubscriptions.aspx
I wanted to tackle a more complete version than Jason provided. There were a couple of key limitations:
1. You'd have to modify the stored procedure every time your parameter list changed. (and potentially have different stored procedures for different reports).
2. He used commands for manipulating the ntext columns that were deprecated. I wanted to avoid these.
3. The code didn't deal with concurrent usage.
Below, you'll find an alternate version of the proc. The concept is still the same. You create a subscription to a report, using tokens for parameters, set the subscription to happen in the past and then execute it using this proc in a scheduled Agent job. The different approach I've taken is:
1. I've provided the ability to deal with a parameter list rather than a single parameter.
2. I found that the ntext columns actually only contained simple xml. While I could have used XQuery to modify it, it's easier to directly manipulate it as varchar data (could have used nvarchar), given all we're doing is replacement of tokens with values. Either way, this avoids the use of UPDATETEXT etc.
3. I've used more of my style of naming conventions..
4. I've used a global temporary table as a flag to limit concurrent use. (Without this, the scheme could fail).
Hope you find it useful.
/*
ExecuteSubscribedReport
PROCEDURE DESCRIPTION:
Creates the effect of a data driven subscription by replacing the fields in
an existing subscription with the supplied values, executing the report
and then replacing the original values.
INPUT:
@ScheduleID The Job Name in SQL Server
@EmailTo The TO address of the email
@EmailCC The Carbon Copy address of the email
@EmailBCC The Blind Copy address of the email
@EmailReplyTo The Reply TO address of the email
@EmailBody Any text that you want in the email body
@ParameterList The parameters for the report in the format 'Parameter1Token,Parameter1Value,Parameter2Token,Parameter2Value…'
Example: '|StartDate|,20071231,|Salesperson|,GE,|Region|,NW'
OUTPUT:
None
WRITTEN BY:
Greg Low based on a concept from Jason L. Selburg at CodeProject.com
LIMITATIONS:
ParameterTokens and ParameterValues are limited to 1000 characters
EmailBody is limited to 8000 characters
ParameterList is limited to 8000 characters total
*/
CREATE PROCEDURE dbo.ExecuteSubscribedReport
( @ScheduleID uniqueidentifier,
@EmailTo varchar (1000) = NULL,
@EmailCC varchar (1000) = NULL,
@EmailBCC varchar (1000) = NULL,
@EmailReplyTo varchar (1000) = NULL,
@EmailBody varchar (8000) = NULL,
@ParameterList varchar (8000) = NULL
)
AS BEGIN
DECLARE @extensionSettingsPointer binary(16),
@parametersPointer binary(16),
@tokenPosition int,
@tokenLength int,
@subscriptionID uniqueidentifier,
@parameterToken varchar(1000),
@parameterValue varchar(1000),
@parameterPosition int,
@numberOfParameters int,
@parameterCounter int,
@character varchar(1),
@parseStatus varchar(1), — 0 ready for another token, 1 in a token, 2 in a value
@originalExtensionSettings varchar(8000),
@originalParameters varchar(8000),
@newExtensionSettings varchar(8000),
@newParameters varchar(8000);
DECLARE @parameters TABLE (ParameterID int IDENTITY(1,1),
ParameterToken varchar(1000),
ParameterValue varchar(1000));
— first we need to unpack the parameter list
IF @ParameterList IS NOT NULL BEGIN
SET @parameterPosition = 1;
SET @parseStatus = 0;
SET @parameterToken = ";
SET @parameterValue = ";
SET @numberOfParameters = 0;
WHILE @parameterPosition <= LEN(@ParameterList) BEGIN
SET @character = SUBSTRING(@ParameterList,@parameterPosition,1);
IF @character = ',' BEGIN
IF @parseStatus = 0 BEGIN — we had two commas in a row or the first character was a comma
PRINT 'ParameterList has incorrect format';
RETURN 1;
END
ELSE IF @parseStatus = 1 BEGIN — we are at the end of the token
SET @parseStatus = 2;
SET @parameterValue = ";
END
ELSE BEGIN — we are at the end of a value
INSERT @parameters (ParameterToken,ParameterValue)
VALUES (@ParameterToken,@ParameterValue);
SET @numberOfParameters = @numberOfParameters + 1;
SET @parseStatus = 0;
SET @parameterToken = ";
END;
END ELSE BEGIN
IF @parseStatus = 0 BEGIN — we have the first character of a token
SET @parseStatus = 1;
SET @parameterToken = @parameterToken + @character;
END
ELSE IF @parseStatus = 1 BEGIN — we have another character in a token
SET @parameterToken = @parameterToken + @character;
END
ELSE BEGIN — we have another character in a value
SET @parameterValue = @parameterValue + @character;
END;
END;
SET @parameterPosition = @parameterPosition + 1;
END;
IF @parseStatus = 2 BEGIN— we were still collecting a value
INSERT @parameters (ParameterToken,ParameterValue)
VALUES (@ParameterToken,@ParameterValue);
SET @numberOfParameters = @numberOfParameters + 1;
END;
END;
— we need to wait for our turn at using the subscription system
WHILE EXISTS(SELECT 1 FROM tempdb.sys.objects WHERE name = '##ReportInUse')
WAITFOR DELAY '00:00:30';
CREATE TABLE ##ReportInUse (ReportID int);
— once we have the parameters unpacked, we now need to find the subscriptionID
SELECT @subscriptionID = SubscriptionID
FROM dbo.ReportSchedule
WHERE ScheduleID = @ScheduleID;
— next we save away the original values of ExtensionSettings and Parameters
— (we use them to make it easy put the values back later)
— they are actually xml but it'll be easier to work with them as strings
SELECT @originalExtensionSettings = CAST(ExtensionSettings AS varchar(8000)),
@originalParameters = CAST(Parameters AS varchar(8000))
FROM dbo.Subscriptions
WHERE SubscriptionID = @subscriptionID;
SET @newExtensionSettings = @originalExtensionSettings;
SET @newParameters = @originalParameters;
— if they have supplied arguments ie: not NULL and not blank, process them
IF COALESCE(@EmailTo,") <> "
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|TO|',@EmailTo);
IF COALESCE(@EmailCC,") <> "
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|CC|',@EmailCC);
IF COALESCE(@EmailBCC,") <> "
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BC|',@EmailBC);
IF COALESCE(@EmailReplyTo,") <> "
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|RT|',@EmailReplyTo);
IF COALESCE(@EmailBody,") <> "
SET @newExtensionSettings = REPLACE(@newExtensionSettings,'|BD|',@EmailBody);
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 @newParameters = REPLACE(@newParameters,@ParameterToken,@ParameterValue);
SET @parameterCounter = @parameterCounter + 1;
END;
END;
— Temporarily update the values
UPDATE dbo.Subscriptions
SET ExtensionSettings = CAST(@newExtensionSettings AS ntext),
Parameters = CAST(@newParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
— run the job
EXEC msdb..sp_start_job @job_name = @ScheduleID
— make enough delay for the report to have started
WAITFOR DELAY '00:00:30'
— put the original extensionsettings and parameter values back
UPDATE dbo.Subscriptions
SET ExtensionSettings = CAST(@originalExtensionSettings AS ntext),
Parameters = CAST(@originalParameters AS ntext)
WHERE SubscriptionID = @subscriptionID;
— finally we free up the subscription system for another person to use
DROP TABLE ##ReportInUse;
END;
GO
EXEC dbo.ExecuteSubscribedReport
@ScheduleID = '4CE38C83-6A03-4780-895A-92FD6F8FD5B0',
@EmailTo = 'greg@sqldownunder.com',
@EmailCC = 'ozinfo@sqldownunder.com',
@EmailBCC = 'info@sqldownunder.com',
@EmailReplyTo = 'greg@sqldownunder.com',
@EmailBody = 'Hello Greg',
@ParameterList = '|StartDate|,20071231,|Salesperson|,GE,|Region|,NW';
wouldn't an application Lock be easier than the global temp table ?
Nicely done, bookmarked for future usage
Hi Stephen,
Yep, they would have been another option. At first I was targeting SQL Server 2000 as well though.
Regards,
Greg
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…
Cheers.
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.
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.
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!
Hello,
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.
Regards,
Jeremy
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.
Regards,
Greg
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?
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.
Regards,
Greg
Greg,
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?
thanks!
Paula
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").
Basically,
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)
AS
BEGIN
DECLARE
@NPOS int,
@NPOSSTARTVALUE int,
@NPOSENDVALUE int,
@NLENGTH 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)
SELECT @NLENGTH = @NPOSENDVALUE – @NPOSSTARTVALUE – 6 – 1
RETURN LEFT(@ParameterString, @NPOS + @NPOSSTARTVALUE + 5)
+ @Value
+ RIGHT(@ParameterString, LEN(@ParameterString) – (@NPOS + @NPOSSTARTVALUE + @NLENGTH) – 5)
END
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: http://www.sqlservercentral.com/Forums/Topic283642-304-1.aspx
Hi,
Does your code work with SQL Server 2008 ?
Thanks,
Guillaume
Yes, that's working 🙂
Thanks
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 <, > 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);
(…)
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;
END;
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.
Cheers
Hi,
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 !
Jimmy
Thanks very much, Greg and Jason.
It works perfectly!!!!!!!!!
A great help.
Hi Greg,
I would like to pass multivalue parameters.
Is this possible?
Thx in advance.
Does anyone have a solution for sending the report to a file share as well ?
Thanks a lot.
For some reason the parameter is not picked from the variable when executing the sp. Can anyone help?
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?
Mike
mgisonda@leespring.com
Subscriptions can write out to file shares. No difference with this type of subscription.
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
|fld1|,1,2,3,|next_param….
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?
thanks
tonyzoc@gmail.com
Hi Tony,
That would work. An alternative would be to use XML for the parameters, and then split them out of there.
Regards,
Greg
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!!
Thanks
Megha
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.
Regards,
Greg
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?
Thanks,
Pete
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)
Regards,
Greg
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" (http://www.sqlservercentral.com/articles/Reporting+Services+(SSRS)/69546/) 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!
Pete
Very cool thanks for this! Have modified it for a SharePoint delivery subscription and works a charm!
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!
Hi Eugene,
That sounds like a reasonable plan.
Glad to hear that it helped.
Regards,
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
GE_NewReport.
EXEC dbo.ExecuteSubscribedReport
@ScheduleID = '4CE38C83-6A03-4780-895A-92FD6F8FD5B0',
@EmailTo = 'glow@solidq.com',
@EmailCC = 'ozinfo@solidq.com',
@EmailBCC = 'info@solidq.com',
@EmailReplyTo = 'glow@solidq.com',
@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.
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.
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. : )
Thanks,
Brian
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.
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.
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?
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.
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.
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 = 'eric.faivre@dmycompany.com',
@EmailCC = 'eric.faivre@mycompany.com',
@EmailBCC = 'eric.faivre@mycompany.com',
@EmailReplyTo = 'eric.faivre@mycompany.com',
@EmailBody = ",
@ParameterList = '|user|,3428CD9C-78FB-E211-B8D1-00155D6CD610';
Thank you for your help
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.
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?
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.
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 ?
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.
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?
No but sounds very much a sharepoint issue rather than a sql server one.
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:
(REPLACE(@newParameters,@ParameterToken,@ParameterValue);)
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.
Brian
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.
I need to send report with dynamic title in email subject (SSRS 2008). Any help?
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.
Greg,
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.
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.
Greg,
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?
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.
It is great work, lovely. a genius work
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?
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
DECLARE @iDoc INT
EXEC sp_xml_preparedocument @idoc OUTPUT, @OriginalParameters
DECLARE @OParamTable TABLE
(
[id] INT,
[parentid] INT,
[nodetype] INT,
[localname] VARCHAR(500),
[prefix] VARCHAR(500),
[namespaceuri] VARCHAR(500),
[datatype] VARCHAR(500),
[prev] VARCHAR(500),
[text] VARCHAR(500)
)
INSERT INTO @OParamTable
( id ,
parentid ,
nodetype ,
localname ,
prefix ,
namespaceuri ,
datatype ,
prev ,
text
)
SELECT *
FROM OPENXML (@idoc, 'ParameterValues',1)
DECLARE @ValueTextIDTable TABLE
(
[Record] INT IDENTITY(1,1),
[Value_id] INT
)
INSERT INTO @ValueTextIDTable( Value_id )
SELECT id FROM @OParamTable WHERE localname = 'Value'
DECLARE @ValueText VARCHAR(100)
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;
END;
END;
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.
Hi,
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.
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.
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 A@abc.com
report B will go to B@abc.com
report C will go to C@abc.com
Please let me know what changes I need to make in order to get this done?
Thank you.
Thanks.
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.
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
begin
WAITFOR DELAY '00:00:1'
end
reset paremters
<end of curser loop>
the final code does pretty much what Farhad needed
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?
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.
I figured it out yesterday. Thank you so much for answering me back.
Hi Priscilla,
I know this is over a year old but we are stumped by the same problem you solved (i.e. the parameters aren't being replaced and the email is being sent with the settings in the subscription). If you get this, could you please let us know what the solution was?
Thanks in advance for any help you can provide.
Cindy
I'll try to take a look at it as soon as I have time. I've been meaning to create an updated version, that includes all the ideas that people have posted in the comments.
Great. But what was wrong?
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!