Solved

Kicking off a report on Report Server from SQL Server JOB

Posted on 2010-09-09
11
448 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to connect Power BI Desktop to ssrs report server 2 47
TSQL - How to declare table name 26 41
union query column need default text 2 14
get_systemdrive info from tsql? 1 18
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 article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

789 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