Exporting to Excel Using a CLR Stored Procedure

Question

Post reply

Exporting to Excel Using a CLR Stored Procedure

Viewing 15 posts - 61 through 75 (of 124 total)

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 1

Anders Pedersen

SSChampion

Points: 11410

December 2, 2009 at 2:44 pm

#1086786

sthompson-1139932 (12/2/2009)

It looks promising, but when I implement it I just get some garbage characters in cell A1 and all the data stuffed into A2.

hmmm not seen that one. Which version of Excel are you trying to open it with?

sthompson-1139932 Valued Member Points: 74 More actions · Answer 2

sthompson-1139932

Valued Member

Points: 74

December 2, 2009 at 2:53 pm

#1086791

I'm using Excel 2000. At first I though I had screwed up the code by incorporating it into my own dll, but I got the same thing when adding an assembly for ExcelExport.dll and using that. Does it only work with newer versions of Excel? Thanks.

Anders Pedersen SSChampion Points: 11410 More actions · Answer 3

sthompson-1139932 (12/2/2009)

I'm using Excel 2000. At first I though I had screwed up the code by incorporating it into my own dll, but I got the same thing when adding an assembly for ExcelExport.dll and using that. Does it only work with newer versions of Excel? Thanks.

We have not tested it on earlier versions. You might be able to open it by going to MS and download the compatibility add-inns. Not sure on that though, they might not be available for 2000 anymore.

Garadin One Orange Chip Points: 29613 More actions · Answer 4

This could potentially be a massive time saver for me. Thanks so much for writing this article and sharing this code.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Anders Pedersen SSChampion Points: 11410 More actions · Answer 5

Jonathan Kehayias (12/2/2009)

My only problem with this is the TRUSTWORTHY ON part. There are so many other ramifications associated with that beyond just allowing your External_Access CLR procedure to execute. This opens up cross database permissions based on the level of rights of the database owner, and can be really problemattic when restoring the database since TRUSTWORTHY is set to OFF by default when you restore or attach a database since it is untrusted. Its to easy to use a certificate signed assembly and do it the right way. Aside from that good work, I like the concept, especially that you used the OpenOffice XML format instead of trying to interop it with UNSAFE assembly.

Jonathan,

thanks.

I do agree that it does leave it open too much, should maybe have emphazied that a bit more in the write up. The solution to using a certificate was mentioned by someone above, and I will explore that option. For us the problem with restoring is not a problem for this particular case. Security risk was deemed ok since I have full control on what goes on that particular server.

digibee Newbie Points: 5 More actions · Answer 6

digibee

Newbie

Points: 5

December 2, 2009 at 7:54 pm

#1086858

Hi,

Can the exported file be in the user local drive or it has to be in the MS SQL Server machine drive?

Thanks,

luke

Rene G SSC Enthusiast Points: 183 More actions · Answer 7

barbiduc (12/2/2009)

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 🙂

THIS. The pretty bowtie to top off the present!

Rob Fisk SSCommitted Points: 1841 More actions · Answer 8

Marvelous on the key thingymajig.

In addition I also have a separate Tools DB where I keep things like this, tally tables, regex CLRs etc.

Mainly that means I have a single point of maintenance for the code of globally useful functions and procedures.

Therefore, if I am in MyDB my execution would look like:

DECLARE @params xml



SET @params ='<params></params>'

EXEC Tools.dbo.ExportToExcel 'MyDB.dbo.Test', 'd:\', 'monkey', @params

It's a real shame that the @params cannot be defaulted in the proc.

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

Derek.Seal SSC Rookie Points: 39 More actions · Answer 9

Great little bit of code but it still does not get me passed a very anoying problem..... which leads me to believe its my set up not the code thats the issue.

Example

Database has say 3 columns

Account number (Int) value 0001

Contact name (varchar(50)) Value 'Mr D Seal'

Address (varchar (500)) Value '1 The High Street

The Town Center

The Village

XX1 1YY'

Notice the carrage returns in the address column

when extracted to an excel sheet - For mailing - the 2 or 3 examples of how to export the date to excel

Remove the carrage returns.

Leaving me with 1 The High Street The Town Center The Village XX1 1YY' in the thrid column

OK im doing something wrong, But what ?

Please help

Rob Fisk SSCommitted Points: 1841 More actions · Answer 10

Derek.Seal (12/3/2009)

Great little bit of code but it still does not get me passed a very anoying problem..... which leads me to believe its my set up not the code thats the issue.

Example

Database has say 3 columns

Account number (Int) value 0001

Contact name (varchar(50)) Value 'Mr D Seal'

Address (varchar (500)) Value '1 The High Street

The Town Center

The Village

XX1 1YY'

Notice the carrage returns in the address column

when extracted to an excel sheet - For mailing - the 2 or 3 examples of how to export the date to excel

Remove the carrage returns.

Leaving me with 1 The High Street The Town Center The Village XX1 1YY' in the thrid column

OK im doing something wrong, But what ?

Please help

No, it's excel being a pain in the bum.

I thought it might be a simple case of escaping the carriage returns in the xml.

It's 0A that excel recognises as the carriage return rather than 0D or any combination of either.

I tried this at the sql end but it then escaped the ampersand within the xml.

I created a test table to look at this:

ALTER PROCEDURE Test



AS

DECLARE @test-2 TABLE(address nvarchar(500))

INSERT @test-2

SELECT 'aaaa

bbbb

cccc' UNION ALL

SELECT '1111

2222

3333'

SELECT 'addr',replace(address,'

',' ') FROM @test-2

This produced the xml:

<Data ss:Type="String">1111 2222 3333</Data>

When of course I would be looking for

<Data ss:Type="String">1111 2222 3333</Data>

Unfortunately Excel has the habit of then displaying these as unprintable characters (the wee square) until you actually start editing them. Never found a way around that.

Edit: Hmmm.. You would have thought that an xml code wrapper would display pointy braces properly. Ho hum.

Edit2: Double hmm it has un-escaped the bits that I did want in. namely the amp.

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

mj12 SSC-Addicted Points: 437 More actions · Answer 11

mj12

SSC-Addicted

Points: 437

December 3, 2009 at 7:17 am

#1087060

Tim Wolf (12/1/2009)

Where is the CLR source attachment?

*bump* 🙂

Bradley Jacques Ten Centuries Points: 1271 More actions · Answer 12

This is absolutly amazing. There are some huge advantages to using CLR to export to excel. One of the issues you will run into whether using open row set or SSIS is that the connection is limited to the way which SQL is handled through the JET engine. For example, the table column names in excel are limited to 54 characters (approx) and the data inserted into an excel cell is limited to 255 characters.

The CLR function handles the data insertion natively through the excel COM. This is amazing, the execution is a ligitimate 10X faster, there are formatting capabilities and there are no truncation issues up to 8000 characters on any of the columns. I really appreciate your posting this script here.

Thanks,

Bradley Jacques

sthompson-1139932 Valued Member Points: 74 More actions · Answer 13

Yea, I installed the compatibility add on, but it had no effect. My spreadsheet opens fine in a trial version of Excel 2007, so it obviously has to do with the version. I noticed that if I open the document with 'Microsoft Open XML Converter' (I don't know where the heck I got that), it looks good in Excel 2000. It seems like that converts the source to the unreadable stuff instead of XML.

Phil Factor SSC-Insane Points: 20224 More actions · Answer 14

I know it is a bit of a trip down memory lane now, but Robyn Page and I wrote an article in 2007, SQL Server Excel Workbench[/url] (http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/) describing various non-CLR techniques for exporting from SQL Server to Excel.

I like Anders' approach. I wish we'd thought of it!

Best wishes,
Phil Factor

Rob Fisk SSCommitted Points: 1841 More actions · Answer 15

Right, I've been doing some more investigation on the issue of carriage returns.

I changed

output = dr[dc].ToString().Trim();

to

output = dr[dc].ToString().Trim().Replace(Environment.NewLine, "& #xA;");

& # (space added here to avoid displaying as a CR in forum)

Again I get the issue that it is (escaping/serialising? not sure which is correct for this instance) the ampersand so viewing the saved doc in a text editor it reads

& amp;#xA; instead of & #xA; (spaces added after ampersand here again for display reasons)

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