Creating a PDF from a Stored Procedure in SQL Server
-
I was wondering what would need to be done to allow this procedure to open up a PDF template (which has a background image) and then write the necessary text to the file and ultimately then save the new PDF as the filename passed into the procedure.
Any help with this would be greatly appreciated.
Thanks,
Jason
-
New procedure 🙂
-
Excellent articel. Pl. give some documentation about your procedure...
-
I made some changes to the SQL2PDF script to make it work a little better in our environment. This script should handle the order of the text in the pdf file better and we were experiencing issues with multi-page pdfs, some would be fine while others appeared to have mini-pages (junk pages) for all pages past page one. This updated script fixed this problem for us. The updated script is below. Hope it's helpful to someone.
------------------------------------------------------------------------------------------
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
ALTER PROCEDURE spSQL2PDF_2
@filename VARCHAR(200),
@font_size VARCHAR(3)
AS
CREATE TABLE #pdf (idnumber INT IDENTITY(1,1)
,code NVARCHAR(400))
CREATE TABLE #xref (idnumber INT IDENTITY(1,1)
,code VARCHAR(40))
CREATE TABLE #text (idnumber INT IDENTITY(1,1)
,code VARCHAR(400))
--New Temp Table, 3/22/2005 - BDL
CREATE TABLE #pageobjects (idnumber INT IDENTITY(1,1)
,code VARCHAR(10))
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(800),
@pdf VARCHAR(200),
@trenutniRed NVARCHAR(300),
@rows INT,
@ofset INT,
@len INT,
@nopg INT,
@FS INT,
@ole INT,
@x INT,
@file INT,
@object INT
SELECT @pdf = @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 statement below won't allow Order by clause
--did inserts through a cursor to circumvent
-- INSERT INTO #text(code) (SELECT LEFT(code, 200) FROM tblReportPDF)
--Accomplished Inserts in Order through a cursor, 3/22/2005 - BDL
DECLARE @TextCode char(200)
DECLARE TextCursor CURSOR
FOR SELECT LEFT(code, 200) FROM tblReportPDF ORDER BY report_id
OPEN TextCursor
FETCH NEXT FROM TextCursor INTO @TextCode
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #text(code) VALUES(@TextCode)
FETCH NEXT FROM TextCursor INTO @TextCode
END
CLOSE TextCursor
DEALLOCATE TextCursor
SELECT @x = COUNT(*) FROM #text
SELECT @x = (@x / 60) + 1
WHILE @nopg < @x
BEGIN
DECLARE SysKursor INSENSITIVE SCROLL CURSOR
--SET THE SUBSTRING LENGTH
FOR SELECT SUBSTRING((code + SPACE(81)), 1, 300) 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 = ' ' + CAST(@object AS VARCHAR) + @ad
--Inserts each page into new temp table.
--Thus accumulating all indirect references to page objects, 3/22/2005 - BDL
INSERT INTO #pageobjects(code) VALUES(@page)
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')
-- SET TEXT FONT AND SIZE
INSERT INTO #pdf (code) VALUES ('/F1 ' + @font_size + ' Tf')
INSERT INTO #pdf (code) VALUES ('1 0 0 1 30 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 ]')
--Replaced indirect reference of only the last page object
--with indirect references of all the page objects, 3/22/2005 - BDL
-- INSERT INTO #pdf (code) VALUES ('/Kids [' + @page + ' ]')
INSERT INTO #pdf (code) VALUES ('/Kids [')
INSERT INTO #pdf (code) SELECT code FROM #pageobjects ORDER BY idnumber
INSERT INTO #pdf (code) VALUES (']')
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
TRUNCATE TABLE tblReportPDF
EXECUTE @ole = sp_OADestroy @file
EXECUTE @ole = sp_OADestroy @FS
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
I think it is a good and working solution.
I shall be reccommending this solution to my clients. they always like something good for nothing.
-
This is the most amazing and cost-effective way shown in the article for creating PDF from sql. It's a beautiful article, I really loved it.
-
Hi,
Let me give everybody a tip here. LaTeX is this fantastic mark up language that produces camera ready PDF, fully bookmarked, fully annotated directly. Works like a charm.
Steve
-
Great procedure but some work could be done to make it clearer what you are doing. Also, where are the specs for PDF's? That would be handy.
Scott.
-
Yes, it would be helpful to understand all the variables and sections, so that it could be adapted to local use. Thanks very much, though. It works as promised, but I'm not sure how to edit it for use.
David Thayer
-
Im getting this error when I run the command sql2pdf 'filename'
OLE Automation objects are not supported in fiber mode.
any clues
-
I love it!!! Thanks for taking the time to create and share this code.
-
You have set your SQL Server to run using lightweight NT fibres rather than threads. Somewhere on the MS site there is a discussion about the pros and cons - you should run in fibre mode only in very few cases - the default for SQL Server and the mode that is best suited to most things on SQL is to run it in threaded mode. When you run in fibre mode, things like OLE automation (which gets involved in threads, apartments, etc - fibres don't tend to mix with this) just don't work if you have fibre mode on.
Run Enterprise manager, bring up the properties of your server and turn off fibre mode in one of the property pages.
Hope that helps
-
Ian,
Thanks for the information. I did look "use Windows NT fibers" setting up in the server and it was turned off.
Does SQL server 7 has anything to it ?
-
Hmmm... Try a google search with the imporart part of the error string - surround it with quotes in Google and see what you come up with. I've read stuff about errors like that before - trust Google
-
very good SPs. Thanks. But how can we export selected data to a paper with different size (e.g. Legal) and orientation (e.g. Landscape)?
Viewing 15 posts - 46 through 60 (of 152 total)
You must be logged in to reply to this topic. Login to reply