2021腾讯云限时秒杀，爆款1核2G云服务器298元/3年！（领取2860元代金券），
地址：https://cloud.tencent.com/act/cps/redirect?redirect=1062

2021阿里云最低价产品入口+领取代金券(老用户3折起)，
入口地址：https://www.aliyun.com/minisite/goods

推荐：整理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
2

解决方法

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

DECLARE @myfile varchar(800)

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

EXEC ('
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 MSSQLTips.com n