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


Automating Excel from SQL Server


Automating Excel from SQL Server

Author
Message
Joseph M. Steinbrunner
Joseph M. Steinbrunner
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 171
It is very interesting to note this is possible, however as others have mentioned, it has significant drawbacks. You must have Excel installed on the server, for example. Excel can be left hanging if even a single error occurs. Much better off using a third party product, such as SoftArtisans Excelwriter. Phenomenal product.
WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
David Johnson (12/5/2008)
Good article - very clear. It is good to know what is possible, even if there are drawbacks.

Please note that there is a comma missing, from before 'SaveAs'. Also, it would be worth mentioning that @value contains the value you want to save.


Good point about the @value variable.
The corrected save routine should read:

Declare @FileName varchar(100)
Set @FileName = 'C:\MyNewExcelSpreadsheet.xls'
execute @rs = master.dbo.sp_OAMethod @xlWorkBook, 'SaveAs', null, @FileName, -4143



As many have pointed out (and as I mentioned in the article), this is not necessarily the best way to do this type of thing... this is just to show what's possible. The biggest drawbacks include (as posters have mentioned) concurrancy, leaving an instance of Excel open, and Excel has to be installed on the server. Each of these are significant, and should be carefully considered.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

WayneS
WayneS
SSCertifiable
SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)SSCertifiable (6.2K reputation)

Group: General Forum Members
Points: 6235 Visits: 10403
paulallen7 (12/5/2008)
Nice article, even if there are drawbacks to this approach it's info worth knowing.

We've been looking at a bit of SQL automation of excel. (not similar to the problem provided but some ideas could be adapted).

Would anybody know if this approach would work on a 64 bit SQL server? (no 64 bit implementation of jet). I've got a stand alone app running on our SQL server that talks to ACE and JET databases (using WOW64) but couldn't get my CLR proc to do the same as it's running under the control of 64 bit SQL.

Any thoughts would be much appreciated.

Thanks
Paul

-- EDIT: Just noticed that post above was written at the same time! Smile

If Excel can be installed on the server, then it would work. But, if you're running a 64bit server, I'd think this over very carefully... are you sure you want to install Excel on that server just to do what's being done here???

If I remember correctly, there are a couple of different flavors of 64bit... one does allow the jet engine, the other doesn't. This just means compatibility with things that could use the jet engine, like OpenRowset.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
Links: For better assistance in answering your questions, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

ricol
ricol
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
Excellent!!

In my view perfect!! During the night filling up spreadsheets for users to consume based on what is loaded into the data warehouse. And so simple instead of digging into trying to make an XML-file (.xlsx) that MS is trying to convince us as better, of course correct if you want to ruin your customers.

Always these silly remarks about performance... Hey we are not kids, during night shift 95% of all SQL-Servers sleep if they are not filled up with silly IS-stuff copying the same data every night...

COM forever, MS will never manage to kill it!!

Rickard
ricol
ricol
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 48
Is there a reason for not serving this type of user. In my experience there are a lot of managers with a lot of money they are willing to pay for getting this type of solution.
Samuel Vella
Samuel Vella
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 2141
ricol (12/5/2008)

Always these silly remarks about performance... Hey we are not kids, during night shift 95% of all SQL-Servers sleep if they are not filled up with silly IS-stuff copying the same data every night...


Performance should ALWAYS be considered, no exception
Even if it is only to reduce power usage
MJVONSTEIN
MJVONSTEIN
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: 11
This is interesting information, but wouldn't this cause Excel to run in the same process space as SQL server, therefore any crash in Excel would crash SQL Server?

Thanks,
Mike
Ed Zann
Ed Zann
Mr or Mrs. 500
Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)Mr or Mrs. 500 (505 reputation)

Group: General Forum Members
Points: 505 Visits: 1391
If I wanted to do this kind of thing server-side I too would look at a server grade component like SA Excelwriter.

With MS Excel, I think if you would probably need to schedule periodic server restarts to clear all of the hung Excel processes that would accumulate over time.



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36116 Visits: 18744
Excel doesn't really run in the same process, AFAIK. It can hang, which could hang this SPID.

It's an interesting solution, and one that might fit in certain places. Having users query Excel works sometimes, doesn't in others. I have had plenty that I wouldn't want messing with Excel queries. Especially in cases where they had Windows Auth access to SQL Server.

My preference is to use SSRS/ SSIS to pull the data out and build an Excel sheet, but that's not to say this might not make sense in some cases.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
charles.flock-906611
charles.flock-906611
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 172
You could think about this in a completely different way. There is at least one third-party function library that provides every EXCEL function as a user-defined function (go to www.xleratordb.com). This means that whatever calculations are being done by EXCEL can actually be done on the database inside of a T-SQL statement. It is possible to imagine situations where you could eliminate EXCEL altogether.
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