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$])
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'<<Domain\User>>', @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?
This question is related to
sql
visual-studio-2013
openrowset
Make sure the Excel file isn't open.
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
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.
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
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.
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'....);
Removing Read-only CheckBox from C:\Users\SQL Service account name\AppData\Local\Temp worked for me.
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.
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$]')
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.
Instead of changing the user, I've found this advise:
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
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.
In the SQL Server, try these steps:
Server Object.Linked Servers.Providers.Microsoft.ACE.OLEDB.12.0 and click Properties.In our case, it helped to add a parameter for SQL Server service:
Services.msc, select SQL Server Service
and open Properties. Startup Parameters and add new
parameter –g512 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
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
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.
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.
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.
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.
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.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.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
Source: Stackoverflow.com