Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SSRS EMail Data-Driven Subscriptions.. File Name need to be changed dynamically. Expand / Collapse
Author
Message
Posted Thursday, July 17, 2014 3:11 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 4:45 PM
Points: 74, Visits: 395

We have the option of changing File Name dynamically in "Windows File Share" delivery method in Data-Driven Subscriptions but is it possible in E-Mail delivery method?
Post #1593853
Posted Friday, August 1, 2014 3:07 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 6:39 AM
Points: 11, Visits: 109
Yes :)

1 create manualy subscription and run it
2 create job whit command eg:

-- This procedure get data from table [name and email adres] then update subscription and run subscription, wait 60 secends and get next value from table

BEGIN
DECLARE @wysylka varchar(max)
DECLARE @oddzial varchar(200)
declare @sql nvarchar(max)
declare @email nvarchar(500)
DECLARE wysylka_crs cursor for SELECT [Branch],[Adres_email]
FROM [RPT].[Emails]


OPEN wysylka_crs
Set @oddzial = ' '
WHILE( @@FETCH_STATUS = 0)
begin
if @oddzial <>' '
begin

UPDATE REPORTSERVER.DBO.SUBSCRIPTIONS
SET ExtensionSettings = '<ParameterValues><ParameterValue><Name>TO</Name><Value>'+@email+'</Value></ParameterValue><ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue><ParameterValue><Name>RenderFormat</Name><Value>MHTML</Value></ParameterValue><ParameterValue><Name>Subject</Name><Value>@ReportName dla oddzialu: '+@oddzial+' wygenerowany: @ExecutionTime </Value></ParameterValue><ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue><ParameterValue><Name>Priority</Name><Value>NORMAL</Value></ParameterValue></ParameterValues>'
WHERE SUBSCRIPTIONID = '8597C181-AE56-4ED7-B6E3-E9939B858C6E'
EXEC ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData= '8597C181-AE56-4ED7-B6E3-E9939B858C6E'
WAITFOR DELAY '00:01:00'

end
FETCH NEXT FROM wysylka_crs INTO @oddzial,@email
end
CLOSE wysylka_crs
DEALLOCATE wysylka_crs
END
Post #1598622
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse