Rendering PDFs Natively in SQL

  • Comments posted to this topic are about the item Rendering PDFs Natively in SQL

  • Title is misleading, I think. PDF is generated using .NET CLR not SQL.

  • FileFoundException (5/7/2013)


    Title is misleading, I think. PDF is generated using .NET CLR not SQL.

    It was the use of the word 'rendering' that puzzled me - I think that 'generating' is better, as you suggest.

    Nonetheless, I'm sure that this will be a useful technique for many people.

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • Nice article David and very timely.

    We are looking to produce something very similar and this will be a great help.

    Mark Harby

    Nottingham. UK

  • FileFoundException (5/7/2013)


    Title is misleading, I think. PDF is generated using .NET CLR not SQL.

    Agree... and since it is I'd probably never put it on the SQL Server. Cool that you can incorporate stuff like this but I'd put it on an application server instead.

  • Ok, you already alluded to the fact that others would disagree with you.

    I hope your company does not have too much success. If they do, it is possible your SQL Server will not be able to handle the load, and you will have only one option...scale up; buy a larger SQL Server.

    For this reason, this kind of work is most commonly performed outside the database.

    Your concern regarding coupon tampering is extremely valid, and the transaction capability is also included. That being said, there is literally nothing keeping you from generating this as a Web Service, or WCF application, allowing the protection you require, transactional capabilities, and scalability. It would be completely inside the firewall.

    If cost is an issue, you could initially run the service on your SQL Server; just not in the SQL Server process. Then, when your company has an explosion or grows beyond your capacity, you host the PDF generation in a web farm inside your firewall, calling it from your web servers just as you do the database today.

    Frankly, it is very cool what you can do with the CLR inside SQL. This is a great demonstration of how flexible it is. But, just like using Indexed views to solve a bad schema, this is also not a great choice.

    Write more...you communicate very well.

    Cheers,

    Ben

  • FileFoundException (5/7/2013)


    Title is misleading, I think. PDF is generated using .NET CLR not SQL.

    I would agree. I create PDFs via SSRS but was hoping something other than another tool. A native solution is what I was hoping to read about.

  • taylor_benjamin (5/8/2013)


    I hope your company does not have too much success. If they do, it is possible your SQL Server will not be able to handle the load, and you will have only one option...scale up; buy a larger SQL Server.

    What a horrible thing to tell someone. Please post somthing soon so everyone can wish you well also.

    #@#%er @$$ :sick:

    As for the author. Awesome article and display of CLR uses. In a world where I have a Hyper-V SQL servers that can expand to 64 CPU's and 128GB+ RAM this would be a great solution for some of our daily automated emails and a lot lighter weight than other server side solutions. Don't pay any attention to the app develeper hate. The more your front end application servers are only tasked with UI Facade renedring the easier it is to scale any application. Good Job offloading.

  • Thanks for the post I have been doing this with SSRS

  • I think a disclaimer with the article would be fitting. Something that warns the reader about the implications for scaling this solution.

    If your mirroring your database CLR procs create overhead for DBA's or your automated deployment depending on what you use.

    I have used them in the past I would think hard about my options before using them again.

  • Awesome delivery. Great problem and solution. Would like to see a solution generating PDFs with barcodes.

    To the nay sayers with compaints about processing and security I say, get over it! If the software can do it, let it be done.

    If you can't keep your database secure, don't keep a secure database.

    If you can't process some extra CPU cycles, then get another CPU! The cycles have to go somewhere right? Shall we ask about the overhead of running it through SQLCLR? Run it through a SQL Express instance CLR assembly somewhere else?

    Excellent article, topic and discussion. Thanks.

  • For me the reserve isn't that you shouldn't do it. I simply think that there is probably at least on server dedicated to processing these and similar requests and that's where I'd put the code too.

  • Ted Cooper-351490 (5/8/2013)


    Awesome delivery. Great problem and solution. Would like to see a solution generating PDFs with barcodes.

    To the nay sayers with compaints about processing and security I say, get over it! If the software can do it, let it be done.

    If you can't keep your database secure, don't keep a secure database.

    If you can't process some extra CPU cycles, then get another CPU! The cycles have to go somewhere right? Shall we ask about the overhead of running it through SQLCLR? Run it through a SQL Express instance CLR assembly somewhere else?

    Excellent article, topic and discussion. Thanks.

    BWAAA_HAAA!!!! The only thing I don't like about this article (I don't really care for SQLCLR because of the abuse that has been heaped upon it but will tolerate good stuff. I'd have to test the stuff in the article, of course!:-D) is that the script turns xp_CmdShell and Ole Automation Procedures off instead of checking to see if they were on to begin with. Not all of us suffer the delusion that having these things turned off provides any more than a 3ms hurdle that a potential attacker is already prepared to jump. Doesn't anyone realize that only those with "SA" can run these things (well, unless someone was dumb enough to grant privs to individuals through proxies)?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    Thanks for chiming in. I'm a big fan of yours and have learned a lot from you.

    FYI, the attached script does in fact check the status of xp_CmdShell and Ole Automation, and returns them to their original settings.

    (Between trying to simplify some things for the article and some later edits to the code, the code excerpts in the article are not identical to the attached script.)

    If you (or anyone else) finds any problems or errors, please let me know.

  • David Rueter (5/8/2013)


    Jeff,

    Thanks for chiming in. I'm a big fan of yours and have learned a lot from you.

    FYI, the attached script does in fact check the status of xp_CmdShell and Ole Automation, and returns them to their original settings.

    (Between trying to simplify some things for the article and some later edits to the code, the code excerpts in the article are not identical to the attached script.)

    If you (or anyone else) finds any problems or errors, please let me know.

    Hoo-boy. I've got to get my eyes checked. :blush: Apparently I looked at only the dynamic code and not the "IF" qualifiers nor the capture of the original settings. My most sincere apologies. You're absolutely correct.

    The good part about me missing that and being made aware of my mistake is now there's nothing in the article that I take grand exception to. 🙂 Well done!

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 33 total)

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