HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMajor

Is there an elegant way to send query results directly to a physical printer?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
querywayprinterelegantdirectlyphysicalresultstheresend

Problem

I've got this weird request to have a report be scheduled and printed to a printer.

The report itself is pretty simple, and will fit on a single page. I can print it to a txt and that will be just fine (although I'm open to suggestions on a better product).

Here's the code. It works. But I don't like it. Specifically, Id like to do away with the nested @bcp and @SQL.

Question: Is there a more elegant way to do this?

declare @filepath varchar(255),
@filename varchar(255),
@filetype varchar(255),
@sql nvarchar(max),
@coverpage_text nvarchar(max)

set @filepath = 'C:\users\jmay\documents\'
SET @filename = 'TestFile'
set @filetype = '.txt'

--output to txt
set @sql = N'declare @bcp varchar(4000)
set @bcp = ''bcp " select * from test_data " queryout ' 
+ @filepath +  @filename + @filetype + ' -t " - " -c -T -d DBA''
print @bcp

EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
 exec sp_executesql @sql

--print data
 set @sql = N'declare @bcp varchar(4000)
set @bcp = ''START /MIN NOTEPAD /P ' + @filepath +  @filename +  @filetype + '''
print @bcp

EXECUTE master.dbo.xp_cmdshell @BCP'

print @sql
exec sp_executesql @sql

Solution

Execute the query using POWERSHELL and OUT-PRINTER

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance .   | Out-Printer



The Out-Printer cmdlet sends output to the default printer or to an
alternate printer, if one is specified.

Code Snippets

Invoke-Sqlcmd -Query "SELECT GETDATE() AS TimeOfQuery;" -ServerInstance .   | Out-Printer

Context

StackExchange Database Administrators Q#193638, answer score: 36

Revisions (0)

No revisions yet.