December 5, 2008 at 9:59 am
What a well written, easy to understand article. Thanks for doing it. I found the content of the article relevant and appropriate. I also appreciate the discussion that has followed, which we wouldn't have had without the article.
Given the concerns listed by some for using this technique, I'm curious why a couple of people think that SSIS would be any better. Are you thinking of using SSIS to create the same Excel spreadsheet? Then aren't you still essentially opening and playing with Excel from SQL Server? (I haven't used SSIS to create Excel documents, so I don't know.)
Thanks for the article.
December 5, 2008 at 10:28 am
We recently went through an exercise on automating Excel, and it is what I would consider to be the "worst case scenario". We had a few hundred excel documents in a format that users were "supposed" to not modify with a two week deadline. Installing MS Excel on the server was really not a good idea from a security perspective, so we couldn't even consider this path. SSIS could process excel documents, but found that users wanted to receive immediate feedback when uploading documents. We also found SSIS could not open worksheets by name if the worksheets had a paranthesis in the name. We also found that users would modify the excel spreadsheet structure, so we needed to add a lot of validation.
Our approach was to use a ASP.NET control from Aspose called cells, so that users could upload the documents themselves and receive immediate feedback on their wrong-doings. There are probably other controls out there, but we were under a 2 week deadline, and this could provide immediate feedback, and didn't require Excel to be installed on the SQL Server. We added the business logic for validating the excel spreadsheets and placing the data into SQL server into a pile of import tables (columns set to a randomly large column size like varchar(3000)). After creating the import tables, we had another set of tables where we attempted to migrated the data into (final tables). Using stored procedures and logging, we caught the errors immediately, and diplayed them on the screen for users, and deleted the junk data if they had errors, and cleaned up the import tables.
I like the approach the author presented, but the best approach is to have a web form to enter the data. Excel really, really, really stinks when it comes to collecting data. If it needs to be collected, the excel spreadsheets need to be locked down and as many validation rules placed in it as possible. If that's not possible, then you need to add a lot of validation, because you will get everything under the sun. For example, a simple column where we were expecting a dollar amount, we received: "N/A", "-", " ", "zero", column movement and column swapping (With the same types of data).
I wish I had some input into the data collection effort before we started receiving the excel files, but that's the way it goes...
December 5, 2008 at 10:58 am
We do have a couple of applications using similar approach. They are coded by a consultant. I prefer to demonstrate what reports you can produce in the Reporting Services and that this report can be exported to Excel on Demand or a subscription including email subscription could be created. If I do a demo shortly after the requesting side tells me "here is an Excel report we need the same but automated" they usually agree that the Reporting Services is a good tool!
Regards,Yelena Varsha
December 5, 2008 at 11:55 am
The third block of code has a typo:
change the last word to OUTPUT (not OUTUT)
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 5, 2008 at 12:53 pm
The throwaway line "Obviously, if SQL Server is going to drive Excel, then Excel needs to be installed on the server that it's running from." has significant implications and MS advises against and doesn't support this.
Having said that I've automated Excel, Word and Infopath server-side with mixed results (although not through SQL Server)
December 5, 2008 at 2:13 pm
For the love of god.. stop showing people crap like this. Someday I will take a consulting job where some vbscripter DBA .NET wannabee has seen and applied this like its gospel and I will have to rip it all out and re do it, just because they read it on SQL Server Central.
Yes its "neat" SQL Server allows this. Its also neat I can puncture a lung with a screwdriver, BUT WE DONT DO IT. EVER!
Too many times, I have seen IIS Servers, SQL Servers, Oracle Servers brought to their knees because someone thought THIS was a good idea, and when Task Mangler is opened, 50 copies of Excel are running in the background. Ugh.
If your going to write articles, make them useful and SANE. Show things that people SHOULD do, not necessarily what they CAN do. Showing people things like this does a disservice to the IT industry as a whole and to future consultants who walk into this crap, weep and then have to fix it.
December 5, 2008 at 2:25 pm
Sorry, you think all of us are fouls. Might be that some consultants use their small brains. I understand your need of telling what could happen if a technolgy is misued, BUT...
Any technolgy misused is a disaster, period.
This technology used in the right condition IS USEFUL, but miused could be a disaster, totally agree.:w00t:
BUT there is no reason to believe that if something could be misused it should be forbidden, right?
Sorry for any misspelling, not naitive English.
December 5, 2008 at 2:35 pm
ricol (12/5/2008)
Sorry, you think all of us are fouls. Might be that some consultants use their small brains. I understand your need of telling what could happen if a technolgy is misued, BUT...Any technolgy misused is a disaster, period.
This technology used in the right condition IS USEFUL, but miused could be a disaster, totally agree.:w00t:
BUT there is no reason to believe that if something could be misused it should be forbidden, right?
Sorry for any misspelling, not naitive English.
Automating Excel on a server is not recommended. Not by Microsoft. Certainly not by any respectable consulting or development companies. Why? Because it has been proven it is not a stable solution, time and time again.
This is not acceptable. Do not do it. Do not demonstrate it.
There are so many other ways to resolve the problem of getting data into Excel, that this avenue should not ever be explored again. While I agree that the sp_OA procs can certainly be used for other types of things, the author should have demonstrated THAT, instead of automating Excel.
To propogate the server office automation idea makes people think its "ok" to do it. Its not. Ever.
December 5, 2008 at 3:04 pm
I have heard this story before, and before and before.
Still my stuff runs stable!!! All nights all year around.
So why all these bad stories, show me the proof!! I have another story to tell based on experience.:)
I get so bored on religious arguments! I heard.....
December 5, 2008 at 6:20 pm
DiverKas (12/5/2008)
Automating Excel on a server is not recommended. Not by Microsoft. Certainly not by any respectable consulting or development companies. Why? Because it has been proven it is not a stable solution, time and time again.This is not acceptable. Do not do it. Do not demonstrate it.
There are so many other ways to resolve the problem of getting data into Excel, that this avenue should not ever be explored again. While I agree that the sp_OA procs can certainly be used for other types of things, the author should have demonstrated THAT, instead of automating Excel.
To propogate the server office automation idea makes people think its "ok" to do it. Its not. Ever.
I'm with DriverKas on this. I implemented server-side automation of Office (from COM written in VB, not SQL Server) just before SSRS V1 came out of Beta, and to this day I'm STILL trying to get them to divert some budget to replace that crap with SSRS. It's an absolute nightmare to configure during deployment, it's in breach of MS licensing for the Office apps, in short it's a nightmare. We should take heed of MS's strong recommendations NOT to do this. Nowadays if I have Excel formatting requirements beyond the capability of the SSRS Excel export function I automate Excel on the client side from within IE.
December 6, 2008 at 6:07 am
Good article - my friends were always upset that SQL Server didn't have all the functions they were used to in Excel, so they created them and packaged them up. There are functions for math, financial, and other general purpose functions. There's a free trial download to kick the tires. The product is called XLeratorDB and the website is http://www.westclintech.com if you're interested.
December 6, 2008 at 11:12 am
ricol (12/5/2008)
I have heard this story before, and before and before.Still my stuff runs stable!!! All nights all year around.
So why all these bad stories, show me the proof!! I have another story to tell based on experience.:)
I get so bored on religious arguments! I heard.....
Heh... if you were to aggregate all of the religious arguments about SQL Server and obey that aggregate, SQL Server would simply not exist nor would anything else. 🙂
Nice article... I love it when things like this are well laid out and to the point. They key here is not whether some of the "religion" about this subject is true or not, the article shows "possibilities". As with any "possibility", ya gotta do some testing and "what if's", but that's a whole lot better than saying "it's impossible". Well done.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 7, 2008 at 5:15 am
"Nice article... I love it when things like this are well laid out and to the point. They key here is not whether some of the "religion" about this subject is true or not, the article shows "possibilities". As with any "possibility", ya gotta do some testing and "what if's", but that's a whole lot better than saying "it's impossible". Well done. "
I have done office automation starting with Apple Script some years ago (14 may be).
There is (still) a big demand to get data into a spreadsheet and there are many ways to
accomplish that. Articles and threads at SQL ServerCentral is a good start to learn.
I have developed many applications started in Windows 95 with VB5 and VBA and
besides memory leakage in Windows 95 (but not in NT) have had very few problems!!
And then I talk about server side solutions there Exelfiles (100s of them) are created
to be put on a file server to be opened by the users or client side solutions there the user
have a macro which downloads the data from a central server.
I have had problems with VB.net!! The problem is Excel orphans which exists if you don't
know how to shut down Excel. (Be aware Excel is a COM object).
Most of the examples I have seen are pretty simple. (Get som data and populate an Excel sheet. Maybe with some formating.) If you want to do accomplish something more complicated in Excel like many sheets to be popluated with data, filtering etc etc any SQL server based method like SSIS will be very hard to program and evaluate. I use VBA in Excel also 2007 and
that works. In fact I have upgraded a solution from SQL server 2000 Excel 2003 WIN 2003 to
Sql server 2005 Excel 2007 Win 2003 64 bit without changing one byte of code.
Using VBA doesn't mean that you have to be a sloppy macro programmer.
//Gosta
I have used DTS and SSIS but
December 7, 2008 at 6:38 am
Jeff Moden (12/6/2008)
Nice article... I love it when things like this are well laid out and to the point. They key here is not whether some of the "religion" about this subject is true or not, the article shows "possibilities". As with any "possibility", ya gotta do some testing and "what if's", but that's a whole lot better than saying "it's impossible". Well done.
Thanks Jeff. You hit the nail on the head... this article is about the possibility, and of how to do it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 8, 2008 at 8:57 am
Nice article, but I'd like to see how to actually work with data, i.e. retrieving data from a table or set of tables - or calling a SP to retrieve a complex report.
Thanks
Viewing 15 posts - 31 through 45 (of 102 total)
You must be logged in to reply to this topic. Login to reply