OpenRowSet is giving error: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
-
Hi all, I am trying to read from an Excel file using OpenRowSet, I am getting the above error, as I found in the google I tried the below script.
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
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
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GOStill no luck it would be very helpful if anybody can help me out, I am also trying different options from online but somehow its not working. Here is the script I am trying.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TestFiles\1-085 Cancelled with Cash-Report 129_2017-10-03.xls;',
'SELECT * FROM [Sheet1$]')And the error message full is:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".This error is happening even if I open VS on the server itself and run this openrowset script and even if I run from the client machine like my local machine, in both cases the openrowset is failing any help can be greatly appreciated friends. Thanks in advance friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi all, I am trying to read from an Excel file using OpenRowSet, I am getting the above error, as I found in the google I tried the below script.
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
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
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GOStill no luck it would be very helpful if anybody can help me out, I am also trying different options from online but somehow its not working. Here is the script I am trying.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TestFiles\1-085 Cancelled with Cash-Report 129_2017-10-03.xls;',
'SELECT * FROM [Sheet1$]')And the error message full is:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".This error is happening even if I open VS on the server itself and run this openrowset script and even if I run from the client machine like my local machine, in both cases the openrowset is failing any help can be greatly appreciated friends. Thanks in advance friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Did you try: [OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f663ab8-c75d-419b-a4bf-bb6c0f5b84c4/ole-db-provider-microsoftaceoledb120-for-linked-server-null-returned-message-unspecified?forum=transactsql) [OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null) - Google-Suche](https://www.google.de/search?q=OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)"+returned+message+"Unspecified+error".+Msg+7303%2C+Level+16%2C+State+1%2C+Line+1+Cannot+initialize+the+data+source+object+of+OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)&oq=OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)"+returned+message+"Unspecified+error".+Msg+7303%2C+Level+16%2C+State+1%2C+Line+1+Cannot+initialize+the+data+source+object+of+OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)&aqs=chrome..69i57&sourceid=chrome&ie=UTF-8)
-
Did you try: [OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".](https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6f663ab8-c75d-419b-a4bf-bb6c0f5b84c4/ole-db-provider-microsoftaceoledb120-for-linked-server-null-returned-message-unspecified?forum=transactsql) [OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error". Msg 7303, Level 16, State 1, Line 1 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null) - Google-Suche](https://www.google.de/search?q=OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)"+returned+message+"Unspecified+error".+Msg+7303%2C+Level+16%2C+State+1%2C+Line+1+Cannot+initialize+the+data+source+object+of+OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)&oq=OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)"+returned+message+"Unspecified+error".+Msg+7303%2C+Level+16%2C+State+1%2C+Line+1+Cannot+initialize+the+data+source+object+of+OLE+DB+provider+"Microsoft.ACE.OLEDB.12.0"+for+linked+server+"(null)&aqs=chrome..69i57&sourceid=chrome&ie=UTF-8)
I tried all of them nothing worked for me so far. It is working but when I remote into the Server and run this OpenRowSet statement there but when I run openrowset on my Local Machine its giving me the same error, any help would be greatly helpful. Thanks in advance.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
I tried all of them nothing worked for me so far. It is working but when I remote into the Server and run this OpenRowSet statement there but when I run openrowset on my Local Machine its giving me the same error, any help would be greatly helpful. Thanks in advance.
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
-
Hi all, I am trying to read from an Excel file using OpenRowSet, I am getting the above error, as I found in the google I tried the below script.
USE master
GO
sp_configure 'show advanced options', 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE WITH OverRide
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
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GOStill no luck it would be very helpful if anybody can help me out, I am also trying different options from online but somehow its not working. Here is the script I am trying.
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\TestFiles\1-085 Cancelled with Cash-Report 129_2017-10-03.xls;',
'SELECT * FROM [Sheet1$]')And the error message full is:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".This error is happening even if I open VS on the server itself and run this openrowset script and even if I run from the client machine like my local machine, in both cases the openrowset is failing any help can be greatly appreciated friends. Thanks in advance friends. Thanks, Abdul Aleem "There is already enough hatred in the world lets spread love, compassion and affection."
Here's another post on the same issue: OLE DB provider "MICROSOFT.JET.OLEDB.4.0" for linked server "(null)" returned message "Unspecified error". – Tips and tricks from a Developer Support perspective.[^] It definitely sounds like a permissions issue on the "temp" directory used by the SQL service account.
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
