--Step 1
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE ;
EXEC sp_configure 'Ad Hoc Distributed Queries' , 1;
RECONFIGURE ;
GO
--Step 2
INSERT INTO Post (ID,Txt) SELECT Post_ID,Post_cont FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=YES;DATABASE=H:\INFO 341\Assignment 2\EventA.xlsx',sheet2$)但是报错 Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.为什么啊,我的系统是win7 64位 , SQL server 2008
EXEC sp_configure 'show advanced options' , 1;
RECONFIGURE ;
EXEC sp_configure 'Ad Hoc Distributed Queries' , 1;
RECONFIGURE ;
GO
--Step 2
INSERT INTO Post (ID,Txt) SELECT Post_ID,Post_cont FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 8.0;HDR=YES;DATABASE=H:\INFO 341\Assignment 2\EventA.xlsx',sheet2$)但是报错 Msg 7308, Level 16, State 1, Line 1
OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.为什么啊,我的系统是win7 64位 , SQL server 2008
select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=d:\temp\ActionResult.xls','select * from [sheet1$]')
--使用OpenRowSet试试,
或者把你打开的excel程序全部关闭掉,让查询excel的时候仅有一个连接---SQL Server to Excel的。
http://forums.asp.net/t/1736766.aspx/1
INSERT INTO Post (ID,Txt) SELECT Post_ID,Post_cont FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'这里改成12.0看看
,'Excel 8.0;HDR=YES;DATABASE=H:\INFO 341\Assignment 2\EventA.xlsx',sheet2$)方法2:
把你的excel文件另存为03版本再执行
方法二也是一样的报错 OLE DB provider 'MICROSOFT.JET.OLEDB.4.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
sp_configure 'Ad Hoc Distributed Queries',1
GO
RECONFIGURE;
go
INSERT INTO Post(ID,Txt)
SELECT ID,Txt FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=H:\INFO 341\Assignment 2\EventA.xlsx','select ID,Txt from [sheet2$]')红色加粗部分有问题啊,.xlsx貌似是office 2007的后缀吧?Microsoft.Jet.OLEDB.4.0貌似只支持office 2003版本的。
解决:你得有office 2003,其次把你最后的sheet2$改为'select ID,Txt from [sheet2$]'。
The OLE DB provider "MICROSOFT.JET.OLEDB.12.0" has not been registered.