Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««910111213»»»

Exporting to Excel Using a CLR Stored Procedure Expand / Collapse
Author
Message
Posted Monday, December 21, 2009 7:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:46 PM
Points: 35,959, Visits: 30,250
SQL Server is running the CLR... SQL Server must be runing as a user that has the privs to "see" the UNC.

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

"Change is inevitable. Change for the better is not." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #837199
Posted Thursday, February 25, 2010 6:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 22, 2010 5:21 AM
Points: 5, Visits: 15
Hi
It is very good and very useful for me
Thanks a lot
Post #872679
Posted Friday, June 18, 2010 4:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
I have tried this and it seems to work fine, however it does not recognise excel when I tried to open although it opens it and my main point is it does not work on a network drive, basically the file needs to be saved in the server where sql server is installed.
Any way to make this work on a network drive? I tried and have required permissions but not working.

Ideally I need it to run on a nework driver to avoid round trips to the server to retrieve the created file.

Thanks

sidni
Post #939486
Posted Friday, June 18, 2010 8:22 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163, Visits: 427
I normally just create a very restriced access share on the DB machine so I can grab the files off it remotely. I find that with SQL server it's a great sanity saver to work with local drives within SQL and work out the access elswhere.


For this exact issue though when users needed access. I actually set the file location to the SQL server local folder that Reporting Services web site is hosted in.

I had a report that ran this SP as well as the same results in the web report and included a static hyperlink to the file.

Nice easy solution.

Basically the reason for this was that they wanted the report both pretty for sending on but still able to sort through in excel and any kind of prettyness messes up the exported file's ability to sort due to merged cells etc in RS.


_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #939634
Posted Friday, June 18, 2010 8:41 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 12:43 PM
Points: 1,281, Visits: 739
sidni (6/18/2010)
I have tried this and it seems to work fine, however it does not recognise excel when I tried to open although it opens it and my main point is it does not work on a network drive, basically the file needs to be saved in the server where sql server is installed.
Any way to make this work on a network drive? I tried and have required permissions but not working.

Ideally I need it to run on a nework driver to avoid round trips to the server to retrieve the created file.

Thanks

sidni


I do this on several of my reports that I wrote this code for. As have been noted above, you should be able to do it with the account that SQL runs under's permission on the folder.

Another way I use it is part of SSIS packages, and then use the file copy in SSIS to put the file in it's final folder (I have some that for various reasons go to multiple locations).

Most of mine though are mailed out from SQL directly to the recipients.

Anders
Post #939654
Posted Friday, June 18, 2010 8:56 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
For some of my Tables I get the following errors, which I don't know if anyone come across these. I have no idea how to try and fix these:

1. The file you trying to open is in a different format than specified by the file extemsion....

1. comes from all executions and 2 below for some. Something to do with excel

2.

Problems During Load

Problems came up in the following areas during load

Workbook Setting

Thanks

sidni
Post #939675
Posted Wednesday, June 30, 2010 8:44 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
Igore my previous post, just figured it out. Great work.

Thanks

sidni
Post #945553
Posted Thursday, July 01, 2010 10:43 PM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 29, 2012 4:56 AM
Points: 163, Visits: 427
sidni (6/30/2010)
Igore my previous post, just figured it out. Great work.

Thanks

sidni


Sorry, missed the original question so have not investigated. Since you have worked it out then an explanation of the cause and solution may be helpful in case anyone else comes across the same issues.

Cheers.


_______________________________________________________
Change is inevitable... Except from a vending machine.
Post #946583
Posted Friday, July 02, 2010 2:39 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
Basically if your first field/column contains only nulls or no data, you will get the excel error on opening the output file and the file will not open at all. That is because the first column values are taken as the excel tab name, and you cannot have an unnamed tab name.

Having gone to basics and reviewed the examples from the article, I worked out that you need to at least create a dummy column populated with data, for example as per article
select 'sysobjects',*from sys.objects, which creates a first field populated with 'sysobjects' and this is taken as the tab name.


I hope it helps

sidni
Post #946661
Posted Friday, July 02, 2010 2:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, October 30, 2013 9:37 AM
Points: 49, Visits: 160
I must add a thank you, this is saving me a great deal of time.
Post #946663
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»»

Permissions Expand / Collapse