我尝试在SSMS中访问Excel文件。搜索互联网后,我无法正常工作。
这是我所做的:

我的环境:

Windows 7(64bit) SP 1,
Microsoft SQL Server 2008 (SP3) - 10.0.5500.0 (X64)
Office 2010 Pro Plus with Access installed(32 bit)
  • 尝试更改OLE的配置,例如:
    exec sp_configure 'Advanced', 1
    RECONFIGURE
    
    exec sp_configure 'Ad Hoc Distributed Queries', 1
    RECONFIGURE
    
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
    EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
    
  • 运行查询:
    SELECT * FROM OPENROWSET('MICROSOFT.ACE.OLEDB.12.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')
    

    或者
    SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Text;Database=C:\Temp\;','SELECT * FROM [test.csv]')
    

  • 对于这两种情况,我都会收到如下错误消息:
    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.
    

    或者
    Msg 7308, Level 16, State 1, Line 1
    OLE DB provider 'MICROSOFT.ACE.OLEDB.12.0' cannot be used for distributed queries because the provider is configured to run in single-threaded apartment mode.
    

    然后,我检查了SQL Server上的链接服务器,默认情况下,运行系统sp时有10个提供程序:
    EXEC master.dbo.sp_MSset_oledb_prop
    
    SQLOLEDB
    MSOLAP
    SQLNCLI11
    ADsDSOObject
    SQLNCLI
    SQLNCLI10
    Search.CollatorDSO
    MSDASQL
    MSDAOSP
    MSIDXS
    

    如何解决这个问题?
    我怎么知道MICROSOFT.ACE.OLEDB.12.0MICROSOFT.JET.OLEDB.4.0可用于SQL Server?

    最佳答案

    对于扩展名为.xlsx的文件类型,请使用“Excel 12.0”或“Excel 12.0 Xml”而不是Excel 9.0

    SELECT *
    FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\Temp\Test.xlsx;', 'SELECT * FROM [Location1$]')
    

    如果要连接到Microsoft Office Excel数据,请根据Excel文件类型添加OLEDB连接字符串的适当扩展属性:
    File Type (extension)                               Extended Properties
    ---------------------------------------------------------------------------------
    Excel 97-2003 Workbook (.xls)                       "Excel 8.0"
    Excel 2007-2010 Workbook (.xlsx)                    "Excel 12.0 Xml"
    Excel 2007-2010 Macro-enabled workbook (.xlsm)      "Excel 12.0 Macro"
    Excel 2007-2010 Non-XML binary workbook (.xlsb)     "Excel 12.0"
    

    09-09 20:30
    查看更多