Automating report execution - saving to separate files

I'm trying to do something which I hope can be accomplished relatively simply.

I have a report similar to bank statements let's say. When run, it currently prints out each person's statement into one file, with page breaks sepearating each person's statement. What I need to do, is when the report is run, save each person's report into a seperate file for the purpose of emailing to them later.

I could easily modify my report to just output for one particular person, but I'm not sure if there's a way to "bulk render" all the reports and have them saved to sepearate files.

I should also add that I'm using an MS Access Data Project (ADP) as the front end to my app - connected to a SQL Server 2005 DB. I currently display the reports by embedding a web browser object into an Access form and rendering the report via HTML.

Thanks in advance,

H

I actually figured out an answer to my own question which I'll post here in case others need to do the same thing. If anyone else has an alternative suggestion please feel free to let me know.

There's a VBA function called "URLDownloadToFile". If I use the reporting services URL access and specify type XLS or PDF, I can pass this as the URL and save the file to a specific folder. Kind of nice actually - and very simple.

Automatic PDF/TIFF generation and saving?

Ello folks...
Question for you all - I'd like to make a call from a webpage (asp or aspx)
that will call up a report with a given set of parameters, format it as a pdf
or tiff file, and save it to a location. Any ideas?
I know I can use the /ReportServer/ instance to directly call the report,
pass it the parameters I need, and render it out as a different format, but
it's the saving part that's throwing me.
Thanks!
- TerosTeros,
How about a subscription instead, or does it have to be on request?
A subscription can do this for you, but rendering on demand will always
ask.
Sorry, I don't think it can be done.
Chris
Teros wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or
> aspx) that will call up a report with a given set of parameters,
> format it as a pdf or tiff file, and save it to a location. Any
> ideas?
> I know I can use the ReportServer instance to directly call the
> report, pass it the parameters I need, and render it out as a
> different format, but it's the saving part that's throwing me.
> Thanks!
> - Teros|||I have seen someone say you can save the output stream as a file . I looked
for a previous post but didn;t find it.
The other way is to create a data-based subscription... in the subscription
row you can choose the filename and path...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Teros" <Teros@.discussions.microsoft.com> wrote in message
news:1BCA0EE4-E9B9-4BE9-8F35-55A0EB09FD96@.microsoft.com...
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or
> aspx)
> that will call up a report with a given set of parameters, format it as a
> pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format,
> but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Check out the web method - RenderStream - you can render to a stream then
save the stream to a file
"Teros" wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or aspx)
> that will call up a report with a given set of parameters, format it as a pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format, but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Teros,
AFAIK there is no way to do this from URL access. We do what you're doing
by calling the web service and saving the stream off to a file.
Ted
"Teros" wrote:
> Ello folks...
> Question for you all - I'd like to make a call from a webpage (asp or aspx)
> that will call up a report with a given set of parameters, format it as a pdf
> or tiff file, and save it to a location. Any ideas?
> I know I can use the /ReportServer/ instance to directly call the report,
> pass it the parameters I need, and render it out as a different format, but
> it's the saving part that's throwing me.
> Thanks!
> - Teros|||Thanks for the input - the report does need to be rendered on demand, so the
subscriptions wouldn't work. I'm playing with the renderstream now to see
what I can play with. It's looking pretty hopeful thus far.
Thanks!|||For PDF or TIFF files, you use the Render() method. RenderStream is used for
things like images on an HTML page.
Ted
"Mary Bray [SQL Server MVP]" wrote:
> Check out the web method - RenderStream - you can render to a stream then
> save the stream to a file
> "Teros" wrote:
> > Ello folks...
> >
> > Question for you all - I'd like to make a call from a webpage (asp or aspx)
> > that will call up a report with a given set of parameters, format it as a pdf
> > or tiff file, and save it to a location. Any ideas?
> >
> > I know I can use the /ReportServer/ instance to directly call the report,
> > pass it the parameters I need, and render it out as a different format, but
> > it's the saving part that's throwing me.
> >
> > Thanks!
> > - Teros|||Any luck Teros? I'm trying to do the same thing (I think). We have a report
that will be called from a third party application, and when it is called we
want it to create a pdf to a specific location, and then close. I'd be very
interested in knowing how you did it.
Michael C.
"Teros" wrote:
> Thanks for the input - the report does need to be rendered on demand, so the
> subscriptions wouldn't work. I'm playing with the renderstream now to see
> what I can play with. It's looking pretty hopeful thus far.
> Thanks!
>

automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!

automated replication scripting

We create replication scripts on a regular basis, saving them to a warm standby server as a precaution. Is there a way to automate (i.e. in a job) the scripting of replication? Right-clicking in EM is becoming tedious with the number of servers we have (80+ of them SQL Servers). My manager does not want to go through just restoring msdb on the standby; he says there are issues with that. I am new enough to replication to just go with his guidance, which is why we are taking this approach.

You can take a look at RMO programming. For example, Publication object has a method Script() that can be used to create a script (see http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.replication.publication.script.aspx). Other replication objects, such as DistributionDatabase, MergePublication, has similar methods.|||At a glance, this appears to apply to SQL Server 2005. I cannot find documentation for SQL Server 2000.|||

For SQL 2000, you can use SQL DMO. Check out SQL 2000 BOL and find topic "Script Method (Replication Objects)".

Peng

|||

Peng, that was it! Sweet as pie, it worked beautifully using a VBScript in a DTS. Something I have never done, but always wanted to. I have always known that SQLDMO would eventually become a tool I needed and you've put me onto it. Thank you!