Rendering PDFs Natively in SQL

  • David Rueter

    SSCrazy

    Points: 2631

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

  • FileFoundException

    SSC-Addicted

    Points: 418

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

  • Phil Parkin

    SSC Guru

    Points: 243853

    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.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • mark.harby

    SSC Enthusiast

    Points: 158

    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

  • Kristian Ask

    SSC Eights!

    Points: 877

    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.

  • taylor_benjamin

    SSC Veteran

    Points: 286

    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

  • danielk1

    SSC-Addicted

    Points: 462

    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.

  • PHYData DBA

    SSCertifiable

    Points: 7541

    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.

  • srujanax

    SSC Rookie

    Points: 29

    Thanks for the post I have been doing this with SSRS

  • Charlie 22296

    SSC Rookie

    Points: 29

    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.

  • Ted Cooper-351490

    SSC Rookie

    Points: 43

    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.

  • Kristian Ask

    SSC Eights!

    Points: 877

    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.

  • Jeff Moden

    SSC Guru

    Points: 995144

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • David Rueter

    SSCrazy

    Points: 2631

    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.

  • Jeff Moden

    SSC Guru

    Points: 995144

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

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

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