Print SSRS Report / PDF automatically from SQL Server agent or Windows Service

Posted by Jeremy Ramos on Geeks with Blogs See other posts from Geeks with Blogs or by Jeremy Ramos
Published on Tue, 22 Oct 2013 04:55:40 GMT Indexed on 2013/10/22 15:55 UTC
Read the original article Hit count: 657

Originally posted on: http://geekswithblogs.net/JeremyRamos/archive/2013/10/22/print-ssrs-report--pdf-from-sql-server-agent-or.aspx

I have turned the Web upside-down to find a solution to this considering the least components and least maintenance as possible to achieve automated printing of an SSRS report. This is for the reason that we do not have a full software development team to maintain an app and we have to minimize the support overhead for the support team.

Here is my setup:

  • SQL Server 2008 R2 in Windows Server 2008 R2
  • PDF format reports generated by SSRS Reports subscriptions to a Windows File Share
  • Network printer
  • Coloured reports with logo and branding

I have found and tested the following solutions to no avail:

ProsCons
Calling Adobe Acrobat Reader exe:
"C:\Program Files (x86)\Adobe\Reader 11.0\Reader\acroRd32.exe" /n /s /o /h /t "C:\temp\print.pdf" \\printserver\printername"
Very simple optionAdobe Acrobat reader requires to launch the GUI to send a job to a printer. Hence, this option cannot be used when printing from a service.
Calling Adobe Acrobat Reader exe as a process from a .NET console appA bit harder than above, but still a simple solutionSame as cons above

Powershell script
(Start-Process -FilePath "C:\temp\print.pdf" -Verb Print)

Very simple optionUses default PDF client in quiet mode to Print, but also requires an active session.   
Foxit ReaderVery simple optionRequires GUI same as Adobe Acrobat Reader
Using the Reporting Services Web service to run and stream the report to an image object and then passed to the printerQuite complexThis is what we're trying to avoid

 

After pulling my hair out for two days, testing and evaluating the above solutions, I ended up learning more about printers (more than ever in my entire life) and how printer drivers work with PostScripts. I then bumped on to a PostScript interpreter called GhostScript (http://www.ghostscript.com/) and then the solution starts to get clearer and clearer.

I managed to achieve a solution (maybe not be the simplest but efficient enough to achieve the least-maintenance-least-components goal) in 3-simple steps:

  1. Install GhostScript (http://www.ghostscript.com/download/)
    - this is an open-source PostScript and PDF interpreter. Printing directly using GhostScript only produces grayscale prints using the laserjet generic driver unless you save as BMP image and then interpret the colours using the image
  2. Install GSView (http://pages.cs.wisc.edu/~ghost/gsview/)
    - this is a GhostScript add-on to make it easier to directly print to a Windows printer. GSPrint automates the above  PDF -> BMP -> Printer Driver.
  3. Run the GSPrint command from SQL Server agent or Windows Service:
    "C:\Program Files\Ghostgum\gsview\gsprint.exe" -color -landscape -all -printer "printername" "C:\temp\print.pdf"
    Command line options are here: http://pages.cs.wisc.edu/~ghost/gsview/gsprint.htm

Another lesson learned is, since you are calling the script from the Service Account, it will not necessarily have the Printer mapped in its Windows profile (if it even has one). The workaround to this is by adding a local printer as you normally would and then map this printer to the network printer. Note that you may need to install the Printer Driver locally in the server.

So, that's it! There are many ways to achieve a solution. The key thing is how you provide the smartest solution!

© Geeks with Blogs or respective owner

Related posts about SSRS printing

Related posts about PDF print