14

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
36

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 '17 at 17:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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