Solved

Kicking off a report on Report Server from SQL Server JOB

Posted on 2010-09-09
11
440 Views
Last Modified: 2012-08-13
Is there a way, to kick off a report, from the SQL Server Job/Agent ? we are using SQL Server 2008 R2. Or, is there a way from SSIS, to pass a variable to a report, and have the report get kicked off. Also, i want it to kick off the subscriptions, and email to a user group. Is any of this possible? Even if i have to use vb or c#, i would like to be able to do this. Thanks.
0
Comment
Question by:basile
  • 7
  • 4
11 Comments
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33641856
I just had to do this very thing.  What I ended up doing was using a SQL Job to call a batch file.  The batch file called Reporting Services from the commandline, passed in the .rss file, and output the report.

There were a few more steps in order to pass my variable in but this is the meat of the solution.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33641902
Here are the details.

1.  SQL job first step deletes report from yesterday because overwriting them was causing trouble.
2.  Next step takes a template .rss file, reads it in via OpenRowSet, and replaces a placeholder value with my parameter, date in my case.  Then the task outputs the new .rss file.
3.  Next step runs the batch file that calls Reporting Services via commandline:
       rs -i {path to my .rss file} -s {my server name} -e {my credential}
4.  After the report is output I email it to a distribution list with a custom sp.
0
 
LVL 1

Author Comment

by:basile
ID: 33641932
ok. so that can't email from the report server no? what i'm trying to do, is use the report server formatting for HTML format. Just as if you were using a subscription and email to a group of users. What i want to do is use a report that is already created, run a paramater thru it, and email the results out to a group of users.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33641986
I couldn't find a way to pass a parameter or email natively from SSRS, or to schedule it either.  

We used Report Server for the formatting, in PDF.  I also did the emailing directly from the SQL Job with an sp that I use that emails directly via SMTP so you don't have to set up Database Mail.
0
 
LVL 1

Author Comment

by:basile
ID: 33642016
so the script of RS utility is where you did all this? I would love to see an example of the script, i guess you can use SQLCmd from SQL Job or even from a package right? do you have the report already created first? and kick it off from the batch file?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33642048
It's all in a couple places.  There's the scheduled SQL Job that has the tasks I outlined above.  It calls the batch file that runs the RS utility.

Yes, we did have the report created already.

Here's the code in my SQL job that reads in my .rss template and changes my parameter.
Declare @LotsOfText varchar(MAX), @myDate varchar(25)



Set @myDate = GetDate()-1



-- Fix the Main report

Select  @LotsOfText = BulkColumn

From OpenRowSet(Bulk 'c:\{file path}\{file.rss}, SINGLE_BLOB) As x 



Set @myDate = Cast(Month(@myDate) As varchar(2)) + '/' + Cast(Day(@myDate) As varchar(2)) + '/' + Cast(Year(@myDate) As varchar(4))



Set @LotsOfText = Replace(@LotsOfText,'myUniquePlaceholder',@myDate)



Exec master.dbo.spSaveTextToFile @lotsOfText, 'c:\{file path}\{new rss file.rss}',0

Open in new window

0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33642055
Here's the code in the batch file that get's called after this step.
rs -i {path to rss file} -s {report server URL} -e {my credential}

Open in new window

0
 
LVL 17

Accepted Solution

by:
Chris Mangus earned 500 total points
ID: 33642067
Here's what's in my template rss file.
Dim format as string = "PDF"

Dim fileName as String = "{output path and filename of my PDF}"

Dim reportPath as String = "{path to report on report server}"



Public Sub Main()



' Prepare Render arguments

Dim historyID as string = Nothing

Dim deviceInfo as string = Nothing

Dim showHide as string = Nothing

Dim results() as Byte

Dim encoding as string = Nothing

Dim mimeType as string = "application/pdf"

Dim extension as string = "pdf"

Dim warnings() AS Warning = Nothing

Dim reportHistoryParameters() As ParameterValue = Nothing

Dim streamIDs() as string = Nothing



rs.Credentials = System.Net.CredentialCache.DefaultCredentials



Dim parameters(1) As ParameterValue

parameters(0) = New ParameterValue()

parameters(0).Name = "FailureDate"

parameters(0).Value = "myUniquePlaceholder"  <<<< This is the value I search for to replace with a date

parameters(1) = New ParameterValue()

parameters(1).Name = "branch"

parameters(1).Value = "3"

Dim execHeader AS New ExecutionHeader()

Dim rpt AS New ExecutionInfo

rpt = rs.LoadReport(reportPath, Nothing)

rs.SetExecutionParameters(parameters, "en-us")

rs.ExecutionHeaderValue = execHeader

rs.ExecutionHeaderValue.ExecutionID = rpt.ExecutionID

results = rs.Render(format, deviceInfo, extension, mimeType, encoding, warnings, streamIDs)



' Open a file stream and write out the report 

Dim stream As FileStream = File.OpenWrite(fileName)

stream.Write(results, 0, results.Length)

stream.Close()



End Sub

Open in new window

0
 
LVL 1

Author Comment

by:basile
ID: 33642156
thanks so much. I think i found a way..... I'm not sure if you heard this. if you create a subscription, from SSRS, it creates a job on the report server... You can use sp_start_job 'jobname' to kick off that report so i can use my report dynamically like that. i'm gonna check it out. but i've been reading about this rs scripting, so this may be a hybrid solution. thanks again.
0
 
LVL 17

Expert Comment

by:Chris Mangus
ID: 33642282
I was aware of that but couldn't find a way to send a parameter to the report.  I would be interested in that if you find a solution.  Check my profile for contact info.
0
 
LVL 1

Author Comment

by:basile
ID: 33648200
I will definatley let you know if that works out. I think what my plan is, to develop the report, using stored procedures, that will pass the parameters around. My final product will be a report with about 4 sections. when my ETL finishes, it will fire of the sp_start_job. IF it's that simple, i'll send you a line.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now