all groups > sql server reporting services > july 2005 >
You're in the

sql server reporting services

group:

Sending reports to a printer without users being involved.


Sending reports to a printer without users being involved. Paul Cyr
7/22/2005 7:56:04 AM
sql server reporting services:
Our billing system runs SQL Agent jobs in the middle of the night and we want
to generate reports that automatically get sent to a printer. I don't care if
they are in PDF, EXCEL, CSV, text or whatever format. I just want them to
print without a user having to click on them.

The reports are all ready for the Finance staff to review when then arrive
in the morning. Our current home grown system does this but we can not more
to reporting services unless we can find a way to do this as the Finance
staff will see this as a step backward.

Do anyone have a process that does this with SQL Reporting Services? Will
RE: Sending reports to a printer without users being involved. NO[at]SPAM
7/22/2005 8:07:03 AM
Implement the Printer Delivery Extension and set up subscriptions to run in
the middle of the night after your other stuff has finished processing, use
the Printer Delivery Extension of as the type of Delivery for the
subscriptions.

[quoted text, click to view]
RE: Sending reports to a printer without users being involved. Harolds
7/22/2005 9:07:06 AM
Have the reports show up in their email, that way you save the paper, and
sell it to the finance department as a step forward.

[quoted text, click to view]
Re: Sending reports to a printer without users being involved. Paul Cyr
7/22/2005 10:12:04 AM
Some are big (20+ pages, especially at month end) but some are just 1 page.

However, this is really a productivity issue. The reports have to be printed
for auditing purposes anyway. So requiring the user to physically press a
button to print them is a step backward.

I am using the following links to try to figure this out. Why is this so
difficult?
Just give me a command line with a -p parameter that I can throw in a batch
file with the location of the report server and the network printer address.
This is crazy complex for non .net developers!!!

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsamples/htm/rss_sampleapps_v1_3t69.asp


[quoted text, click to view]
Re: Sending reports to a printer without users being involved. NO[at]SPAM
7/22/2005 10:43:04 AM
Whats giving you trouble when implementing the Printer Delivery Extension?

I did this just the other day, here is what I did:

1) Compiled the code to get the DLL
2) put the DLL in the ReportServer\bin folder and the ReportManager\bin folder
3) Made the changes to the configuration files per the document you referenced
4) Stop and Restarted the ReportServer Service
5) Reset IIS with IISRESET command

[quoted text, click to view]
Re: Sending reports to a printer without users being involved. NO[at]SPAM
7/22/2005 10:46:03 AM
Paul,

Another option, would be to use the FileShare delivery extension that ships
with RS with a subscription to output the files as say PDF and then use a
utility like "Batch Print & Pro" or something similiar to watch the folder
and print the PDF (or whatever output type you choose) as the files start
showing up. We have a # of clients using RS with a workflow like I just
described.

Nick

[quoted text, click to view]
Re: Sending reports to a printer without users being involved. Bruce L-C [MVP]
7/22/2005 11:18:20 AM
As suggested by someone else, I would think finance would be thrilled to
have an Excel spreadsheet showup automatically. Most finance people live in
Excel and are very comfortable with it. One word of caution, Excel rendering
is slow, how big are these reports?


--
Bruce Loehle-Conger
MVP SQL Server Reporting Services

[quoted text, click to view]

Re: Sending reports to a printer without users being involved. Kerry
7/22/2005 9:13:01 PM
Another solution is to programmatically generate the PDF's store them in a
predefined location then using Adobe Acrobat Reader or the full version print
them using the command line printer function however I think in version 7
they removed that functionality but don't quote me on that I cannot remember
the commandline switch for auto printing but it exist in version 5 for sure.
Alternatively if you want to spend money you can go to
http://www.pdfstore.com/ and buy a PDF printer utility. Or pre generate Excel
files into predefined locations and using the Excel Automation object print
it directly from Excel which is free if you have MSOffice. All of the above
requires .NET or VB6 programming.

[quoted text, click to view]
Re: Sending reports to a printer without users being involved. Paul Cyr
7/26/2005 10:50:03 AM
This is the solution I used (It will work ok for our purposes):

I downloaded PrintPDF.VBS script from www.planetpdf.com.
I modified the original code to work with a network print queue on our
network.
I also made a few more modifications to make it work properly for us.

Thanks for everyones help on this.
------------------------------------------------------------------------------

Dim pdfKey, printCmd, printToCmd, printJob
Dim printerName, printerDriver, printerPort, regPath
Dim fso, fileName, fullPath, readerActive
Dim wmi, wmiq, colProcesses, colPrinters
Dim printJobStarted, loopCounter
Dim arDefaultPrinter

Const TIMEOUT = 60

'-- Initialize variables
printJobStarted = False
loopCounter = 0
strComputer = "."
readerActive = False

'-- Initialize objects
Set objShell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
Set wmi = GetObject("winmgmts:")
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set objArgs = WScript.Arguments

If objArgs.length = 0 Then
WScript.echo("Usage: PrintPDF <PDF File Path> [""Printer Name""]")
WScript.Quit(1)
End If

'-- Get PDF Filename from command line
fileName = objArgs(0)

'-- Get full path to PDF file
fullPath = fso.GetAbsolutePathName(fileName)

'-- Query for active acrobat reader processes
wmiq = "select * from Win32_Process where name='AcroRd32.exe'"
Set colProcesses = wmi.ExecQuery(wmiq)

'-- Set flag if acrobat reader is running
For Each process in colProcesses
readerActive = True
Next

'-- Get the registry key of file association for ".pdf"
pdfKey = objShell.RegRead("HKCR\.pdf\")

'-- Get the print or printto command from the registry
If objArgs.length = 1 Then
'-- Get the default printer
printerName=objShell.RegRead("HKCU\Software\Microsoft\Windows
NT\CurrentVersion\Windows\Device")
arDefaultPrinter=Split(printerName,",")

If IsArray(arDefaultPrinter) Then
printerName=arDefaultPrinter(0)
printerPort=arDefaultPrinter(2)
End If
Else
'-- Get the printer name arg from command line
printerName = objArgs(1)
End If

'-- Get the printto command for .pdf files
printCmd = objShell.RegRead("HKCR\" & pdfKey & "\shell\printto\command\")

'-- Get the remaining args from the registry
regPath = "HKLM\SYSTEM\CurrentControlSet\Control\Print\Printers\" &
printerName
printerDriver = objShell.RegRead(regPath & "\Printer Driver")
printerPort = objShell.RegRead(regPath & "\Port")

'-- Replace printjob argument with the PDF file path
printJob = Replace(printCmd, "%1", fullPath)
'-- Second argument is printer name
printJob = Replace(printJob, "%2", printerName)
'-- Third argument is driver name
printJob = Replace(printJob, "%3", printerDriver)
'-- Fourth argument is port
printJob = Replace(printJob, "%4", printerPort)

'-- Print the PDF file
objShell.Run(printJob)

wmiq = "select * from Win32_Printer where Name ='" & printerName & "'"

Do
'-- Query status of the printer
Set colPrinters = objWMIService.ExecQuery(wmiq)

'-- Check printing status of the printer
For Each objPrinter in colPrinters
Select Case objPrinter.PrinterStatus
Case 1 '-- Status Other
If printJobStarted Then Exit Do
Case 2 '-- Status Unknown
If printJobStarted Then Exit Do
Case 3 '-- Status Idle
If printJobStarted Then Exit Do
Case 4 '-- Status Printing
printJobStarted = True
Case 5 '-- Status Warming Up
End Select
Next

'-- Wait 100 ms
WScript.Sleep 100

'-- Increment our loop counter
loopCounter = loopCounter + 1

'-- Timeout after TIMEOUT seconds
If loopCounter > (TIMEOUT * 10) Then Exit Do
Loop

'-- If acrobat reader wasn't active before printing, kill it
If readerActive = False Then
'-- Query for active acrobat reader processes
wmiq = "select * from Win32_Process where name='AcroRd32.exe'"
Set colProcesses = wmi.ExecQuery(wmiq)

'-- Terminate all acrobat reader processes
For Each process in colProcesses
process.Terminate(1)
Next
End If
-------------------------------------------------------------------------------------

[quoted text, click to view]
AddThis Social Bookmark Button