Creating a PDF from a Stored Procedure in SQL Server

  • ivica-105479

    SSC Veteran

    Points: 230

    convert int column into varchar by cast

  • sokun2003

    Old Hand

    Points: 301


    convert int column into varchar by cast

    i dont get what u said...sorry...coz the column of psopdf is already in varchar

    why sqlserver tried to convert 'TOTO' in to the INT column? where does this "INT column" come from ? i really dont get it...sorry

    can u pls explain ? tks

  • ivica

    Right there with Babe

    Points: 727

    try like this --> SELECT CAST(nb_client AS VARCHAR) + ' ' + CAST(tx_shor_name AS VARCHAR) ...

  • gimpygoat

    SSC Enthusiast

    Points: 181

    WOW! A++++++ this is a super procedure! Kudos to you for all your hard work! I am using this on a production DB that logs all firewall / web traffic event logs. I then setup this up as a job that runs daily and creates pdf reports based upon various criteria. Again, great job on the procedure and thanks for giving those easy to understand screenshots and how to's!

  • dgentry

    SSC Enthusiast

    Points: 196

    Excellent, excellent sp!

  • DarkKnightWI

    Mr or Mrs. 500

    Points: 508

    Very nice tool, only critic I would have is a description of the variables that are used in the procedure.

  • ivica

    Right there with Babe

    Points: 727


    Excellent script.

    However, there is  a glich. No sorting in #text and in psopdf, hence you cannot guarantee

    the resulting pdf report data order.

    So, can you please include:

    CREATE TABLE psopdf (

    pkey int identity (1,1)

    code NVARCHAR(80))

    and ...

    INSERT INTO #text(code) (SELECT code FROM psopdf order by pkey)

    If you don't believe, try couple times to run this:

    set nocount on

    declare @i int

    set @i = 1

    while @i <1000


    insert into psopdf(code) values(str(@i,10))

    set @i=@i+1


    exec sql2pdf 'test'



  • ivica

    Right there with Babe

    Points: 727

    If I have about 1650 rows in the table

    psopdf the SP will crash at line


    Server: Msg 8152, Level 16, State

    4, Procedure sql2pdf, Line 162


    or binary data would be truncated. The

    statement has been terminated.


    line in the psopdf is longer than about

    60 chars (only givenname, surname,

    telephone number).

    With less than

    1600 rows the SP works fine.


    Set in table #pdf code

    nvarchar(400) and @trenutnired


  • amfps

    SSC Eights!

    Points: 814


    I'm trying to use this procedure with .asp and ntext fields, but I'm having some problems with it.

    How can I deal with ntext fields, and page breaks?

    Thank you,


  • ivica

    Right there with Babe

    Points: 727

    Page dimension is 80x60 chars.You can make virtual table:

    INSERT psopdf(code)

      SELECT SUBSTRING((city+ SPACE(20)),1,20)

      + SUBSTRING((state+ SPACE(10)),1,20)

      + SUBSTRING((address+ SPACE(25)),1,20)

    FROM  authors

    EXEC sql2pdf 'demo1'

    Playing with SUBTRING you can get text alignment (demo1 has left align).

    This is tip how to get exact lenght for varchar.

    For new page you have to insert blank row into the psopdf and every 60th row will be new page.

  • Leendert van Staalduinen

    Right there with Babe

    Points: 797

    Very good job. I think M. Ivica has shown us a road, we would never have found by ourselves. Thanks, I will use it in near future.

    Leendert van Staalduinen.

  • oldgoat


    Points: 2344

    wow! shows how easy it really is, given a little time to do it.

    I've checked out the acrobat specs doc, and from that there's plenty of potential for a lot of formatting control, though you'd have to use either an xml file for the parameters around each bit, or a mor complex table that could hold it.


  • Joseph-146713

    Valued Member

    Points: 72

    Hi I wasn't able to create the file. Everything compiles fine but no output file is generated.....


    Also, is the following line in the code legal? i found some funky chars in it:

     INSERT INTO #pdf (code) VALUES ('%ÓÓÓÓ')

    Please let me know



  • dz

    SSC Enthusiast

    Points: 127

    There are problems when generateing more than one page. It looks that this procedure breaks every 60 lines into a page. One page works fantastically. But when I have more than 60 lines, my Acrobat Reader 6.0 will encounter an error "There is a problem reading this document (114)".

    Any thought? Lower Acrobat reader works fine?

  • Jerry OLoughlin

    Old Hand

    Points: 394

    Enjoyed this procedure. Thanks!


Viewing 15 posts - 16 through 30 (of 150 total)

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