XLSExport: A CLR procedure to export proc results to Excel

  • bret.lowery

    Old Hand

    Points: 302

    Comments posted to this topic are about the item XLSExport: A CLR procedure to export proc results to Excel

  • mark.phillips-744751

    SSC Journeyman

    Points: 90

    Hi,

    I am not sure if I am missing something, but how do I add the NPOI library to the application. I have create a VS 2010 SQL CLR Project (vb.net) but I cannot seem to add a reference to the NPOI dll?

    Can someone please point me in the right direction.

    thanks

  • Bradley Deem

    SSCrazy

    Points: 2565

    I assume the dll is unsafe. So for example if we were adding System.Web.dll.

    -- Trustworthy is required for unsafe access or use certificates

    ALTER DATABASE Utility SET TRUSTWORTHY ON

    -- This project depends on System.Web.dll

    CREATE ASSEMBLY [System.Web.dll]

    AUTHORIZATION dbo

    FROM 'C:\Windows\Microsoft.NET\Framework64\v2.0.50727\System.Web.dll' -- Path on the server 64 Bit server 2008.

    WITH PERMISSION_SET = UNSAFE

    You can also use the UI to browse and upload the dll, but then you may not get the full error message if there is an issue.

    In SSMS, click on the Database, under Programmablity, Assemblies, right click "New Assembly..."

    Once you've added the assembly to the database, you can added it as a reference to your Visual Studio project.

  • mark.phillips-744751

    SSC Journeyman

    Points: 90

    Thank you.

  • yolousa

    Old Hand

    Points: 367

    In my opinion this one thing you should not use CLR integration for... it is cool and interesting, but I would not do it in a production environment

  • mark.phillips-744751

    SSC Journeyman

    Points: 90

    Grasshopper,

    What would be your reasons for not doing this in a production environment?

  • dangle-706099

    SSC Rookie

    Points: 27

    Agree, don't do it this way. For small data sets it might be okay. Not for useful/larger sets. I experimented with a very similar routine recently using NPOI, but for larger sets, and the looping over each column and over each row made it quite slow.

    My scenario:

    Had to export a specific SP's data from SQL to Excel. Some dates had to be mm/dd/yyyy, other date columns had to be mmm-yyyy etc. The formatting rarely changes. Usually get back around 15k records, with 20 columns.

    I ended up creating an excel template for that specific format requirement, saved it in a shared folder on the sql server.

    Created a clr sp to copy & rename that template to a working file. Coding it as a clr sp let me limit the copy/rename operation to the specific shared folder and a date & login-based filename. (instead of enabling xp_cmdshell, etc)

    Then modified my specific SP to call this clr sp to prepare the template for use, then used OPENROWSET to export the data I wanted into that temp file.

    Openrowset was much faster than looping over rows & columns. Plus, didn't involve installing NPOI as an assembly on SQL server. Since I tried it both ways, I don't remember if Trustworthy had to be on with the solution I ended up with, but prefer not to do that, normally.

  • fahey.jonathan

    Hall of Fame

    Points: 3526

    I use the querying capability built in to Excel to import from SQL Server rather than push things to Excel:

    xlApp = GetObject(, "Excel.Application")

    xlQueryTable = xlApp.ActiveSheet.QueryTables.Add(Connection:=psConnectString, Destination:=xlApp.ActiveSheet.Range(psCurrentCell))

    With xlQueryTable

    .Sql = psExecString

    .Refresh(BackgroundQuery:=False)

    End With

    The application is driving Excel to import, so the columns take on the datatype supplied by SQL Server rather than having to set each one excplicity.

  • yolousa

    Old Hand

    Points: 367

    Because you have lots of other ways (including SSIS) to accomplish the same and because I like separation of resposibility in my architecture and beacuse (in my view) each piece of software should be responsible for doing what it is REALLY good at doing. I could mention memory consumption, processor utilization, etc but you get the drift. I try to leave the database out ETL functions... but that is just me. Don't get me wrong it was a good read, I just gave my two cents. (BTW, grasshoper is my status since I do not post very often, actually posted only one time. My id is yolousa)

  • jcasement

    SSC Veteran

    Points: 220

    I took the dnld from the article and tried in vain to compile/deploy/run the Solution.

    I'm failing with the following:

    L:\ZipStuff\XLSExport\XLSExport\bin\Debug\iCExport.sql(39-39): Deploy error SQL01268: .Net SqlClient Data Provider: Msg 6503, Level 16, State 12, Line 1 Assembly 'npoi.hssf, version=0.0.0.0, culture=neutral, publickeytoken=null.' was not found in the SQL catalog.

    Any ideas on how to correct? and Yes I did the SQL configuration to enable CLR and Alter for Trust on the database.

    Thanks, Sid

  • irozenberg

    SSC-Addicted

    Points: 478

    Thank you for article.

    IMHO if I need to import data to Excel from SQL I would use either

    SSIS package

    or DYI Stored Procedure (Plain old-fashion one, not CLR) that imports data to CSV file.

    BTW MS ACCESSS had the best import Wizard to perform this in no time.

    Excel always was and I hope always would be able to read CSV files (regardless of version).

    You solution is based on assumption that all your customers have the same version of EXCEL.

  • YSLGuru

    SSC-Insane

    Points: 21304

    yolousa (6/23/2011)


    Because you have lots of other ways (including SSIS) to accomplish the same and because I like separation of resposibility in my architecture and beacuse (in my view) each piece of software should be responsible for doing what it is REALLY good at doing. I could mention memory consumption, processor utilization, etc but you get the drift. I try to leave the database out ETL functions... but that is just me. Don't get me wrong it was a good read, I just gave my two cents. (BTW, grasshoper is my status since I do not post very often, actually posted only one time. My id is yolousa)

    Can you give a short example of how SSIS would be a better choice then NPOI for doing this kind of SP based Data Export to Excel with the following criteria/conditions?

    1) Users can call the SP at any time of day that they have access to the DB.

    2) Users have no security/permissions above execute on the SP

    3) Allows the users to provide the arguments for the SP (the SP’s arguments are NOT hard coded/fixed)

    4) Can run on a system that does not have Excel installed on it.

    I'm not saying this can't be done in SSIS but I sure would like to know how you’d do it in SSIS and still meet the criteria I've provided. As far as I know SSIS is for ETL done by the DBA or on a scheduled basis and not really something for end users to use at their leisure to generate data output based on dynamic parameters they provide.

    I’m looking and have been for a while on how to give users of our DB a way to create reports in Excel and this NPOI sounds promising. I’m curious, very curious to know how you’d use SSIS to do this. I may be wrong but I think you may have mis-interpreted the authors goals. I don’t believe he/she was looking for a process to export data themselves in raw form but something more along the lines of a reporting tool that uses Excel as the presentation layer for the report.

    Thanks

    Kindest Regards,

    Just say No to Facebook!

Viewing 12 posts - 1 through 12 (of 12 total)

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