The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"
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.
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
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