Creating a PDF from a Stored Procedure in SQL Server

  • Halo Masar Ivica

    thanks for sharing

    • This reply was modified 3 years, 7 months ago by  zacar.
  • very good.

    here is my log

    --DROP PROCEDURE sql2pdf
    create PROCEDURE sql2pdf
    @filename VARCHAR(100)
    AS

    /*
    also maybe run
    exec sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    exec sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE
    exec sp_configure 'Ole Automation Procedures', 1
    GO
    RECONFIGURE
    GO

    before you run ,insert into test data
    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'
    exec sql2pdf 'test1'

    */

    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 = 'd:\\Users\\Administrator\\Documents\\' + @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 ('%\d3\d3\d3\d3')
    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

    • This reply was modified 1 year, 10 months ago by  secowu.
  • thanks very much

    • This reply was modified 1 year, 10 months ago by  secowu.

Viewing 3 posts - 151 through 152 (of 152 total)

You must be logged in to reply to this topic. Login to reply