How to send PDF as an attachment from TSQL

Question

0.00/5 (No votes)

See more:

Hi,

I've the following requirement.

I want to send the Output of a Stored Proc as a PDF and send it on mail.

What is the best possible way to do that. When using SP_SEND_DBMAIL it sends PDF but that is corrupted.

Can i use dll for this..If yes how can this particular scenario be achieved.

Posted 12-Jun-12 20:08pm

Mannu Sh

Add a Solution

Comments

Farhan Ghumra 15-Jun-12 7:07am

I don't think that there would be any 3rd party dll, try to use SP_SEND_DBMAIL with other PDFs

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shanalal Kasim · Answer 1 · 2012-10-29T01:57:00

To Create PDF from SQL stored procedure

SQL2PDF makes a PDF report from text inserted in the table psopdf (nvarchar(80)). First a table named psopdf should be created.

SQL

CREATE TABLE psopdf (code NVARCHAR(80)) 
After that create the below stored procedure SQL2PDF. 
-- DROP PROCEDURE SQL2PDF 
CREATE PROCEDURE SQL2PDF 
@filename VARCHAR(100) 
AS 
CREATE TABLE #pdf (idnumber INT IDENTITY(1,1) 
,code NVARCHAR(200)) 
CREATE TABLE #xref (idnumber INT IDENTITY(1,1) 
,code VARCHAR(30)) 
CREATE TABLE #text (idnumber INT IDENTITY(1,1) 
,code VARCHAR(200)) 
DECLARE @end VARCHAR(7), 
@beg VARCHAR(7), 
@a1 VARCHAR(3), 
@a2 VARCHAR(3), 
@ad VARCHAR(5), 
@cr VARCHAR(8), 
@pr VARCHAR(9), 
@ti VARCHAR(6), 
@xstr VARCHAR(10), 
@page VARCHAR(8000), 
@pdf VARCHAR(100), 
@trenutniRed NVARCHAR(200), 
@rows INT, 
@ofset INT, 
@len INT, 
@nopg INT, 
@fs INT, 
@ole INT, 
@x INT, 
@file INT, 
@object INT 
SELECT @pdf = 'C:\' + @filename + '.pdf' 
SET @page = '' 
SET @nopg = 0 
SET @object = 6 
SET @end = 'endobj' 
SET @beg = ' 0 obj' 
SET @a1 = '<<' 
SET @a2 = '>>' 
SET @ad = ' 0 R' 
SET @cr = CHAR(67) + CHAR(114) + CHAR (101) + CHAR(97) + CHAR(116) + CHAR (111) + CHAR(114) 
SET @pr = CHAR(80) + CHAR(114) + CHAR (111) + CHAR(100) + CHAR(117) + CHAR (99 ) + CHAR(101) + CHAR(114) 
SET @ti = CHAR(84) + CHAR(105) + CHAR (116) + CHAR(108) + CHAR(101) 
SET @xstr = ' 00000 n' 
SET @ofset = 396 
INSERT INTO #xref(code) VALUES ('xref') 
INSERT INTO #xref(code) VALUES ('0 10') 
INSERT INTO #xref(code) VALUES ('0000000000 65535 f') 
INSERT INTO #xref(code) VALUES ('0000000017' + @xstr) 
INSERT INTO #xref(code) VALUES ('0000000790' + @xstr) 
INSERT INTO #xref(code) VALUES ('0000000869' + @xstr) 
INSERT INTO #xref(code) VALUES ('0000000144' + @xstr) 
INSERT INTO #xref(code) VALUES ('0000000247' + @xstr) 
INSERT INTO #xref(code) VALUES ('0000000321' + @xstr) 
INSERT INTO #xref(code) VALUES ('0000000396' + @xstr) 
INSERT INTO #pdf (code) VALUES ('%' + CHAR(80) + CHAR(68) + CHAR (70) + '-1.2') 
INSERT INTO #pdf (code) VALUES ('%ÓÓÓÓ') 
INSERT INTO #pdf (code) VALUES ('1' + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES ('/' + @cr + ' (Ivica Masar ' + CHAR(80) + CHAR(83) + CHAR (79) + CHAR(80) + CHAR(68) + CHAR (70) + ')') 
INSERT INTO #pdf (code) VALUES ('/' + @pr + ' (stored procedure for ms sql pso@vip.hr)') 
INSERT INTO #pdf (code) VALUES ('/' + @ti + ' (SQL2' + CHAR(80) + CHAR(68) + CHAR (70) + ')') 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
INSERT INTO #pdf (code) VALUES ('4' + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES ('/Type /Font') 
INSERT INTO #pdf (code) VALUES ('/Subtype /Type1') 
INSERT INTO #pdf (code) VALUES ('/Name /F1') 
INSERT INTO #pdf (code) VALUES ('/Encoding 5' + @ad) 
INSERT INTO #pdf (code) VALUES ('/BaseFont /Courier') 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
INSERT INTO #pdf (code) VALUES ('5' + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES ('/Type /Encoding') 
INSERT INTO #pdf (code) VALUES ('/BaseEncoding /WinAnsiEncoding') 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
INSERT INTO #pdf (code) VALUES ('6' + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES (' /Font ' + @a1 + ' /F1 4' + @ad + ' ' + @a2 + ' /ProcSet [ /' + CHAR(80) + CHAR(68) + CHAR (70) + ' /Text ]') 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
INSERT INTO #text(code) (SELECT code FROM psopdf) 
SELECT @x = COUNT(*) FROM #text 
SELECT @x = (@x / 60) + 1 
WHILE @nopg < @x 
BEGIN 
DECLARE SysKursor INSENSITIVE SCROLL CURSOR 
FOR SELECT SUBSTRING((code + SPACE(81)), 1, 80) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 ) 
FOR READ ONLY 
OPEN SysKursor 
FETCH NEXT FROM SysKursor INTO @trenutniRed 
SELECT @object = @object + 1 
SELECT @page = @page + ' ' + CAST(@object AS VARCHAR) + @ad 
SELECT @len = LEN(@object) + LEN(@object + 1) 
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES ('/Type /Page') 
INSERT INTO #pdf (code) VALUES ('/Parent 3' + @ad) 
INSERT INTO #pdf (code) VALUES ('/Resources 6' + @ad) 
SELECT @object = @object + 1 
INSERT INTO #pdf (code) VALUES ('/Contents ' + CAST(@object AS VARCHAR) + @ad) 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
SELECT @ofset = @len + 86 + @ofset 
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) 
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
SELECT @object = @object + 1 
INSERT INTO #pdf (code) VALUES ('/Length ' + CAST(@object AS VARCHAR) + @ad) 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES ('stream') 
INSERT INTO #pdf (code) VALUES ('BT') 
INSERT INTO #pdf (code) VALUES ('/F1 10 Tf') 
INSERT INTO #pdf (code) VALUES ('1 0 0 1 50 802 Tm') 
INSERT INTO #pdf (code) VALUES ('12 TL') 
WHILE @@Fetch_Status = 0 
BEGIN 
INSERT INTO #pdf (code) VALUES ('T* (' + @trenutniRed + ') Tj') 
FETCH NEXT FROM SysKursor INTO @trenutniRed 
END 
INSERT INTO #pdf (code) VALUES ('ET') 
INSERT INTO #pdf (code) VALUES ('endstream') 
INSERT INTO #pdf (code) VALUES (@end) 
SELECT @rows = (SELECT COUNT(*) FROM #text WHERE idnumber BETWEEN ((@nopg * 60) + 1) AND ((@nopg + 1) * 60 ))* 90 + 45 
SELECT @nopg = @nopg + 1 
SELECT @len = LEN(@object) + LEN(@object - 1) 
SELECT @ofset = @len + 57 + @ofset + @rows 
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) 
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR) + @beg) 
INSERT INTO #pdf (code) VALUES (@rows) 
INSERT INTO #pdf (code) VALUES (@end) 
SELECT @len = LEN(@object) + LEN(@rows) 
SELECT @ofset = @len + 18 + @ofset 
INSERT INTO #xref(code) (SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) 
CLOSE SysKursor 
DEALLOCATE SysKursor 
END 
INSERT INTO #pdf (code) VALUES ('2' + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES ('/Type /Catalog') 
INSERT INTO #pdf (code) VALUES ('/Pages 3' + @ad) 
INSERT INTO #pdf (code) VALUES ('/PageLayout /OneColumn') 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
UPDATE #xref SET code = (SELECT code FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref)) WHERE idnumber = 5 
DELETE FROM #xref WHERE idnumber = (SELECT MAX(idnumber) FROM #xref) 
INSERT INTO #pdf (code) VALUES ('3' + @beg) 
INSERT INTO #pdf (code) VALUES (@a1) 
INSERT INTO #pdf (code) VALUES ('/Type /Pages') 
INSERT INTO #pdf (code) VALUES ('/Count ' + CAST(@nopg AS VARCHAR)) 
INSERT INTO #pdf (code) VALUES ('/MediaBox [ 0 0 595 842 ]') 
INSERT INTO #pdf (code) VALUES ('/Kids [' + @page + ' ]') 
INSERT INTO #pdf (code) VALUES (@a2) 
INSERT INTO #pdf (code) VALUES (@end) 
SELECT @ofset = @ofset + 79 
UPDATE #xref SET code =(SELECT SUBSTRING('0000000000' + CAST(@ofset AS VARCHAR), 
LEN('0000000000' + CAST(@ofset AS VARCHAR)) - 9, 
LEN('0000000000' + CAST(@ofset AS VARCHAR))) + @xstr) WHERE idnumber = 6 
INSERT INTO #xref(code) VALUES ('trailer') 
INSERT INTO #xref(code) VALUES (@a1) 
SELECT @object = @object + 1 
UPDATE #xref SET code = '0 ' + CAST(@object AS VARCHAR) WHERE idnumber = 2 
INSERT INTO #xref(code) VALUES ('/Size ' + CAST(@object AS VARCHAR)) 
INSERT INTO #xref(code) VALUES ('/Root 2' + @ad) 
INSERT INTO #xref(code) VALUES ('/Info 1' + @ad) 
INSERT INTO #xref(code) VALUES (@a2) 
INSERT INTO #xref(code) VALUES ('startxref') 
SELECT @len = LEN(@nopg) + LEN(@page) 
SELECT @ofset = @len + 86 + @ofset 
INSERT INTO #xref(code) VALUES (@ofset) 
INSERT INTO #xref(code) VALUES ('%%' + CHAR(69) + CHAR (79) + CHAR(70)) 
INSERT INTO #pdf (code) (SELECT code FROM #xref) 
--SELECT code FROM #pdf 
SELECT @trenutniRed = 'del '+ @pdf 
EXECUTE @ole = sp_OACreate 'Scripting.FileSystemObject', @fs OUT 
EXEC master..xp_cmdshell @trenutniRed, NO_OUTPUT 
EXECUTE @ole = sp_OAMethod @fs, 'OpenTextFile', @file OUT, @pdf, 8, 1 
DECLARE SysKursor INSENSITIVE SCROLL CURSOR 
FOR SELECT code FROM #pdf ORDER BY idnumber 
FOR READ ONLY 
OPEN SysKursor 
FETCH NEXT FROM SysKursor INTO @trenutniRed 
WHILE @@Fetch_Status = 0 
BEGIN 
EXECUTE @ole = sp_OAMethod @file, 'WriteLine', Null, @trenutniRed 
FETCH NEXT FROM SysKursor INTO @trenutniRed 
END 
CLOSE SysKursor 
DEALLOCATE SysKursor 
DELETE FROM psopdf 
EXECUTE @ole = sp_OADestroy @file 
EXECUTE @ole = sp_OADestroy @fs

And table psopdf has to be filled with your data as shown in examples below. At the end the stored procedure is called using the file name only (not extension). 
EXEC sql2pdf 'fileName' 
The result is in your C:\ directory.

EXAMPLE 1:

SQL

INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY LTD' 
INSERT psopdf(code) SELECT SPACE(60) + 'COMPANY ADDRESS' 
INSERT psopdf(code) SELECT SPACE(60) + 'STREET NAME & No' 
INSERT psopdf(code) SELECT ' ' 
INSERT psopdf(code) SELECT SPACE(34) + 'BILL OF SALE' 
INSERT psopdf(code) SELECT ' ' 
INSERT psopdf(code) SELECT 'Product' + SPACE(10) + 'Quantity' 
+ SPACE(10) + 'Price' + SPACE(10) + 'Total' 
INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_') 
INSERT psopdf(code) SELECT 'Product1' + SPACE(9) + '10.00 ' 
+ SPACE(10) + '52.30' + SPACE(10) + '5230.0' 
INSERT psopdf(code) SELECT 'Product2' + SPACE(9) + '2.00 ' 
+ SPACE(10) + '10.00' + SPACE(10) + ' 20.0' 
INSERT psopdf(code) SELECT REPLACE(SPACE(56), ' ', '_') 
INSERT psopdf(code) SELECT SPACE(50) + '5250.0'

After INSERT call the stored procedure with file name demo2. 
EXEC sql2pdf 'demo2' 
The result is in your C:\ directory.