注册 | 登录

解决SQL Server export to Excel with OPENROWSET

itPublisher 分享于



推荐:整理sql server的opendatasource OpenRowSet 连接excel的错误

一 t-sql  select * from opendatasource  ( 'Microsoft.Jet.OLEDB.4.0',   'Data Source="c:/test2.xls";User ID=Admin;Password=;Extended properties=Excel 8

I am successfully exporting to excel with the following statement:

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;Database=C:\template.xls;', 
'SELECT * FROM [SheetName$]') 
select * from myTable

Is there any standard way to use this template specifying a new name for the excel sheet so that the template never gets written to or do I have to come up with some work-around?

What's the best way to do this in people experience?

sql sql-server excel export-to-excel openrowset
edited May 9 '16 at 13:03 shA.t 9,550 4 20 47 asked May 26 '09 at 10:11 JohnIdol 24.9k 45 136 221      Oh lucky man. I've been already spending 2 hours trying to run this and getting all kind of errors. (using 2008SP2) –  iDevlop Oct 6 '15 at 14:24


2 Answers


You'd have to use dynamic SQL. OPENROWSET etc only allows literals as parameters.

DECLARE @myfile varchar(800)

SET @myfile = 'C:\template.xls'

insert into OPENROWSET(''Microsoft.Jet.OLEDB.4.0'', 
''Excel 8.0;Database=' + @myfile + ';'', 
''SELECT * FROM [SheetName$]'') 
select * from myTable

Remember: the path is relative to where SQL Server is running

推荐:SQL SERVER使用OpenRowset,、OpenDataSource函数导入、导出数据到Excel 的几种方法(整理)

一、如我在D盘下有D:/物件编码.xls文件,有工作表名Sheet是中文命名为‘办公用品编码’,‘零件编码’。        select * into #temp  from opendatasource('Mic

edited May 9 '16 at 13:04 shA.t 9,550 4 20 47 answered May 26 '09 at 11:25 gbn 286k 43 405 505      this is good - but I'd still have to put the template there in the first place so I cannot come up with names in the SQL, I'd have to programmatically create the spreadsheet first. –  JohnIdol May 26 '09 at 12:01      Ah I get you. No, you can't manipulate Excel that way in T-SQL. Perhaps a CLR stored proc? –  gbn May 26 '09 at 13:19      never worked with CLR SPs - but I guess it's easy enough - any good sample to get me going? –  JohnIdol May 26 '09 at 14:02      Sorry... not used them. The "SQLCLR" tag has 31 related questions. –  gbn May 26 '09 at 14:12      I'll clarify.. our corporate build from DB engineering says no –  gbn May 26 '09 at 19:50


Couldn't you make a copy of your template first, then pass the copy's filename into OPENROWSET?

answered Nov 28 '09 at 0:59 Seth Ladd 18.7k 3 55 139


推荐:Export data from SQL Server to Excel

Export data from SQL Server to Excel Written By: Edgewood Solutions Engineers -- 3/16/2007 -- 10 comments      Stay informed - get the n








您的注册邮箱: 修改

重新发送激活邮件 进入我的邮箱