XLSExport: A CLR procedure to export proc results to Excel

Question

XLSExport: A CLR procedure to export proc results to Excel

bret.lowery

Old Hand

Points: 302
More actions
June 22, 2011 at 10:14 pm

#252406

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

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

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

mark.phillips-744751 SSC Journeyman Points: 90 More actions · Answer 1

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 More actions · Answer 2

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 More actions · Answer 3

mark.phillips-744751

SSC Journeyman

Points: 90

June 23, 2011 at 8:58 am

#1343096

Thank you.

yolousa Old Hand Points: 367 More actions · Answer 4

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 More actions · Answer 5

Grasshopper,

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

dangle-706099 SSC Rookie Points: 27 More actions · Answer 6

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: 3617 More actions · Answer 7

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 More actions · Answer 8

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: 280 More actions · Answer 9

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 More actions · Answer 10

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: 21661 More actions · Answer 11

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!