Exporting to Excel Using a CLR Stored Procedure

Question

Post reply

Exporting to Excel Using a CLR Stored Procedure

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

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

steve quinn Old Hand Points: 394 More actions · Answer 1

steve quinn

Old Hand

Points: 394

December 2, 2009 at 4:39 am

#1086361

Interesting.

But source code/DLL please?

ESL_HSBC SSC Veteran Points: 290 More actions · Answer 2

ESL_HSBC

SSC Veteran

Points: 290

December 2, 2009 at 4:43 am

#1086362

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

richard-592555 Valued Member Points: 59 More actions · Answer 3

richard-592555

Valued Member

Points: 59

December 2, 2009 at 4:48 am

#1086363

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

The_African010 SSC Journeyman Points: 87 More actions · Answer 4

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

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

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

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

wcwarner

SSChasing Mays

Points: 611

December 2, 2009 at 6:03 am

#1086406

Looks very useful. Where is the CLR source code?

Josep SSCommitted Points: 1518 More actions · Answer 9

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

henry99

Newbie

Points: 9

December 2, 2009 at 6:20 am

#1086428

Source Code?

David Gosche Grasshopper Points: 11 More actions · Answer 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: 1861 More actions · Answer 12

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

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

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

Anders Pedersen SSChampion Points: 11410 More actions · Answer 15

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.