Password protection on subscription-based Excel reports

I know RS doesn't offer this now.
I'm trying to figure out a workaround.
I need to:
-run a report on a schedule
-render it to excel
-password protect it
-email it
Subscriptions would be fine if it weren't for the password issue.
I've got DotNet code create the reprot and render it as an Excel
spreadsheet.
I've got code to password-protect the spreadsheet.
I want code to control the above code - to run at a certain time and
then deliver it via email. But it doesn't look like the Subscriptions
object will do this. Subscriptions appears to create the report and
deliver it, but not allow intervention for something like password
protection.My suggestion is that you use a script outside of Reporting Services to send
the mail. You can use CDO or .NET web mail. YOu can then schedule this using
Windows scheduler.
Try using the following code in a VB Console Application (you need to add a
reference of System.Web.dll to your project):
Imports System.Web.Mail
Module Module1
Dim oMail As MailMessage = New MailMessage
Sub Main()
oMail.From = "user@.mydomain.com"
oMail.To = "administrator@.classroom.com"
oMail.Subject = "Sending Using Web Mail"
oMail.BodyFormat = MailFormat.Text
oMail.Body = "Password protected file."
Dim myfile As String = "c:\Myfile.xls"
Dim oAttachment As MailAttachment = New MailAttachment(myfile)
oMail.Attachments.Add(oAttachment)
SmtpMail.SmtpServer = "10.1.1.200"
SmtpMail.Send(oMail)
oMail = Nothing
oAttachment = Nothing
End Sub
End Module
HTH
Charles Kangai, MCT, MCDBA
"Jay" wrote:
> I know RS doesn't offer this now.
> I'm trying to figure out a workaround.
> I need to:
> -run a report on a schedule
> -render it to excel
> -password protect it
> -email it
> Subscriptions would be fine if it weren't for the password issue.
> I've got DotNet code create the reprot and render it as an Excel
> spreadsheet.
> I've got code to password-protect the spreadsheet.
> I want code to control the above code - to run at a certain time and
> then deliver it via email. But it doesn't look like the Subscriptions
> object will do this. Subscriptions appears to create the report and
> deliver it, but not allow intervention for something like password
> protection.
>|||The best solution here is to create a custom delivery extension.
--
Peter Blackburn
Windows Server Systems - SQL Server MVP
Hitchhiker's Guide to SQL Server 2000 Reporting Services
http://www.sqlreportingservices.net
"Jay" <jaytaylor_it@.hotmail.com> wrote in message
news:a737af3c.0411291503.60317abc@.posting.google.com...
>I know RS doesn't offer this now.
> I'm trying to figure out a workaround.
> I need to:
> -run a report on a schedule
> -render it to excel
> -password protect it
> -email it
> Subscriptions would be fine if it weren't for the password issue.
> I've got DotNet code create the reprot and render it as an Excel
> spreadsheet.
> I've got code to password-protect the spreadsheet.
> I want code to control the above code - to run at a certain time and
> then deliver it via email. But it doesn't look like the Subscriptions
> object will do this. Subscriptions appears to create the report and
> deliver it, but not allow intervention for something like password
> protection.|||Thanks. This looks like the way to go. I think I"ll have to go outside RS. I
don't think a delivery extension can handle this.
"Charles Kangai" wrote:
> My suggestion is that you use a script outside of Reporting Services to send
> the mail. You can use CDO or .NET web mail. YOu can then schedule this using
> Windows scheduler.
> Try using the following code in a VB Console Application (you need to add a
> reference of System.Web.dll to your project):
> Imports System.Web.Mail
> Module Module1
> Dim oMail As MailMessage = New MailMessage
> Sub Main()
> oMail.From = "user@.mydomain.com"
> oMail.To = "administrator@.classroom.com"
> oMail.Subject = "Sending Using Web Mail"
> oMail.BodyFormat = MailFormat.Text
> oMail.Body = "Password protected file."
> Dim myfile As String = "c:\Myfile.xls"
> Dim oAttachment As MailAttachment = New MailAttachment(myfile)
> oMail.Attachments.Add(oAttachment)
> SmtpMail.SmtpServer = "10.1.1.200"
> SmtpMail.Send(oMail)
> oMail = Nothing
> oAttachment = Nothing
> End Sub
> End Module
>
> HTH
> Charles Kangai, MCT, MCDBA
>
> "Jay" wrote:
> > I know RS doesn't offer this now.
> >
> > I'm trying to figure out a workaround.
> >
> > I need to:
> >
> > -run a report on a schedule
> > -render it to excel
> > -password protect it
> > -email it
> >
> > Subscriptions would be fine if it weren't for the password issue.
> >
> > I've got DotNet code create the reprot and render it as an Excel
> > spreadsheet.
> > I've got code to password-protect the spreadsheet.
> >
> > I want code to control the above code - to run at a certain time and
> > then deliver it via email. But it doesn't look like the Subscriptions
> > object will do this. Subscriptions appears to create the report and
> > deliver it, but not allow intervention for something like password
> > protection.
> >|||Thanks. I don't get it, though - it looks like whatever custom delivery
extension I'd create, I'd still need to use RS's CreateSubscription method.
How woud I be able to insert password protection into the process when the
subscription kicks off?
"Peter Blackburn (www.sqlreportingservice" wrote:
> The best solution here is to create a custom delivery extension.
> --
> Peter Blackburn
> Windows Server Systems - SQL Server MVP
> Hitchhiker's Guide to SQL Server 2000 Reporting Services
> http://www.sqlreportingservices.net
>
> "Jay" <jaytaylor_it@.hotmail.com> wrote in message
> news:a737af3c.0411291503.60317abc@.posting.google.com...
> >I know RS doesn't offer this now.
> >
> > I'm trying to figure out a workaround.
> >
> > I need to:
> >
> > -run a report on a schedule
> > -render it to excel
> > -password protect it
> > -email it
> >
> > Subscriptions would be fine if it weren't for the password issue.
> >
> > I've got DotNet code create the reprot and render it as an Excel
> > spreadsheet.
> > I've got code to password-protect the spreadsheet.
> >
> > I want code to control the above code - to run at a certain time and
> > then deliver it via email. But it doesn't look like the Subscriptions
> > object will do this. Subscriptions appears to create the report and
> > deliver it, but not allow intervention for something like password
> > protection.
>
>