我正在执行这个openrowset函数:
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Users\JCPABALAN\Desktop\Data Migration\ListOfDiscards.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]')
但是它给了我以下错误:
OLE DB提供程序"Microsoft.ACE.OLEDB.12.0"为链接服务器"(null)"返回消息"The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."。 Msg 7350,级别16,状态2,行1 无法从链接服务器"(null)"的OLE DB提供程序"Microsoft.ACE.OLEDB.12.0"获取列信息。
我已经将
Ad Hoc Distributed Queries
设置为1,并安装了Microsoft ACE
,并执行了此行代码:EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
但它仍然无法正常工作。