Microsoft.ACE.OLEDB.12.0" for linked server - Error

Question

Microsoft.ACE.OLEDB.12.0" for linked server - Error

PJ_SQL

SSCertifiable

Points: 6699
More actions
April 16, 2023 at 1:45 pm

#4174751

Hello!
This suddenly stopped working and giving this error message below:
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)".
Tried this:
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
Rebooted the server.

Thanks.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 1

PJ_SQL wrote:

Hello!
This suddenly stopped working and giving this error message below:
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)".
Tried this:
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
Rebooted the server.

Why do so many people do this. By your own words, this WAS working and now it's not. Why did you even think of changing the configuration for it? Do you realize that you could be breaking everyone else's stuff by doing that?

The error you got is caused by the file state, the file location, or the file privs.

In other words, if someone has the file open, you'll get that error. If someone moved the file or it's not there, you'll get that error. If someone added a password to the file, you'll get that error.

Try the code again. If it still gives you the error, then go check on the 3 things I talked about above before you do anything else. YOU might even be the person causing the issue... do YOU have the file open?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PJ_SQL SSCertifiable Points: 6699 More actions · Answer 2

Those configurations were set to 1 before as well, file is not open and it lets me make a copy of it and even with different name it gives the same error.

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 3

PJ_SQL wrote:

Those configurations were set to 1 before as well, file is not open and it lets me make a copy of it and even with different name it gives the same error.

Are you on a different machine than when it was working? Could be the ol' "Kerberos Double-Hop" getting to you.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PJ_SQL SSCertifiable Points: 6699 More actions · Answer 4

PJ_SQL

SSCertifiable

Points: 6699

April 16, 2023 at 3:12 pm

#4174773

I am in same machine

Jeff Moden SSC Guru Points: 1003875 More actions · Answer 5

PJ_SQL wrote:

I am in same machine

Ok... so what changed?

And, I'm not trying to be difficult here. It's just that these type of failures don't just suddenly appear after working for a long time. Something had to change and once we know what that is, it's pretty like to be the problem and much more likely to be fixed.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)