Solved

Scheduling a report to be emailed using dynamic parameters in SQL 2005

Posted on 2012-03-27
3
196 Views
Last Modified: 2012-05-22
Hello,

I have a report this is based/grouped on project.  I need to send each project to a different email recipient.  The relationship between the project and recipient is stored in the DB.
0
Comment
Question by:rtmc_jneal
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 22

Accepted Solution

by:
Nico Bontenbal earned 500 total points
ID: 37777267
http://msdn.microsoft.com/en-us/library/ms159150(SQL.90).aspx
But this feature is only available in Evaluation, Developer, and Enterprise.
You could also create a vbscript (for example) that downloads and mails the reports for you, and skip the report server scheduler.
0
 

Author Comment

by:rtmc_jneal
ID: 37777274
We have standard version.  Do you have an example of the vbscript?
0
 
LVL 22

Expert Comment

by:Nico Bontenbal
ID: 37780548
This piece of code will save the output of a http request to a file.
Sub ExportReport(strReport, strFile)
	dim  xml, oStream
	Set xml = CreateObject("Microsoft.XMLHTTP")
	xml.Open "GET", strReport, False
	xml.Send
	set oStream = createobject("Adodb.Stream")
	Const adTypeBinary = 1
	Const adSaveCreateOverWrite = 2
	Const adSaveCreateNotExist = 1
	oStream.type = adTypeBinary
	oStream.open
	oStream.write xml.responseBody
	' Do not overwrite an existing file
	oStream.savetofile strFile , adSaveCreateOverWrite
	' Use this form to overwrite a file if it already exists
	' oStream.savetofile DestFolder & ImageFile, adSaveCreateOverWrite
	oStream.close
	set oStream = nothing
	Set xml = Nothing
end sub

Open in new window

The url looks something like this:
http://Server/ReportServer/Pages/ReportViewer.aspx?/Reportfolder/Reportname&rs:Format=EXCEL&rs:Command=Render

Open in new window

See here for more information:
http://msdn.microsoft.com/en-us/library/ms154042(SQL.90).aspx
Test the url in the browser first. If it returns the right data, put it in the vbscript. To mail a file from vbScript just google for the answer.

If you got those two working you can send a report from a vbscript. Now you need to get the parameters in the URL as well so you can create a report for one project. See:
http://msdn.microsoft.com/en-us/library/ms155391(SQL.90).aspx
If you got this working you need to connect to the SQL database from the vbscript and loop through the table with the project/email data. For each row download the report (with the project as parameter) and send it to the right email address.

Be prepared to invest some serious time in this. It is not an easy task.
0

Featured Post

Turn Insights Into Action

You’ve already invested in ITSM tools, chat applications, automation utilities, and more. Fortify these solutions with intelligent communications so you can drive business processes forward.

With xMatters, you'll never miss a beat.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question