Solved

Kicking off a report on Report Server from SQL Server JOB

Posted on 2010-09-09
11
454 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:Auerelio Vasquez
[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
  • 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:Auerelio Vasquez
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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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:Auerelio Vasquez
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
 
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:Auerelio Vasquez
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:Auerelio Vasquez
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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