Solved

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

Posted on 2012-03-27
3
194 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
  • 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

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
CRM 2011 SSRS Question 12 94
SSRS 2008 r2 - finding the selection Screen 3 126
Grid querry results 41 79
SSRS troubles 4 52
Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I'll be showing you how easy it is to add an Else part to the Switch function. T…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

825 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