Время создания: 26.12.2018 18:55
Текстовые метки: Allow_inprocess
Раздел: !Закладки - SQL Server
SQL Server 2012 querying Access 2007 data using OPENROWSET error
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...
'C:\Users\oliver.klosoff\Desktop\New folder\41000-13-0085 Consolidated Killers LLC.mdb';
...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)".
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
Is there a way to do what I am trying to accomplish?
sql-server ms-access sql-server-2012 ms-access-2007
shareimprove this question
edited Nov 9 '14 at 14:43
asked Jan 28 '14 at 18:00
add a comment
Finally, after several unsuccessful attempts to have SQL Server "talk to" an Access database – either as a "Linked Server" in SSMS or via OPENROWSET() in T-SQL – I found this blog post that offered the following three (3) suggestions.
Tweak #1: OLE DB Provider settings
The OLE DB Provider for ACE (or Jet) must have the "Dynamic parameter" and "Allow inprocess" options enabled. In SSMS, open the
Server Objects > Linked Servers >Providers
branch, right-click "Microsoft.ACE.OLEDB.12.0" (or "Microsoft.Jet.OLEDB.4.0"), choose "Properties", and ensure that those options are selected:
Так же в этом разделе: