December 10, 2008 at 4:01 pm
QueryToDoc Command Line Publisher (http://www.schematodoc.com) offers a command line driven solution to this problem. Write your query and store it in a file. Create a QueryToDoc parameter file that contains connection information, the location of the query file, the type of output you want to create (Excel, Word, HTML, or CSV file), the destination directory for the output file, and (optionally) a list of e-mail recipients who should receive the output file as an e-mail attachment. Create a cmd file that points to the QueryToDoc parameter file and then set up Windows Scheduler to run that cmd file every day. Result: Query gets run each day and selected recipients get the query results in the form of an Excel e-mail attachment.
December 11, 2008 at 3:19 am
I'm afraid I have to join the nay-sayers on this topic. It seems to be entirely the wrong way to go about this. Reporting Services, possibly combined with SSIS, will do all this and far, far more with full automation and no need for Excel installations on the server, etc. If RS isn't available, and it doesn't need to sit on the same server as the SQL Server instance, so surely most people could find a place for it somewhere, particularly as it's a no added cost component of SQL Server, then surely the sensible way is to approach the problem from Excel, using OLEDB, as has been mentioned earlier.
There is NEVER any excuse, as a professional, for doing something because you can, or because it's "cool", when you won't always be there to support it and when there are more stable, supportable ways of doing it.
January 13, 2009 at 10:34 am
Excellent Article
For some projects Reporting Services are great.
But, there are some situations where Excel Automation is just a fantastic solution.
And I may have one coming up where I want to call my VBA functions from T-SQL:
I prefer to create custom Functions or Subroutines in Excel VBA that include multiple parameters with hundreds of lines of code.
Any advice on how to call Excel Functions remotely from my T-SQL on SQL Server or considerations for creating them?
Also, for those of us who still use Access 2003 Applications with VBA automation and SQL Server Databases for small and medium business solutions: Are there any good articles like this one for Automating Access with T-SQL? I would rather drive code from my SQL Server using T-SQL.
By adding Interop on my SQL Server - from what I read, it will not be necessary to install Office on the SQL Server.
Interop for Office 2007 http://www.microsoft.com/downloads/details.aspx?familyid=59daebaa-bed4-4282-a28c-b864d8bfa513&displaylang=en
January 13, 2009 at 10:53 am
You can call Excel remotely from ASP and ASP.NET pages
Also we have good experience with MS ACCESS / VBA and linked tables to SQL Server. This is really good for disconnected clients. You can have local Access tables and when the client is on the network these local tables can be synchronized with SQL Server linked tables from MS ACCESS.
For you to be able to use remote Excel or Access procedures or functions you have to install Excel or Access on the remote server. There is a lot of articles on the topic but the main idea is:
Dim exapp As Excel.Application
Dim workbook1 As Excel.Workbook
Dim worksheet1 As Excel.Worksheet
......
exapp = New Excel.Application
workbook1 = exapp.Workbooks.Open(FilePath)
then use properties and methods of the workbooks and worksheets
Regards,Yelena Varsha
January 13, 2009 at 12:03 pm
You can find the equivalent of EXCEL functions which can be called in T-SQL at http://www.xleratordb.com.
January 15, 2009 at 10:52 am
Any ideas about installing Office Object Libraries with out installing Office 2003?
I had a dream that the Office Interop would just add the Excel .DLL onto my Windows 2003 server registry to provide the object librarys for my T-SQL automation to reference.
Not haveing to install Excel 2003 on my server sounded like a great solution.
The ReadMe files are corrrect, when trying to install an error message asked me to install Office 2003 first.
[font="Arial Black"]Previous Post in this thread: 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.[/font]
Read Me for Office 2003 Redistributable Primary Interop Assemblies
Minimum system requirements to install the Office 2003 Primary Interop Assemblies are:
A Microsoft Office System 2003 product
.NET Framework 1.1 or higher
B.T.W Installing Office 2003 Service Pack 1 after installing the Office 2003 Primary Interop Assemblies may require a repair of the Office 2003 Primary Interop Assemblies
MSDN Reference
http://msdn.microsoft.com/en-us/library/15s06t57(VS.80).aspx
February 14, 2009 at 10:41 am
For this type of problem, I like using excel web queries, and .iqy files.
An excel web query works by linking a section of the spreadsheet to the contents of a web page (NOTE: this does not have to be an html file). And the data can be updated whenever the user wants to. I won't go in to detail here, but search on web for how to create iqy files. Parameters can be added as well, which can come from either user interaction/ fixed values/ cell references.
Excel can be used to do the style / layout/ data type formatting, and sql can do the bit which it is best at, returning/sorting data.
The person who asked you for the report never has to again, as they can update it themselves (from excel), removing the need for scheduling and file storage. And they can add any pretty colors/etc themselves.
The implementation of such a web service is simple. Receive a web request, call a stored proc with any necessary arguments, and then return the resulting dataset in to a tab separated format (Excel will take this response, and place it in to the spreadsheet).
The parameters and name of the stored proc could in theory, be inferred by the parameters of the web query, such that you only ever need to create the web method once.
Sam
February 15, 2009 at 11:03 am
Thanks Sam!
It is great to have all the options!
This will probably work for some of my data only Excel creations.
And, I can get somewhat the same resusts in MS Reporting Services
Typically, I create Excel Workbooks that evaluate the data. The data determines the formula that I want to include in my Excel Workbook cell on an individual basis.
The number of Excel worksheets and the custom data in each are sorted out and organized at the server. Those choices may for example create a custom pull-down list box with values for a financial futures analysist based on today's prices and the prices of owned futures contracts that affect the enterprise risk. These are somewhat large reports that allow "what - if" from a Excel user interface.
Using Passthrough Queries and Stored procedures, these individual Excel reports were taking about 5 to 8 seconds on an unloaded network. That was ok until all the other users saw the wonderful applications and how much time savings it created.
You have given me another valuable option to explore. 🙂
B.T.W. I read somewhere on a blog that Microsoft Accountants internally use custom Excel reports generated with VBA (0r SQL T-SQL) at the server level.
I would love to be a fly on the wall behind one of those programmers!
T
Again Thanks!
March 8, 2009 at 11:59 am
I'm with the naysayers. This comes from several years of experience dealing with Excel automation. Not to say that this technique doesn't work for certain situations, but here are the flaws as I see them.
- Installing Excel on a server that has SQL Server
- Quirks dealing with Excel COM objects and programming.
- Assuming that the original spreadsheet will stay consistent
I think the best technique is to deal with Excel on the client or with a product/service that is designed to do this task.
As a side note: I have clients and coworkers that commonly submit data to me in Excel format. Excel is not a database or a desirable data source! Neither is Access! :crazy:
March 8, 2009 at 12:49 pm
Thanks for your comments. Any solution besides installing Excel on my SQL server would be great.
In this specific situation, the daily subscriptions that I bring in from multiple web sites and upload to my SQL Server databaes using around 500 lines of code save the little company more each month than my monthly contracting price alone. They could get it in XML instead of Excel, but at a much bigger subscription cost.
Users also have access to automated multi-tab Excel sheets that include protection, network user security, formulas, pull-down list and other features. They evaluate marketing contracts, approve them, then activate the positions to be brought back into SQL Server.
From a business perspective, it has given a small company a competitive edge against much larger companies. It allows me to change the business rules as the economy changes sometimes on a weekly basis.
Is this the solution for all my past, current, and future clients? No.
Is it a solution for everyone else's customers? No.
Is it something I do for the entire business support? No.
I don't use Access as the actual database, SQL Server is my database. Access is the programming interface. It is highly versioned with automated distribution to clients. Pass-Through queries are used so SQL Server manages the processing. It is not perfect. It appears to be very cost effective for this situation.
I am very interested in using other options than prevent installing Excel on my SQL server.
All comments and ideas are welcome.
The good, the bad, and the ugly suggestions will help in planning maintenance and support.
Or, help consider other cost-effective solutions.
March 8, 2009 at 12:57 pm
Thank you all for interesting comments in this thread.
We do have different experiences and what works for one
of us may be doesn't work for another of us.
I have been working with Excel and SQL-server for many years
and never really have had problems with automation. I have to go
back to Windows 95 and memory leakage problems. But I have one
basic rule. A common user can never open a file on the server or
run a (Excel)process on the server. An Excelfile is produced on the server
and delivered to the use (eg coppied to another server) or the user
can use a makro in his template Excelfile (pW protected) and with
remote OLE DB get data from the server.:)
I use VBA or still old VB for this. I have tryed to use VB.net and office
toolkits but I am not happy with that and is now getting to old to
learn the stuff. Also I have problems to orient myselt in the new
Office2007 user interface.
If
B.T.W. I read somewhere on a blog that Microsoft Accountants internally use custom Excel reports generated with VBA (0r SQL T-SQL) at the server level.
is true
I am in good company!
//Gosta
March 8, 2009 at 2:00 pm
The feedback on this article has been very interesting. And good enough that I've been looking at other ways to accomplish what I'm using the Excel Automation for.
So, let me lay out what's going on, and hopefully I'll get suggestions for how to handle this.
This is happening for a small business. They deal with the reselling of international magazines in the US (and the handling of subscribers for the magazine). Their SQL Server is 2008 (recently upgraded from 2000). The excel spreadsheets are reports for each publisher, generated when each issue of their magazine is released. The spreadsheets are uploaded to the company web site for the publishers to log in and download.
When most of these spreadsheets are generated, new data is appended to the existing spreadsheet.
Since the company deals with numerous publishers, obtaining a separate license for each publisher to access SQL would be cost-prohibitive. (As would be upgrading to unlimited licenses.) This is the primary reason for exporting the reports as spreadsheets.
I've looked into inserting into the spreadsheet thru the OpenRowset function. This requires two things:
1. You can't use variables for the parameters, and each publisher has a different path to the file.
2. You must know the name of the worksheet, and some are not the default (or even the same between publishers).
The Excel spreadsheets are not being used for their functions. In fact, the most complicated thing going on is to do some simple math (add, get percentages) on some columns (which can easily be done at the sql level to avoid this), and to format some of the cells (ie. the percentage columns, date columns, etc.).
So, I need to be able to:
1. append data to an existing spreadsheet.
2. must be able to work on spreadsheets based on a variable to the path where they are located, and to the name of the file itself.
3. must be able to work on the first worksheet of that spreadsheet, regardless of it's name.
4. some of the spreadsheets export just one new row. Others do multiple. On at least one, the first row is raw numbers, and the second row is percentages dealing with that number. On another, the first row contains a variable title (which changes for each issue release), and the second contains data for that title.
5. some of the spreadsheets need to have a separate worksheet for some of the data. This is typically done be year, with the most recent year being the first worksheet. (Currently, new worksheets are manually created and arranged manually by the staff as necessary.)
I don't think SSIS or SSRS will be able to handle all of the above requirements. Please correct me if I'm wrong.
I could load all of the data from each type of spreadsheet into a history table, and then just add data to these tables, and from there completely rewrite the spreadsheets. This would take care of the need to append to existing spreadsheets, however the other 4 issues are still present.
So, what other options are there?
Looking forward to reading these...
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
March 9, 2009 at 2:10 pm
I just opened my e-mail and grinned!
While my schedul won't allow me to respond for a couple of days...
This looks like some fun!
I will plan on getting back to this.
March 9, 2009 at 2:31 pm
Wayne,
Please read about data driven subscriptions for Reporting Services. It may help you.
Here is a link to the 3 -page 30 min tutorial:
http://technet.microsoft.com/en-us/library/ms169673.aspx
Tutorial: Creating a Data-Driven Subscription
In the tutorial it shows you how to create a database with the list of the report subscribers and then create a number of your business reports one per subscriber and output them on the file share..
I guess that if you then have to move the reports to the different folders you probably can have a script for that.
Regards,Yelena Varsha
March 12, 2009 at 1:29 pm
I think would be better to handle excel automation from VB.net, C# or a CLR stored procedure rather than use OA procedures.
Viewing 15 posts - 61 through 75 (of 102 total)
You must be logged in to reply to this topic. Login to reply