Automating Excel from SQL Server

Question

Automating Excel from SQL Server

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

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

Samuel Vella SSCrazy Eights Points: 8055 More actions · Answer 1

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 Valued Member Points: 71 More actions · Answer 2

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 SSCrazy Eights Points: 8694 More actions · Answer 3

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 - SSC Editor SSC Guru Points: 736478 More actions · Answer 4

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.

charles.flock-906611 SSC Enthusiast Points: 151 More actions · Answer 5

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.

WayneS SSC Guru Points: 95460 More actions · Answer 6

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

WayneS SSC Guru Points: 95460 More actions · Answer 7

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

WayneS SSC Guru Points: 95460 More actions · Answer 8

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

DragonGod Old Hand Points: 396 More actions · Answer 9

DragonGod

Old Hand

Points: 396

December 5, 2008 at 8:06 am

#908332

Easy Read. Very Clean.

Thanks.

Peter Rijs Hall of Fame Points: 3400 More actions · Answer 10

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

Laurie Dunn Ten Centuries Points: 1101 More actions · Answer 11

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.

gnash Valued Member Points: 59 More actions · Answer 12

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.

paule SSC Veteran Points: 266 More actions · Answer 13

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

ricol SSC Veteran Points: 241 More actions · Answer 14

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

rprice-959549 Valued Member Points: 59 More actions · Answer 15

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