Chris Rose, our former DBA and a friend mentioned yesterday the need of having to use SQL server reporting services subscription emails “WITHOUT Outlook and Exchange”.
He suggested and I concur, there would be two ways to do it.
1. Configuring a Report Server for E-Mail Delivery
Use the RSReportServer Configuration File and edit the SMTP portion of it.
<RSEmailDPConfiguration>
<SMTPServer>mySMTPServer.Adventure-Works.com</SMTPServer>
<SMTPServerPort></SMTPServerPort>
<SMTPAccountName></SMTPAccountName>
<SMTPConnectionTimeout></SMTPConnectionTimeout>
<SMTPServerPickupDirectory></SMTPServerPickupDirectory>
<SMTPUseSSL></SMTPUseSSL>
<SendUsing>2</SendUsing>
<SMTPAuthenticate></SMTPAuthenticate>
<From>my-rs-email-account@Adventure-Works.com</From>
<EmbeddedRenderFormats>
<.RenderingExtension>MHTML</RenderingExtension>
</EmbeddedRenderFormats>
<PrivilegedUserRenderFormats></PrivilegedUserRenderFormats>
<ExcludedRenderFormats>
<RenderingExtension>HTMLOWC</RenderingExtension>
<RenderingExtension>NULL</RenderingExtension>
</ExcludedRenderFormats>
<SendEmailToUserAlias>True</SendEmailToUserAlias>
<DefaultHostName></DefaultHostName>
<PermittedHosts>
<HostName>Adventure-Works.com</HostName>
<HostName>hotmail.com</HostName>
</PermittedHosts>
</RSEmailDPConfiguration>
The MSDN Article discusses both local and remote SMTP service usage options.
2 xp_smtp_sendmail
This would be an awkward work around but for some reason, if you can't use the reporting services SMTP configs (one scenario I can think of is local exchange being blocked from sending any outbound emails i.e. no relaying outside the active directory and you don't want to open it up just for reporting). Therefore, since you don't have the luxury of having a custom report config for every report, it can have subscription to generate target file (PDF, XLS, HTM, XML etc) on file system. Let the SQL server handle it via xp_smtp_sendmail or a bare bones app to send out an email. The other SMTP relay can be controlled and locked down. My two cents. If you know of other ways, please contribute.
For further reading on Microsoft's SQL server reporting services, my article:
Business Intelligence with Microsoft SQL Server Reporting Services
Food for thought: Having an nBIRT Project - Business Intelligence and Reporting Tools; a similar open source approach as Eclipse's BIRT (no this is not plagiarism, its inspiration!)
SQL Server Reporting Services Forums
Anything and everything pertaining to SQL Server Reporting Services.
