Data-Driven Subscriptions for SSRS Reports with SQL 2008 Standard Edition

I have a need to create a scheduled report via email (2x weekly).  This report would be received by a dynamic list of individuals that would be published in a SQL table.  Ideally, I would do this in Report Manager and use data driven subscriptions.  However, I am on SQL Server 2008 Standard Edition which doesn't enable this functionality.  Does anyone have a recommendation, or a script that may offer similar functionality?
goodmanroAsked:
Who is Participating?
 
SThayaTechnical MAnagerCommented:
Hi,

  please refer the below link

http://msdn.microsoft.com/en-us/library/ms169673.aspx

OR

Try to use the below XQuery


Steps:

1. Deploy the RDL in the reportserver
2.  create a subscription with the To and CC .
3.  create a sechdule for only one time (it create a Subcription id in the SQL job)
4. create SP with the below code .
5. sechdule a SQL job

Code :


create PROCEDURE [dbo].[gsp_EmailXQUERY]  
--@FileNAme Varchar(75)    
AS    
 

BEGIN

--To DECLARE the Local Variable

DECLARE @PARAMETERVALUES XML,@VDate VARCHAR(250),@FILENAME VARCHAR(MAX),
@RDLPath VARCHAR(MAX),@ItemID VARCHAR(250),@SbscrptnID VARCHAR(250),@newSubject VARCHAR(MAX)

--To Get the RDL path from catalog table from ReportServer

SET @RDLPath ='/ Reports/SAB/SutdataEmail'

 
select  @newSubject =  'Report:SiteData'


 
--Exportfile with Extn
SET @FILENAME=@newSubject

--To Get the ItemID from catalog table from ReportServer

SELECT @ItemID =  [ItemID] FROM ReportServer.dbo.[Catalog]
WHERE PATH = @RDLPath

--To Get the SubscriptionID with the help of ItemID from catalog table

SELECT  @SbscrptnID = Sch.[SubscriptionID]    
FROM   ReportServer.dbo.[ReportSchedule] Sch WITH (NOLOCK)
WHERE [ReportID] = @ItemID

--To SET Extension SETtings for the particular ID

SELECT @PARAMETERVALUES = ExtensionSETtings
FROM ReportServer.dbo.[Subscriptions]
WHERE  [DeliveryExtension] = 'Report Server Email'
and SubscriptionID =@SbscrptnID

--Use X-Query to replace the existng values
 
SET @PARAMETERVALUES.modify('replace value of (/ParameterValues/ParameterValue/Value/text())[5] with sql:variable("@FILENAME")')

--Update the New File Name in [Subscriptions] table

UPDATE ReportServer.dbo.[Subscriptions]
SET ExtensionSETtings =CAST(CONVERT(XML,@PARAMETERVALUES,1) as NVARCHAR(MAX))
WHERE  [DeliveryExtension] = 'Report Server Email'
and SubscriptionID =@SbscrptnID

--To Fire-up and Event based on the subscription ID
 
EXEC REPORTSERVER.DBO.ADDEVENT @EventType='TimedSubscription',@EVENTDATA=@SbscrptnID


END
0
 
TempDBACommented:
You can create a .net application that make use of the rdl and send mail to the list of users through db_mail using the list you have in your table stored.
0
 
goodmanroAuthor Commented:
Using SQL Server Enterprise version is the easiest option.  SThaya offers a nice work-around for SQL Server Standard above.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.