The OLE DB provider Microsoft ACE OLEDB 12 0 for linked server null

Question

I m trying to run the following statement but am receiving the error messages just below   I have researched answers to no end and none have worked for me   I m running Office 365  64bit    I have loaded the Microsoft Access Database Engine  64bit    This is in Visual Studio 2013 with SSDT as well as SQL Server 2012   I do not have access to changing environment or startup parameters to SQL Server   Any help is appreciated   SELECT   FROM OPENROWSET  Microsoft ACE OLEDB 15 0         Excel 12 0 Database C  Users UserName Folder SomeFile xlsx  HDR NO IMEX 1    Table 1       Msg 7399  Level 16  State 1  Line 1 The OLE DB provider  Microsoft ACE OLEDB 15 0  for linked server   null   reported an error  The provider did not give any information about the error  Msg 7303  Level 16  State 1  Line 1 Cannot initialize the data source object of OLE DB provider  Microsoft ACE OLEDB 15 0  for linked server   null      Here s what I have tried   First  I ran     sp configure  show advanced options   1  RECONFIGURE  GO sp configure  Ad Hoc Distributed Queries   1  RECONFIGURE  GO   Followed by   with no love   EXEC sys sp addsrvrolemember  loginame   N  lt  lt Domain User gt  gt     rolename   N sysadmin   GO  EXEC master dbo sp MSset oledb prop N Microsoft ACE OLEDB 15 0   N AllowInProcess   1  GO  EXEC master dbo sp MSset oledb prop N Microsoft ACE OLEDB 15 0   N DynamicParameters   1  GO    I have changed the code to read Microsoft ACE OLEDB 12 0 as I have seen that as well  still no love   I have also checked permissions of C  Users MSSQLSERVER AppData Local Temp and C Windows ServiceProfiles NetworkService AppData Local which have granted Full Control for the following  System  MSSQLSERVER  and Administrators  Network Service  on the latter    Still no love   Lastly  I have tried changing to the 32bit version of the Microsoft Access Database Engine which has persisted in not working   Help  anyone

User · Answer

There are two crucial nonobvious settings that I've discovered when tuning linked servers on Excel under SQL Server 2014. With those settings, ' FROM OPENDATASOURCE(''Microsoft.ACE.OLEDB.16.0'', ...)' as well as '... FROM [' + @srv_name + ']...data AS xl ...' function properly.

Linked server creation

This is just for context.

DECLARE @DB_NAME NVARCHAR(30) = DB_NAME();
DECLARE @srv_name nvarchar(64) = N'<srv_base_name>@' + @DB_NAME; --to distinguish linked server usage by different databases

EXEC sp_addlinkedserver
    @server=@srv_name,
    @srvproduct=N'OLE DB Provider for ACE 16.0',
    @provider= N'Microsoft.ACE.OLEDB.16.0',
    @datasrc= '<local_file_path>\<excel_workbook_name>.xlsx',
    @provstr= N'Excel 12.0;HDR=YES;IMEX=0'
;

@datasrc: Encoding is crucial here: varchar instead of nvarchar.
@provstr: Verion, settings and sytax are important!
@provider: Specify the provider installed in your SQL Server environment. Available providers are enumerated under Server Objects::Linked Servers::Providers in SSMS's Object Explorer.

Providing access to the linked server under specific SQL Server logins

This is the first crucial setting. Even for SA like for any other SQL Server login:

EXEC sp_addlinkedsrvlogin @rmtsrvname = @srv_name, @locallogin = N'sa', @useself = N'False', @rmtuser = N'admin', @rmtpassword = N''
;

@rmtuser: It should be admin. Actually, there is no any admin in Windows logins on the system at the same time.
@rmtpassword: It should be an empty string.

Providing access to the linked server via ad hoc queries

This is the second crucial setting. Setting Ad Hoc Distributed Queries to 1 is not enough. One should set to 0 the DisallowAdhocAccess registry key explicitly for the driver specified to @provider:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.DEF_INST\Providers\Microsoft.ACE.OLEDB.16.0]
"DisallowAdhocAccess"=dword:00000000

User · Answer

Close SQL Server Management Studio  Type Services msc in the run command to open the services window  Search for SQL Server Service and right click it and select properties  In the Log On Tab  select system account or select your domain ID and Account and password  Once it finds your login name press OK  Now type your login   s passwords in both the fields  Restart the services so that the new changes are applied as shown in figure below  Now start SQL Server Management Studio and try to run the query if still not working try a system restart        or execute the following query   USE  master   GO  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

User · Answer

Removing Read-only CheckBox from C  Users SQL Service account name AppData Local Temp worked for me

User · Answer

This is my error code   OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null   returned message  The Microsoft Access database engine could not find the object  ByStore    Make sure the object exists and that you spell its name and the path name correctly  If  ByStore   is not a local object  check your network connection or contact the server administrator     Msg 7350  Level 16  State 2  Procedure PeopleCounter daily  Line 26  Cannot get the column information from OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null      My problem was the excel file was missing at the path  Just put the file with the correct sheet will do

User · Answer

I had the same issues even after running all those EXEC stored procedure commands that you see on every answer blog  but when I ran SSMS as administrator  the problems went away  Since  I didn t want to do that  I opened Services  went to SQL Server  MyServer   right clicked  selected properties  On the Log On tab   This account   was selected with and listed my SQL Server account password  I instead checked  Local System account  and ticked the  Allow service to interact with desktop   Then I stopped and started the service  Now  I can run the same query as the original poster mentions without running as administrator in SSMS  This probably worked because I am running SQL Server on my home desktop   I d also like to point out that you can use GUI in SSMS to enable adhoc access  and many other options   In SSMS  go to Linked Servers Providers  right click on the provider  and select properties  Then you can just check uncheck the ones you want

User · Answer

For me it was the permission on the file s folder  I had to add all permissions for user  Everyone   Folder properties security -  add user everyone and set all permissions

User · Answer

I had the exact same error message  and tried the suggested solutions in this thread but without success   what solved the problem for me is opening the  xlsx file and saving it as  xls  excel 2003  file   maybe the file was corrupted or in a different format  and saving it again fixed it

User · Answer

This is for my reference  as I encountered a variety of SQL error messages while trying to connect with provider  Other answers prescribe  try this  then this  then this   I appreciate the other answers  but I like to pair specific solutions with specific problems    Error     provider did not give information   Cannot initialize data source object     Error Numbers  7399  7303  Error Detail  Msg 7399  Level 16  State 1  Line 2 The OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null   reported an error     The provider did not give any information about the error   Msg 7303  Level 16  State 1  Line 2 Cannot initialize the data source object   of OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null      Solution  File was open  Close it   Credit   https   stackoverflow com a 29369868 1175496     Error  Access denied   Cannot get the column information     Error Numbers  7399  7350  Error Detail  Msg 7399  Level 16  State 1  Line 2 The OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null   reported an error     Access denied  Msg 7350  Level 16  State 2  Line 2 Cannot get the column information    from OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null      Solution  Give access  Credit   https   stackoverflow com a 27509955 1175496     Error  No value given for one or more required parameters    Cannot execute the query      Error Numbers       7320  Error Detail  OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null   returned message  No value given for one or more required parameters    Msg 7320  Level 16  State 2  Line 2 Cannot execute the query  select  Col A    Col A  FROM  Sheet  against OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null       Solution  Column names might be wrong  Do  Col A  and  Col B  actually exist in your spreadsheet     Error   Unspecified error    Cannot initialize data source object     Error Numbers       7303  Error Detail  OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null   returned message  Unspecified error   Msg 7303  Level 16  State 1  Line 2 Cannot initialize the data source object of OLE DB provider  Microsoft ACE OLEDB 12 0  for linked server   null      Solution  Run SSMS as admin  See this question     Other References  Other answers which suggest modifying properties  Not sure how modifying these two properties  checking them or unchecking them  would help    https   stackoverflow com a 31605038 1175496 http   www aspsnippets com Articles The-OLE-DB-provider-Microsoft Ace OLEDB 12 0-for-linked-server-null aspx https   social technet microsoft com Forums lync en-US bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access forum sqldataaccess 3fcc14f4-420e-4544-be74-eea1e0e78462

User · Answer

Instead of changing the user  I ve found this advise   https   social technet microsoft com Forums lync en-US bb2dc720-f8f9-4b93-b5d1-cfb4f8a8b1cb the-ole-db-provider-microsoftaceoledb120-for-linked-server-null-reported-an-error-access forum sqldataaccess     This might help someone else out - after trying every solution to   trying and fix this error on SQL 64        Cannot initialize the data source object of OLE DB provider    Microsoft ACE OLEDB 12 0  for linked server   null           I found an article here         http   sqlserverpedia com blog sql-server-bloggers too-many-bits         which suggested I give Everyone full permission on this folder        C  Users SQL Service account name AppData Local Temp      And hey presto   My query suddenly burst into life   I punched the air   in delight       Edwaldo

User · Answer

In our case  it helped to add a parameter for SQL Server service    Go to Services msc  select SQL Server Service and open Properties   Choose Startup Parameters and add new parameter    g512  Restart SQL server service

User · Answer

In the SQL Server  try these steps    Open one database  Click in the option Server Object  Click in Linked Servers  Click in Providers  Right click on Microsoft ACE OLEDB 12 0 and click Properties  Uncheck all the options and close

User · Answer

With SQL 2014  I changed the SQL Server Service  MSSQL  to run as LocalSystem  This solved the problem for me   It used to work as NT SERVICE MSSQL MSSQL fine under 2008  from what I remember

User · Answer

Make sure the Excel file isn t open

User · Answer

Here is specifically what worked for me only when the Excel file being queried was not open and when running the SQL Server Service as me  as a user that has access to the file system    I see pieces of my answer already given elsewhere  so I apologize for any redundancy  but for the sake of a more succinct answer   USE  master  GO  EXEC sp configure  Show Advanced Options   1 RECONFIGURE GO  EXEC sp configure  Ad Hoc Distributed Queries   1 RECONFIGURE GO  EXEC sp MSSet oledb prop N Microsoft ACE OLEDB 12 0   N AllowInProcess   1 GO  EXEC sp MSSet oledb prop N Microsoft ACE OLEDB 12 0   N DynamicParameters   1 GO   SELECT    FROM OPENROWSET  Microsoft ACE OLEDB 12 0                    Excel 12 0 Database C  MyExcelFile xlsx                    SELECT   FROM  MyExcelSheetName

User · Answer

Exec sp configure  show advanced options   1  RECONFIGURE  GO  Exec sp configure  Ad Hoc Distributed Queries   1  RECONFIGURE  GO  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  Insert into OPENDATASOURCE  Microsoft ACE OLEDB 12 0   Data Source C  upload test xlsx Extended Properties Excel 12 0      Sheet1   SELECT ColumnNames FROM Your table -- Sheet Should be already Present along with headers  EXEC master dbo sp MSset oledb prop N Microsoft ACE OLEDB 12 0    N AllowInProcess    0  GO EXEC master dbo sp MSset oledb prop N Microsoft ACE OLEDB 12 0    N DynamicParameters    0  GO  Exec sp configure  Ad Hoc Distributed Queries   0  RECONFIGURE  GO  Exec sp configure  show advanced options   0 RECONFIGURE  GO

User · Answer

I m running SQL Server 2014 64 bit on Windows 10   I tried everything  the thing that made it work was   EXEC sp MSset oledb prop N Microsoft ACE OLEDB 12 0   N AllowInProcess   0   I don t know why the AllowInProcess turned off makes it work but that was the key in my case   Thank you for the suggestion of turning all the options off on the linkserver

User · Answer

http   www aspsnippets com Articles The-OLE-DB-provider-Microsoft Ace OLEDB 12 0-for-linked-server-null aspx  This solves the issue  For some reason SQL Server does not like the default MSSQLSERVER account  Switching it to a local user account resolves the issue

User · Answer

For me  these two things helped on different occasions   1  If you ve just installed the MS Access runtime  reboot the server  Bouncing the database instance isn t enough   2  As well as making sure the Excel file isn t open  check you haven t got Windows Explorer open with the preview pane switched on - that locks it too

User · Answer

In my case this problem was occuring because i was accessing the file from shared folder by using computerName  OPENROWSET   Microsoft Jet OLEDB 4 0     Excel 8 0 Database   ntpc0100 MysharedFolder KPI tempData VariablePayoutDetails xls  IMEX 1            Insted of using computerName ntpc0100 whatever your machine name  you should specify IPaddress of your machine  for example -  OPENROWSET   Microsoft Jet OLEDB 4 0     Excel 8 0  Database   193 34 23 200 MysharedFolder KPI tempData KPIVariablePayoutDetails xls

[sql] The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Linked server creation

Providing access to the linked server under specific SQL Server logins

Providing access to the linked server via ad hoc queries

Examples related to sql

Examples related to visual-studio-2013

Examples related to openrowset