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

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.
rtmc_jnealAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nico BontenbalCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rtmc_jnealAuthor Commented:
We have standard version.  Do you have an example of the vbscript?
0
Nico BontenbalCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SSRS

From novice to tech pro — start learning today.