?
Solved

Kicking off a report on Report Server from SQL Server JOB

Posted on 2010-09-09
11
Medium Priority
?
457 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

777 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