Click here to Skip to main content
15,501,747 members
Please Sign up or sign in to vote.
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
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

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. 
 
Share this answer
 
Comments
Nelek 29-Oct-12 8:01am    
?????
Shanalal Kasim 29-Oct-12 8:11am    
what?

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900