Automating Excel from SQL Server

  • 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

  • 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

  • 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.

  • 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.

  • 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 http://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.

  • MJVONSTEIN (12/5/2008)


    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

    No, it's actually running in a separate process space.

    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

  • Ed (12/5/2008)


    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.

    No, just use TaskKill to kill them:

    TASKKILL /IM EXCEL.EXE rem Kills any excel processes still responding

    TASKKILL /F /IM EXCEL.EXE rem Kills any Excel processes still running forcefully

    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

  • Hey all, don't forget to check out the references in the article. I included a sql script that makes a bunch of stored procedures for handling different aspects of Excel for what is being done in the article (and a few other things to boot), and then it executes a report proc to generate an Excel spreadsheet on the root of your C: drive. Steve also included the word document I uploaded to him, so you can have the article that way also.

    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

  • Easy Read. Very Clean.

    Thanks.

  • AFAIK there were also the Office PIA's, that made it possible to do this kind of stuff without (fully) installing Excel on the server.

    Here they are: Office 2003 Redistributable Primary Interop Assemblies.

    I'm not sure if something like this exists for 2007 however, and agree with all other voices giving other possible solutions to fulfill similar requirements.

    Kudo's to Wayne though for exploring and illustrating the possibility, even if we really think of it as a last resort!

    Peter Rijs
    BI Consultant, The Netherlands

  • Thank you. Very helpful and clearly written. I provide various reports to a good number of engineers here and they love Excel. I'll definitely be able to use this.

  • Interesting article.

    I wanted to comment on one detail that I have been caught with before. In the article is mentioned how to get the last row and column using Cells.SpecialCells(11). The Excel constant is: Const xlCellTypeLastCell = 11. This will provide the last row and column of the UsedRange which is not always was is intended as UsedRange can include both rows and columns that visually appear blank. Net results is that this will give a blank row or column in the data set. Something to be aware.

  • http://support.microsoft.com/kb/257757

    "All current versions of Microsoft Office were designed, tested, and configured to run as end-user products on a client workstation. They assume an interactive desktop and user profile. They do not provide the level of reentrancy or security that is necessary to meet the needs of server-side components that are designed to run unattended.

    Microsoft does not currently recommend, and does not support, Automation of Microsoft Office applications from any unattended, non-interactive client application or component (including ASP, ASP.NET, DCOM, and NT Services), because Office may exhibit unstable behavior and/or deadlock when Office is run in this environment."

  • Most of us know MS viewpoint!

    Facts remains it works, so why worry...

    The posting you refer to is a legal posting trying to make people afraid of using this technolgy, probably thera are som legal aspects of running Office on a server... Could be chance of loosing money???

    Office run well in Citrix and TS environment so the problem is not technical!!

  • much easier to simply have the database do everything. use xleratordb to do the calculations that are currently being done by excel and just deliver the results to the user. you DON'T "need" excel at all. makes life much easier...

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

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