Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
stephenbow
Starting Member
2 Posts |
 Posted - 2011-10-29 : 11:57:52
|
| I can no longer create an Excel workbook in T-Sql and save it to disk. My script has worked for years, and not it fails when I try to save the file to disk. Must be permissions, but which?This is the script. Please can someone tell me why it no longer works?DECLARE @objExcel Int,@objWorkBook Int,@objWorkSheet Int,@HR Int --Hold value that says if EXEC command worked or not---------------------------------------------------------------------------------------------------------Create Excel applicationEXEC @HR = sp_OACreate 'Excel.Application', @objExcel OUTprint @HR-----------------------------------------------------------------------create a workbook EXEC @HR = sp_OAGetProperty @objExcel, 'WorkBooks.Add', @objWorkBook OUT print @HR-----------------------------------------------------------------------add a worksheet to the workbookEXEC @HR = sp_OAGetProperty @objWorkBook, 'worksheets.Add', @objWorkSheet OUT print @HR-----------------------------------------------------------------------Save spreadsheet to nominated folderEXEC @HR = master.dbo.sp_oaMethod @objWorkbook ,'SaveAs',null,'C:\temp.xls'print @HR |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2011-10-29 : 12:16:08
|
| whats the error you're getting? Can you provide more details on the error?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
paultech
Yak Posting Veteran
79 Posts |
Posted - 2011-10-30 : 05:54:00
|
Ok,Please look to me Carefullyquote:
Instead of creating the Excel spreadsheet with OLEDB One can use thesp_makewebtask Users must have SELECT permissions to run a specified query and CREATE PROCEDURE permissions in the database in which the query will run. The SQL Server account must have permissions to write the generated HTML document to the specified location. Only members of the sysadmin server role can impersonate other users.*/ sp_makewebtask @outputfile = 'c:\CambridgePubsHTML2.xls', @query = 'Select * from ##CambridgePubs', @colheaders =1, @FixedFont=0,@lastupdated=0,@resultstitle='Cambridge Pubs', @dbname ='MyDatabaseName' /* This is fine for distributing information from databases but no goodif you subsequently want to open it via ODBC.*/
For more details just go tohttp://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/#sixthpaul Tech |
|
|
|
stephenbow
Starting Member
2 Posts |
Posted - 2011-10-30 : 12:10:36
|
| The OLE Automation error number is -2146827284 which translates into thisMicrosoft ExcelUnable to get the SaveAs property of the Workbook class |
|
|
|
|
|
|
|
|
OLE Automation