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

1 Answer 1


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.

  • 4
    It worked like magic, the same code can shorten down to: Invoke-Sqlcmd -Query "SELECT * from dba..test_data;" | Out-Printer I scheduled it as a job and bam. thanks for your help!
    – James
    Dec 20, 2017 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.