Post reply

Automating Excel from SQL Server

  • December 4, 2008 at 10:47 pm

    #196867

    Comments posted to this topic are about the item Automating Excel from SQL Server

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 4, 2008 at 10:49 pm

    #908069

    A good script, I was looking for some time.

  • December 5, 2008 at 12:20 am

    #908078

    Nice article...:)

  • December 5, 2008 at 1:25 am

    #908092

    Yeah, but in reality:

    - You'd at very least set the Excel worksheet up with data ranges, then all you have to do programatically is ask the worksheet to refresh itself from the database. Easier to maintain.

    - You'd think very seriously about using Excel Services (or a 3rd party product) rather than kill your SQL Server (and your sanity) with COM calls. Not to mention the concurrency impact.

    Just because we can, doesn't mean we should...

  • December 5, 2008 at 2:00 am

    #908105

    Hi,

    Maybe it's a marvelous idea, but......

    Why do we need this ?

    An Excel-user can create a Microsoft Query in Excel (OLEDB) and update the spreadsheet he created with all the formatting he wants.

    The DBA creates a view for this purpose and give permission to the user who needs it.

    Users can than select a subset of the data (if needed) by using parameters in the query.

    Users are able to choose for automated refreshing of the data when openening their excel-spreadsheet or by command.

    My experience is that when you build something specific for a user (formatting he wants, data he wants now), you will get a lot of requests for enhancements and little changements, which will keep you busy.

    Regards,

    Frank van de Ven

  • December 5, 2008 at 2:21 am

    #908110

    Automating Excel is a very useful way to solve the kind of problem you describe, but I too would be wary of doing it from within SQL Server. For example if something goes wrong, how can you be sure never to leave an instance of Excel running in the background?

    I have found it useful to automate Excel from scripts - in my case mainly VBS. You will find that you can use much the same Excel code as you are doing already, and you could access the database using ADODB connection, recordset and command objects and many more.

    If you are into Powershell then you could use the .NET objects instead to access the database and benefit from much better error handling as well.

  • December 5, 2008 at 2:31 am

    #908113

    I'd have to agree with Piers,

    This looks like it could be useful under certain circumstances and for that reason the article is worthwhile.

    In most cases though, I would not want to ask the database to perform those operations. SSIS, VBA or a .NET application would be more appropriate.

  • December 5, 2008 at 2:41 am

    #908115

    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.

  • December 5, 2008 at 4:28 am

    #908145

    Will this work on 64 bit Sql Server?

    Paul

  • December 5, 2008 at 4:28 am

    #908146

    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! 🙂

  • December 5, 2008 at 5:50 am

    #908196

    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.

  • December 5, 2008 at 6:16 am

    #908221

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 5, 2008 at 6:22 am

    #908226

    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! 🙂

    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
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • December 5, 2008 at 6:43 am

    #908244

    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

  • December 5, 2008 at 6:48 am

    #908246

    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.

Viewing 15 posts - 1 through 15 (of 102 total)

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