can we create pdf from an sql query at run time
Question
Tuesday, March 6, 2018 1:46 PM
Hi ,
the below query i am using will create an out put and will store it in an excel in a drive and the db mail will pick up and send it to the people .
my doubt is , i should see that the output is created in pdf at run time and then db mail should pick up and send it , can you please help me to perform the same .
execute as login = 'sa'
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'QIK',
@recipients = 'TP2132@gmail.com',-- @nifi_recipients,
@copy_recipients = 'TP2132@gamil.com',
--@blind_copy_recipients = 'hemadribabu@gmail.com',
@body_format = 'HTML',
@subject = 'test' ,
@body = 'test'
,@attach_query_result_as_file = 1,
@query_result_header = 0,
@query_result_no_padding = 1,
@query = 'set nocount on;
SELECT top 10 employee_code from HRMS40..epin_employee
',
@query_attachment_filename = 'TEST.xls',
@query_result_separator = ' '
hemadri
All replies (5)
Tuesday, March 6, 2018 2:37 PM
There is nothing built into SQL Server which will output a PDF file. You would need to use another library and create the PDF and then attach it to the email.
Wednesday, March 7, 2018 4:29 AM
may i know the type of library to be used please. ?
hemadri
Wednesday, March 7, 2018 7:09 AM
Depends on which Technology / programming language you want to use. In .NET Framework you can use iText library: https://github.com/itext/itextsharp
Olaf Helper
Wednesday, March 7, 2018 1:14 PM
i am using TSQL , its not .NET please.
hemadri
Thursday, March 8, 2018 6:38 AM
It's not possible with plain Transact SQL.
Other option is to use SSRS = "SQL Server Reporting Service": Create a report which represent your data, host it in SSRS and create a subscription to send the report as PDF file via SMTP email to receivers.
Subscriptions and Delivery (Reporting Services)
Export Reports (Report Builder and SSRS)
Olaf Helper