Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-03-27
3
Medium Priority
?
212 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 2000 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
This code started out as a fix for a customer that had incoming data that was hunderds of numbers and words long that was to fit in one column. The problem was that the customer did not want to split words or numbers when wrapping in the column. …
Loops Section Overview
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

578 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