Creating a PDF from a Stored Procedure in SQL Server

  • convert int column into varchar by cast

  • quote:

    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

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

  • 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!

  • Excellent, excellent sp!

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

  • Hi,

    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'



  • 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


  • Hello,

    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,


  • 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.

  • 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.

  • 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.


  • 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



  • 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?

  • Enjoyed this procedure. Thanks!


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

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