Solved

Kicking off a report on Report Server from SQL Server JOB

Posted on 2010-09-09
11
447 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…

785 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