This article explains how to create a a stored procedure that will in turn create a simple column based report in PDF without using any external tools or libraries (and their associated licensing costs!)
SQLPDF makes a PDF report from text inserted in the table psopdf ( nvarchar() ) First a table named psopdf should be created
CREATE TABLE psopdf (code NVARCHAR())
After that create the stored procedure SQLPDF
SQLPDFTXT
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 sqlpdf fileName
The result is in your C:\ directory
EXAMPLE :
INSERT psopdf(code) SELECT SPACE() + COMPANY LTD
INSERT psopdf(code) SELECT SPACE() + COMPANY ADDRESS
INSERT psopdf(code) SELECT SPACE() + STREET NAME & No
INSERT psopdf(code) SELECT
INSERT psopdf(code) SELECT SPACE() + BILL OF SALE
INSERT psopdf(code) SELECT
INSERT psopdf(code) SELECT Product + SPACE() + Quantity
+ SPACE() + Price + SPACE() + Total
INSERT psopdf(code) SELECT REPLACE(SPACE() _)
INSERT psopdf(code) SELECT Product + SPACE() +
+ SPACE() + + SPACE() +
INSERT psopdf(code) SELECT Product + SPACE() +
+ SPACE() + + SPACE() +
INSERT psopdf(code) SELECT REPLACE(SPACE() _)
INSERT psopdf(code) SELECT SPACE() +
After INSERT call the stored procedure with file name demo
EXEC sqlpdf demo
The result is in your C:\ directory
EXAMPLE :
Second example uses a database pubs
USE pubs
INSERT psopdf(code) SELECT tau_lname + + tau_fname + + tphone
+
+ taddress + + tcity + + tstate + + tzip FROM
authors t authors t
After INSERT call the stored procedure with file name demo
EXEC sqlpdf demo
>The result is in your C:\ directory
educitycn/img_///gif>sqlpdftxt
DROP PROCEDURE sqlpdf
CREATE PROCEDURE sqlpdf
@filename VARCHAR()
AS
CREATE TABLE #pdf (idnumber INT IDENTITY()
code NVARCHAR())
CREATE TABLE #xref (idnumber INT IDENTITY()
code VARCHAR())
CREATE TABLE #text (idnumber INT IDENTITY()
code VARCHAR())
DECLARE @end VARCHAR()
@beg VARCHAR()
@aVARCHAR()
@aVARCHAR()
@adVARCHAR()
@crVARCHAR()
@prVARCHAR()
@tiVARCHAR()
@xstrVARCHAR()
@pageVARCHAR()
@pdf VARCHAR()
@trenutniRed NVARCHAR()
@rows INT
@ofsetINT
@lenINT
@nopg INT
@fs INT
@oleINT
@x INT
@file INT
@object INT
SELECT @pdf = C:\ + @filename + pdf
SET @page =
SET @nopg =
SET @object =
SET @end = endobj
SET @beg = obj
SET @a = <<'
SET @a2 = '>>
SET @ad = R
SET @cr = CHAR() + CHAR() + CHAR () + CHAR() + CHAR() + CHAR () + CHAR()
SET @pr = CHAR() + CHAR() + CHAR () + CHAR() + CHAR() + CHAR ( ) + CHAR() + CHAR()
SET @ti = CHAR() + CHAR() + CHAR () + CHAR() + CHAR()
SET @xstr = n
SET @ofset =
INSERT INTO #xref(code) VALUES (xref)
INSERT INTO #xref(code) VALUES ( )
INSERT INTO #xref(code) VALUES ( f)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #xref(code) VALUES ( + @xstr)
INSERT INTO #pdf (code) VALUES (% + CHAR() + CHAR() + CHAR () + )
INSERT INTO #pdf (code) VALUES (%佑佑)
INSERT INTO #pdf (code) VALUES ( + @beg)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (/ + @cr + (Ivica Masar + CHAR() + CHAR() + CHAR () + CHAR() + CHAR() + CHAR () + ))
INSERT INTO #pdf (code) VALUES (/ + @pr + (stored procedure for ms sqlpso@viphr))
INSERT INTO #pdf (code) VALUES (/ + @ti + (SQL + CHAR() + CHAR() + CHAR () + ))
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ( + @beg)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (/Type /Font)
INSERT INTO #pdf (code) VALUES (/Subtype /Type)
INSERT INTO #pdf (code) VALUES (/Name /F)
INSERT INTO #pdf (code) VALUES (/Encoding + @ad)
INSERT INTO #pdf (code) VALUES (/BaseFont /Courier)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ( + @beg)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (/Type /Encoding)
INSERT INTO #pdf (code) VALUES (/BaseEncoding /WinAnsiEncoding)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #pdf (code) VALUES ( + @beg)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (/Font + @a + /F + @ad + + @a + /ProcSet [ / + CHAR() + CHAR() + CHAR () + /Text ])
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (@end)
INSERT INTO #text(code) (SELECT code FROM psopdf)
SELECT @x = COUNT(*) FROM #text
SELECT @x = (@x / ) +
WHILE@nopg < @x
BEGIN
DECLARE SysKursorINSENSITIVE SCROLL CURSOR
FOR SELECT SUBSTRING((code + SPACE()) ) FROM #text WHERE idnumber BETWEEN ((@nopg * ) + ) AND ((@nopg + ) * )
FOR READ ONLY
OPEN SysKursor
FETCH NEXT FROM SysKursor INTO @trenutniRed
SELECT @object = @object +
SELECT @page = @page + + CAST(@object AS VARCHAR) + @ad
SELECT @len = LEN(@object) + LEN(@object + )
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR)+ @beg)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (/Type /Page)
INSERT INTO #pdf (code) VALUES (/Parent + @ad)
INSERT INTO #pdf (code) VALUES (/Resources + @ad)
SELECT @object = @object +
INSERT INTO #pdf (code) VALUES (/Contents + CAST(@object AS VARCHAR) + @ad)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (@end)
SELECT @ofset = @len + + @ofset
INSERT INTO #xref(code) (SELECT SUBSTRING( + CAST(@ofset AS VARCHAR)
LEN( + CAST(@ofset AS VARCHAR))
LEN( + CAST(@ofset AS VARCHAR))) + @xstr)
INSERT INTO #pdf (code) VALUES (CAST(@object AS VARCHAR)+ @beg)
INSERT INTO #pdf (code) VALUES (@a)
SELECT @object = @object +
INSERT INTO #pdf (code) VALUES (/Length + CAST(@object AS VARCHAR) + @ad)
INSERT INTO #pdf (code) VALUES (@a)
INSERT INTO #pdf (code) VALUES (stream)
INSERT INTO #pdf (code) VALUES (BT)
INSERT INTO #pdf (code) VALUES (/F