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: 810
SSRS printing
|PDF print
|Automated printing
|SQL Server agent Print
|Windows Service print
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:
| Pros | Cons | |
| 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 option | Adobe 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 app | A bit harder than above, but still a simple solution | Same as cons above |
Powershell script | Very simple option | Uses default PDF client in quiet mode to Print, but also requires an active session. |
| Foxit Reader | Very simple option | Requires 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 printer | Quite complex | This 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:
- 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 - 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. - 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
