Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


XLSExport: A CLR procedure to export proc results to Excel


XLSExport: A CLR procedure to export proc results to Excel

Author
Message
bret.lowery
bret.lowery
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 190
Comments posted to this topic are about the item XLSExport: A CLR procedure to export proc results to Excel
mark.phillips-744751
mark.phillips-744751
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 176
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
Bradley Deem
Mr or Mrs. 500
Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)Mr or Mrs. 500 (589 reputation)

Group: General Forum Members
Points: 589 Visits: 1248
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
mark.phillips-744751
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 176
Thank you.
yolousa
yolousa
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 89
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
mark.phillips-744751
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 176
Grasshopper,

What would be your reasons for not doing this in a production environment?
dangle-706099
dangle-706099
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 60
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
fahey.jonathan
SSC-Enthusiastic
SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)SSC-Enthusiastic (128 reputation)

Group: General Forum Members
Points: 128 Visits: 398
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
yolousa
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 89
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
jcasement
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 127
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search