THE SQL Server Blog Spot on the Web

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

The Bit Bucket (Greg Low): IDisposable

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

Data Driven Subscriptions in SQL Server 2005 Standard Edition

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';

 

 

 

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

Comments

 

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.

Regards,

Greg

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...

Cheers.

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:

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

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.

Regards,

Greg

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.

Regards,

Greg

November 5, 2008 11:27 PM
 

Paula said:

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

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").

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

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:  http://www.sqlservercentral.com/Forums/Topic283642-304-1.aspx

November 2, 2009 4:26 PM
 

Guillaume said:

Hi,

Does your code work with SQL Server 2008 ?

Thanks,

Guillaume

December 15, 2009 9:06 AM
 

Guillaume said:

Yes, that's working :)

Thanks

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;

   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

May 13, 2010 8:55 PM
 

Jimmy George said:

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

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?

Mike

mgisonda@leespring.com

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

|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

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.

Regards,

Greg

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!!

Thanks

Megha

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.

Regards,

Greg

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?

Thanks,

Pete

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)

Regards,

Greg

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" (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

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.

Regards,

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

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.

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.  : )

Thanks,

Brian

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 = '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

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:  

(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

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

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Tags

No tags have been created or used yet.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement