Exporting to Excel Using a CLR Stored Procedure

  • steve quinn

    Old Hand

    Points: 314

    Interesting.

    But source code/DLL please?

  • ESL_HSBC

    SSC Veteran

    Points: 280

    very good but, can't use with SQL2005....;-)

  • richard-592555

    Valued Member

    Points: 59

    I love it - I think. Where is the download?:-)

  • The_African010

    SSC Journeyman

    Points: 87

    Hi,

    What kind of running times are we looking at? Say 1000 rows over 10 workbooks?

    Obviously different kit will give different results but say just an average time.

    Thanks

  • napa3uT

    SSC Journeyman

    Points: 82

    Hi,

    2 questions:

    1. Will it work on SQL 2005?

    2. Will I need to install Excel or other Office components on SQL Server machine?

    Thanks,

    Evgeny.

  • Rob Fisk

    SSCommitted

    Points: 1841

    evgeny-745697 (12/2/2009)


    Hi,

    2 questions:

    1. Will it work on SQL 2005?

    2. Will I need to install Excel or other Office components on SQL Server machine?

    Thanks,

    Evgeny.

    1: It does say that it has been tested on SQL 2005 with the caveat that some of the scripts may need to be tweaked.

    2: I am assuming that it does not need excel. It would make it far less than ideal if it did and I am sure that it would have been mentioned. Of course seeing the source for the dll would make that fairly clear.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • Horia Negrila

    SSC Veteran

    Points: 252

    ALTER DATABASE ProdReports2008 SET TRUSTWORTHY ON

    The above setting of database could be really tricky for some DBAs. As a developer, when I handed this request to our DBA, he just linked me several links with reasons why he wont do that (security reasons). So I had to create a certificate and sign the dll with that, then build the project. Then created an asymmetric key from the dll, created a login from the asymmetric key, and granted EXTERNAL ACCESS ASSEMBLY to that login.

    CREATE ASYMMETRIC KEY ClrExternalAccessKey FROM EXECUTABLE FILE = '*path*' --replace this with your path (where u copied the assembly on the DB server)

    CREATE LOGIN ClrExternalAccessLogin FROM ASYMMETRIC KEY ClrExternalAccessKey

    GRANT EXTERNAL ACCESS ASSEMBLY TO ClrExternalAccessLogin

    This way, u'll keep ur DBAs happy 🙂

  • wcwarner

    SSChasing Mays

    Points: 611

    Looks very useful. Where is the CLR source code?

  • Josep

    SSCommitted

    Points: 1518

    Rob Fisk (12/2/2009)


    evgeny-745697 (12/2/2009)


    2: I am assuming that it does not need excel. It would make it far less than ideal if it did and I am sure that it would have been mentioned. Of course seeing the source for the dll would make that fairly clear.

    I wouldn't bet for it. Usually these solutions use Excell (or other Office applications) COM interface: so you need Excell DLLs.

    Hoping to get on hands of the CLR code to see if this implementation needs Excell installed.

    Josep

  • henry99

    Newbie

    Points: 9

    Source Code?

  • David Gosche

    Grasshopper

    Points: 11

    This apparently makes a .XLS file, but could it also make a .XLSX file? I didn't see anything that specifically said to make just the .XLS file.

  • Rob Fisk

    SSCommitted

    Points: 1841

    It would be really, really nice if people could actually read the previous comments and realise that 95% of the comments so far are just one liners asking for the source and that just more of that only goes to clog up the discussion.

    Let's give it some time.

    Maybe the author submitted before going to bed, who knows.

    [font="Arial"]_______________________________________________________
    Change is inevitable... Except from a vending machine.[/font]

  • dsmith1211

    Newbie

    Points: 5

    Just another voice in the wilderness trying to find the DLL attachment - glad I'm not alone - I was feeling pretty stoo-pid for a minute.

  • INCREDIBLEmouse

    SSC Eights!

    Points: 814

    This is not another post looking for the missing download. That would be rude.

  • Anders Pedersen

    SSChampion

    Points: 11410

    Howdy.

    Going to assume that Steve is not awake yet, and will fix the source code when he gets up and sees comments.

    It does work on SQL 2005, only tested on 32 bit. Only tested 2008 on 64 bit.

    It does not require Excel to be installed on the server.

    Performance testing on this is sort of useles. There is nothing to compare it to.

    There is a security risk with this, no doubt about that. But, if the DBA has good control over what goes on the server in the CLR procedures, I don't think the risk in unreasonable. Now, there are DBAs that do not know C#/.NET at all and would be very uncomfortable with this, and understandably so. I've gone trough a wholescale conversion of my thinking in the last year, from being a firm "no CLR until I know more," to now seeing the light and that it is useful in many circumstances.

    As far as the path goes. The code that will be in the source, can also very easily be added directly to a C# application to export to Excel from that. That was actually the original venue for this functionality, when I saw that in Nick's code I wanted to know if we could easily expose that functionality in SQL.

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

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