August 3, 2012 at 1:15 pm
Hello,
I am trying to build a dynamic stored procedure to send a report when called. I would like it to render the report in the body of an email exactly how a subscription works. Essentially I want to be able to run a subscription without having to manually build one in SSRS. That way I can call a SPROC with a couple of parameters dynamically to send the report in an email. Any help on this would be greatly aappreciated. The report is on an internal report server and needs to be sent esternally so a URL will not work it should run as it does in subscriptions.
August 6, 2012 at 3:00 am
The only way I can think of off the top of my head if you dont want to use subscriptions, is to build your dynamic proc and format it in a html table actually inside the proc, then use sp_send_dbmail to send it to where it needs to go.
August 6, 2012 at 6:59 am
I've certainly done non-reporting services HTML reports via sprocs and mailed them, is that what you are after?
the trick is to build an html string, and use FOR XML to format the rows.
you want to format header columns as a string,a nd build the details in the query.
here's a full example i collected from a blog someplace and slightly adapted:
Declare @HTMLBody varchar(max),
@TableHead varchar(max),
@TableTail varchar(max)
Set NoCount On;
Set @TableTail = '</table></body></html>';
Set @TableHead = '<html><head>' +
'<style>' +
'td {border: solid black 1px;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:11pt;} ' +
'</style>' +
'</head>' +
'<body><table cellpadding=0 cellspacing=0 border=0>' +
'<tr bgcolor=#FFEFD8><td align=center><b>Server Name</b></td>' +
'<td align=center><b>Product</b></td>' +
'<td align=center><b>Provider</b></td>' +
'<td align=center><b>Data Source</b></td>' +
'<td align=center><b>Is Linked?</b></td></tr>';
Select @HTMLBody = (Select Row_Number() Over(Order By is_linked, name) % 2 As [TRRow],
name As
,
product As
,
provider As
,
data_source As
,
is_linked As
From sys.servers
Order By is_linked, name
For XML raw('tr'), Elements)
-- Replace the entity codes and row numbers
Set @HTMLBody = Replace(@HTMLBody, '_x0020_', space(1))
Set @HTMLBody = Replace(@HTMLBody, '_x003D_', '=')
Set @HTMLBody = Replace(@HTMLBody, '<tr><TRRow>1</TRRow>', '<tr bgcolor=#C6CFFF>')
Set @HTMLBody = Replace(@HTMLBody, '<TRRow>0</TRRow>', '')
Select @HTMLBody = @TableHead + @HTMLBody + @TableTail
-- return output
--Select @HTMLBody
--this assumes a profile was set as "default", so i don't have to explicitly specify which one to use. else you get this error:
/*
Msg 14636, Level 16, State 1, Procedure sp_send_dbmail, Line 112
No global profile is configured. Specify a profile name in the @profile_name parameter.
*/
EXEC msdb.dbo.sp_send_dbmail
--@profile_name='Lowell''s DBMail',
@recipients='lowell@ssomedomain.com',
@subject = 'Simple Email Example Showing linked servers',
@body = @HTMLBody,
@body_format = 'HTML'
--@body_format = 'TEXT'
Lowell
August 6, 2012 at 7:12 am
I wonder why you wouldn't just use a subscription? It sounds like a data driven subscription is mostly what you want. Do you not use Enterprise version?
If the issue is being able to run on demand then make your subscription, disable it if you don't want it to run, then use msdb.dbo.sp_start_job to execute the subscription.
August 6, 2012 at 9:41 am
On the HTML format I use those for other notifications. The report that is built contains a ton of data, runs a lot of custom procs and is client facing so they like the report in a specific format. The reason I am not using subscriptions is we integrate client data into our system every morning each client on their own schedule and would like a step in the loading process to fire off the report. As the report needs to be dynamic to set the company specific report and all clients are on different sources "servers, databases". I would love to see how the subscription is scripted out, as I think I might be able to use that code to do what I am looking for. Does anyone know how to view the subscription code the generates the report? Also thank you all for your responses.
August 6, 2012 at 11:06 am
Report subscription information, and all report information for that matter, is stored in the Reporting Services SQL Server database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply