Post reply

Creating a PDF from a Stored Procedure in SQL Server

  • August 27, 2004 at 3:07 am

    #520827

    Very cool for simple listing-type reports. Bye-bye to expensive reporting engines...

  • August 27, 2004 at 12:17 pm

    #520938

    Excellent work.

    Would be good to see this developed further.

    As a suggestion, the inclusion of some error handling to fail gracefully when using the OLE automation calls would not go amiss.

    Declare @hr int,

            @src varchar(255),

            @desc varchar(255)

    If @ole <> 0

      Begin

        Exec sp_OAGetErrorInfo @object, @src OUT, @desc OUT

        Select ole=convert(varbinary(4),@ole), Source=@src, <A href="mailtoescription=@desc">Description=@desc

        Return

      End

  • August 27, 2004 at 2:42 pm

    #520959

    Rather interesting stored procedure.  However, the pdf document I ended up with gave me an error when i tried to open it with acrobat 5.0...said it was damaged and could not be repaired. 

    Is the resulting pdf document supposed to be all readable text?  I'm still looking at it.  very interesting.

  • August 29, 2004 at 6:24 am

    #520990

    But it can't support Chinese word !!

    Like ???,? .....

    Who can solve this problem?

  • September 15, 2004 at 3:59 am

    #523121

    Very Nice. Made a few adjustments for my environment. I'm not comfortable with the cmdexec, so removed that and also replaced the 'OpenTextFile' call with the following:

    EXECUTE @ole = sp_OAMethod @FS, 'CreateTextFile', @file OUT, @pdf, -1, 0

    This still creates the same Unicode file as the original 'OpenTextFile' method, but has the option set to overwrite an existing file, which is what the cmdexec was doing with the "del" command.

    Also, rather than build @pdf from 'c:\' + @filename + '.pdf', it suited my purposes better to replace @filename in the function call with @pdf, and get rid of the DECLARE @pdf and the build statement mentioned. This allows me to pass in a UNC path (including the full constructed file name with .pdf) to the function and have the file created on another location. This obviously requires the executing account (possibly and/or the SQL Service account, I didn't research it) to have network access to the remote share.

    I incorporated your bug fixes regarding the sorting and line length variable length adjustments you mentioned earlier. I plan on making additional adjustments so a new stored procedure can build a temp table and populate it with the report data, then either pass the temp table to this procedure as a table variable, or create it as a global session temp table and pass the temp table name to the pdf procedure and use that instead of the query to "psopdf", allowing multiple processes to invoke the function without worrying about report table data concurrent usage.

    This has given me lots of ideas and solves a problem that was on the drawing board for a few weeks out; very timely for me (although the problem I was working on tonight that was on the drawing board for tomorrow got pushed out a little ). Thanks for a great procedure!

    Thanks,

    Brett Hacker

  • September 29, 2004 at 11:09 pm

    #524859

    Thanks for this SPROC! It has assisted in solving an issue for us with combining SQL data stored records and generating archived invoices in our clients specialist on-line layout format.

    Cheers,

    Chris Hughes-Gage

  • November 5, 2004 at 7:07 am

    #529085

    Superb. Congrats. Its a real good stuff.

    Jeswanth

    --------------------------------

  • January 5, 2005 at 2:37 pm

    #536078

    Great Stored procedure. How do I achieve the same length for a variable field? For instance, I have a money field.

  • January 6, 2005 at 1:53 pm

    #536279

    I ran the create table script and then the Create Proc. Then I used the "INSERT" statements in the article. Run the SP2PDF 'filename' and it ran without error but did not create a file. Bummer

  • January 7, 2005 at 10:30 am

    #536486

    I started using this for some basic reports that simplify my day.  I showed them to the boss and immediately he was wondering how far we can extend this capability.

    I realize that this article is old and people aren't perusing this forum too much, but I am wondering if anyone ever started using this in a production system.  How did it work?

    I'd also like to see if anyone extended the formating capabilities by adding fonts, bold, colors, etc.

    Regards,

    Scott

  • January 7, 2005 at 1:25 pm

    #536524

    Chuck-

    Check for C:\FILENAME.PDF, it should be there.

    Regards,

    Scott

  • January 7, 2005 at 1:42 pm

    #536529

    Thanks Scotts but no. I did a search for any pdf or any file created at that itme and nothing. the only thing I can think of is...

    when this returns @file is null and I'm thinking it should have some value!

    Thanks!

    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

  • January 11, 2005 at 7:07 am

    #536803

    Don't know what to say, it worked for me.

    How about rights to create the file, you might need to determine what account is running the SPROC.  I would think that if it failed that you get an error message.

    Good Luck,

    Scott

  • January 12, 2005 at 5:52 am

    #537005

    Thanks Scott,

    I've got sysadmin rights and no error msg. No file either LOL!

  • January 13, 2005 at 1:17 am

    #537194

    After EXECUTE @ole

    SELECT  @ole

    have to be

    -----------

    0

    help link:

    http://www.databasejournal.com/features/mssql/article.php/10894_1459181_1

    or read previous posts

Viewing 15 posts - 31 through 45 (of 152 total)

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