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