SQL Server 2012使用OPENROWSET错误查询Access 2007数据 [英] SQL Server 2012 querying Access 2007 data using OPENROWSET error
问题描述
我想从与SQL Server 2012实例位于同一台计算机上的Microsoft Access 2007数据库中查询Management Studio中的数据.我不想使用链接服务器来执行此操作，因为用户可以选择不同的Access数据库.我正在按照 technet 以及我已阅读的其他来源中的说明进行操作使用OPENROWSET作为执行我想要的操作的正确方法，但是当我在Management Studio中执行此操作时...
I would like to query data in Management Studio from a Microsoft Access 2007 database located on the same machine as my SQL Server 2012 instance. I do NOT want to use a linked server to do this as different Access databases can be chosen by the user. I am following the directions found on technet and other sources I have read said to use OPENROWSET as the proper way to do what I want, but when I execute this in Management Studio...
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Users\oliver.klosoff\Desktop\New folder\41000-13-0085 Consolidated Killers LLC.mdb';
'admin';'',tblTtlHrsFringes);
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Users\oliver.klosoff\Desktop\New folder\41000-13-0085 Consolidated Killers LLC.mdb';
'admin';'',tblTtlHrsFringes);
...我收到以下错误:
...I get the error below:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
数据库没有为admin用户设置密码，并且admin用户具有读取此表的权限. Access 2007 32位和SQL Server实例均安装在64位计算机上.我相信SQL Server可以访问数据库文件，因为当我执行以下命令时得到1:
The database does not have a password set for the admin user, and the admin user has permissions to read this table. Access 2007 32bit is installed on the machine, which is 64 bit, as well as the SQL Server instance. I believe that SQL Server can access the database file because when I get 1 when I execute this:
DECLARE @out INT
EXEC master.dbo.xp_fileexist 'C:\Users\oliver.klosoff\Desktop\New folder\41000-13-0085 Consolidated Killers LLC.mdb', @out OUTPUT
SELECT @out`
有没有办法做我想完成的事情?
Is there a way to do what I am trying to accomplish?
推荐答案
这应该有效
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO
USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0','Data Source="C:\Employees.accdb"')...tblEMPS;
这篇关于SQL Server 2012使用OPENROWSET错误查询Access 2007数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
