December 8, 2008 at 9:18 am
sammesel (12/8/2008)
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
At the bottom of the article is a references section. This has a sql script that puts all of this into stored procedures, and a stored procedure that runs everything, putting various data into a spreadsheet (in various format settings) and saving the spreadsheet to the root of your C: drive.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 8, 2008 at 10:52 am
I saw it, and I actuallly ran the code (had to do minor adjustments due case-sensitivity of my SQL installation here). But the code does not retrieve data from any table.. it is getting a date or a number...
Would you be able to show me how to execute a SELECT or a SP that returns ROWs?
thanks in advance
December 8, 2008 at 10:58 am
sammesel (12/8/2008)
I saw it, and I actuallly ran the code (had to do minor adjustments due case-sensitivity of my SQL installation here). But the code does not retrieve data from any table.. it is getting a date or a number...Would you be able to show me how to execute a SELECT or a SP that returns ROWs?
thanks in advance
You would have to retrieve the data into variables, then pass those variables on to the procedures.
ie.
declare @Title nvarchar(8), @FirstName nvarchar(50), @LastName nvarchar(50), @Department nvarchar(50), @StartDate datetime
select
@Title = Title,
@FirstName = FirstName,
@LastName = LastName,
@Department = Department,
@StartDate = StartDate
from AdventureWorks.HumanResources.vEmployeeDepartment
where EmployeeID = 5
Then write these variables to the spreadsheet...
execute dbo.spUtl$ExcelCellGet @xlWorkSheet, 1, 1, @xlCell OUTPUT
if @xlCell is not null execute @rs = dbo.spUtl$OADestroy @xlCell
execute dbo.spUtl$ExcelCellSetValue @xlCell, @Title
if @xlCell is not null execute @rs = dbo.spUtl$OADestroy @xlCell
execute dbo.spUtl$ExcelCellGet @xlWorkSheet, 1, 2, @xlCell OUTPUT
execute dbo.spUtl$ExcelCellSetValue @xlCell, @FirstName
if @xlCell is not null execute @rs = dbo.spUtl$OADestroy @xlCell
execute dbo.spUtl$ExcelCellGet @xlWorkSheet, 1, 3, @xlCell OUTPUT
execute dbo.spUtl$ExcelCellSetValue @xlCell, @LastName
if @xlCell is not null execute @rs = dbo.spUtl$OADestroy @xlCell
execute dbo.spUtl$ExcelCellGet @xlWorkSheet, 1, 4, @xlCell OUTPUT
execute dbo.spUtl$ExcelCellSetValue @xlCell, @Department
if @xlCell is not null execute @rs = dbo.spUtl$OADestroy @xlCell
execute dbo.spUtl$ExcelCellGet @xlWorkSheet, 1, 5, @xlCell OUTPUT
execute dbo.spUtl$ExcelCellSetValue @xlCell, @StartDate
-- set the date format
execute dbo.spUtl$ExcelCellSetFormat @xlCell, 'd-mmm'
if @xlCell is not null execute @rs = dbo.spUtl$OADestroy @xlCell
HTH,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 8, 2008 at 11:07 am
understood, but not practical at all...
Thanks Wayne
December 8, 2008 at 11:24 am
Joseph M. Steinbrunner (12/5/2008)
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.
First, let me say that this is a well done article. I will join with others in saying that using Ole automation with excel is rarely the best answer, but it is a good technique to know and this article lays it out well. I did similar things when I was starting as a sql developer before I started learning about some of the better ways.
Joseph, I agree that it has significant drawbacks, including the need to have excel installed on the server. With that said, leaving excel hanging is not one of them if the t-sql code is written properly. With a combination of try-catch blocks, error checking, and ending the procedure with an unconditional close of the excel process it generates you can make it so it is highly unlikely excel will every be left hanging. But there are plenty of other reasons to look at other options before using this one.
---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
December 8, 2008 at 12:01 pm
"First, let me say that this is a well done article. I will join with others in saying that using Ole automation with excel is rarely the best answer, but it is a good technique to know and this article lays it out well. I did similar things when I was starting as a sql developer before I started learning about some of the better ways.
Joseph, I agree that it has significant drawbacks, including the need to have excel installed on the server. With that said, leaving excel hanging is not one of them if the t-sql code is written properly. With a combination of try-catch blocks, error checking, and ending the procedure with an unconditional close of the excel process it generates you can make it so it is highly unlikely excel will every be left hanging. But there are plenty of other reasons to look at other options before using this one. "
As have been show using T-sql to automate Excel is one way to produce Excel reports but It is
somewhat complicated and not easy to debug. But that is not an excuse for not trying to using OLE automation on a server because you can make it work. Look at Excel and what's inside like VBA. And if you don't like Excel to be installed at the server at all you can let the user :)run Excel on his/her client machine fetching data from the server /with a built in macro.
With OLE db and ADO you can connect to the database and run queries, procedures to populate Excelsheets or insert data from Excel and you can program all the methods etc in Excel.
I would be glad to share my experiences if you would like that.:)
//Gosta M
December 9, 2008 at 4:02 am
Gosta,
I agree that automating the client was one of the solutions I had in mind, that's how I started (using SQL / doing BI stuff) as well!
I don't think this is the appropiate place to elaborate on using VBA, and therefore didn't mention that.
@several comments:
I assumed that by installing the Office PIA, you are not installing full-blown excel, but still can use the excel object model and automation.
As the comment about "not willing to install excel on the server" was repeated several times after I mentioned the PIA, it may be that my assumption is wrong.
Are there arguments against using the PIA as well, or is the PIA not suitable for the scenario Wayne worked out?
I would be glad to see some thoughts on that.
Peter Rijs
BI Consultant, The Netherlands
December 9, 2008 at 4:43 am
"Gosta,
I agree that automating the client was one of the solutions I had in mind, that's how I started (using SQL / doing BI stuff) as well!
I don't think this is the appropiate place to elaborate on using VBA, and therefore didn't mention that.
@several comments:
I assumed that by installing the Office PIA, you are not installing full-blown excel, but still can use the excel object model and automation. "
Peter
I can accept that this forum is not appropriate place to elaborate VBA but what forum is more suitable because it works? I am also a member of an Excel forum and there they try to solve everything in Excel even if a database is the right place.
I have tryed to use VB.net with Office PIAs but I am not so happy. I am still much more productive with VBA.
What are are your experiences out there?
/Gosta
December 9, 2008 at 6:54 am
I'm with DiverKas on the theological argument that goes with this article.
If you were a 'Solutions Provider' and implemented this unsupported technique on a fee paying client site you would also be handing them a big stick to beat you with when it goes pear shaped. You would lose credibility and maybe even the client and subsequent fees.
Best to play with it in your own back yard.
December 9, 2008 at 8:05 am
Peter Rijs (12/9/2008)
Gosta,I assumed that by installing the Office PIA, you are not installing full-blown excel, but still can use the excel object model and automation.
As the comment about "not willing to install excel on the server" was repeated several times after I mentioned the PIA, it may be that my assumption is wrong.
Are there arguments against using the PIA as well, or is the PIA not suitable for the scenario Wayne worked out?
I would be glad to see some thoughts on that.
In brief, it states: "In Microsoft Office 2003, you can work with COM objects exposed by the Office applications by using a primary interop assembly (PIA). "
Thus, you are still automating through the use of COM objects. In my mind you inherit all of the things that are bad about automating Office on a server, without the licensing issues.
A better solution would be to use Crystal, ActiveReports or any other tool that does not require Excel, to export and format the data in an Excel format. The sp_OA methods could still be employed, just like the authors article demonstrates, with more control, less headache and risk.
December 9, 2008 at 8:14 am
DiverKas (12/9/2008)
A better solution would be to use Crystal, ActiveReports or any other tool that does not require Excel, to export and format the data in an Excel format. The sp_OA methods could still be employed, just like the authors article demonstrates, with more control, less headache and risk.
Do any of these tools allow you to add a row to an existing spreadsheet? It's my understanding that these can only export to essentially a new sheet.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
December 9, 2008 at 8:15 am
Just to put some oil on the fire... I just started working with SSRS and it beats the crap out of manually making excel files. Since it does not cost anything more to have RS installed (assuming you can put it on the same server as the db engine), then I say screw the pain of developping those reports manually.
Awesome article btw :P.
December 9, 2008 at 8:19 am
WayneS (12/9/2008)
DiverKas (12/9/2008)
A better solution would be to use Crystal, ActiveReports or any other tool that does not require Excel, to export and format the data in an Excel format. The sp_OA methods could still be employed, just like the authors article demonstrates, with more control, less headache and risk.Do any of these tools allow you to add a row to an existing spreadsheet? It's my understanding that these can only export to essentially a new sheet.
A simple solution would be to make a history reports that saves the previous reports totals and redisplays them... then the export becomes a breeze.
Or the user can always copy and paste the new data in the old spreadsheet, it's not a big pain in the *** to do.
December 9, 2008 at 2:00 pm
A simple solution would be to make a history reports that saves the previous reports totals and redisplays them... then the export becomes a breeze.
Or the user can always copy and paste the new data in the old spreadsheet, it's not a big pain in the *** to do.
I've had a few high-up users that I couldn't tell to C&P, not the least because they have not discovered Ctrl+C yet. Oh, I've stories...
My first thought on being faced with a solution like that would be to create a nicely formatted spreadsheet with a defined data area populated from a query that calls a stored procedure on SQL (I think you can) or selects from a view. Data would refresh on opening the spreadsheet.
As far as historical rows, I am not sure what the problem is. Couldn't the query retrieve all data records, including history? If not, historical data should probably be maintained on the server - if they are valuable, why trust Excel?
But if I had a production SSRS server, which I currently don't, then I agree with DiverKas - I would create a report there and let the user save it as Excel.
December 10, 2008 at 10:34 am
I will first say this is a decent article and expresses a concept that some people can use. However a few things I would note.
1) If you loose the hanle to excel during the process this will leave an instance of Excel open and in many cases will prevent you from being able to connect to the file afterwards as it is locked. This may also cause issues with other items.
2) You have to install MS Excel on the server to work properly with the objects and you may have to spend a considerable amount of time working out permission issues. And afterwards you end up creating added security risks.
3) Also you should demonstrate clearing a worksheet thru deleting rows versus clearing rows. Method you choose can cause data to not appear until after the original datas last row. Found many a worksheet with large number of blanks before I figured out what was happening. And besides DTS can append rows to Excel files.
4) Keep in mind Excel has a limit of 64000 rows (this may have changed in Excell 2007 but can't remember)
I might suggest using DTS for readability in a VBScript package but this too can suffer from the same issues noted in 1, however you can have a lot more error handlign in this way.
Better yet one of the other ways mentioned or even using Reporting Services.
Viewing 15 posts - 46 through 60 (of 102 total)
You must be logged in to reply to this topic. Login to reply