how change SSMS connection to remote server and run OPENROWSET on ACE.OLEDB.12.0

Question

how change SSMS connection to remote server and run OPENROWSET on ACE.OLEDB.12.0

James R. Hill 1

How to change connection via SSMS to different server and run : SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 Xml;HDR=YES;Database=.\fileshare name\excelsheet.xlsx :
this returns error = Cannot initialize the data source object of OLE DB provider

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-07T20:55:41.14+00:00

To change the connection for a query window in SSMS, you can right-click and select Connect->Change Connection. As for the error, is the ACE provider installed on the machine where SQL Server is running?
James R. Hill 1 Reputation point

2023-04-07T21:14:45.8866667+00:00

Yes it is, and I understand how to change connection, but the query fails when I do change it. Thanks
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-07T21:18:52.33+00:00

It's not uncommon that a query that runs on one instance of SQL Server does not run on a different instance. In your case, it could be that the ACE provider is installed on the first instance you run on, but not the second one.

If this answer is not satisfactory, you need to give more details. Not the least why you expect the connection change not to matter.
James R. Hill 1 Reputation point

2023-04-07T22:10:11.78+00:00

all servers have the provider, the query runs successfully locally on all of them, but only locally, when the connection changes it fails: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\path\path\excel\file.xlsx'. It is already opened exclusively by another user, or you need permission to view and write its data.". This is what I don't understand.
Seeya Xi-MSFT 16,586 Reputation points

2023-04-11T08:01:13.23+00:00

Hi @James R. Hill, We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated. Best regards, Seeya

2 answers

Your answer

Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-07T20:55:41.14+00:00

To change the connection for a query window in SSMS, you can right-click and select Connect->Change Connection. As for the error, is the ACE provider installed on the machine where SQL Server is running?
James R. Hill 1 Reputation point

2023-04-07T21:14:45.8866667+00:00

Yes it is, and I understand how to change connection, but the query fails when I do change it. Thanks
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-07T21:18:52.33+00:00

It's not uncommon that a query that runs on one instance of SQL Server does not run on a different instance. In your case, it could be that the ACE provider is installed on the first instance you run on, but not the second one.

If this answer is not satisfactory, you need to give more details. Not the least why you expect the connection change not to matter.
James R. Hill 1 Reputation point

2023-04-07T22:10:11.78+00:00

all servers have the provider, the query runs successfully locally on all of them, but only locally, when the connection changes it fails: OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "The Microsoft Access database engine cannot open or write to the file '\path\path\excel\file.xlsx'. It is already opened exclusively by another user, or you need permission to view and write its data.". This is what I don't understand.
Seeya Xi-MSFT 16,586 Reputation points

2023-04-11T08:01:13.23+00:00

Hi @James R. Hill, We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated. Best regards, Seeya

Answer 1

Erland Sommarskog 121.9K MVP Volunteer Moderator

If you access the operating system on the server where this happens through Remote Desktop and open Task Manager, do you see any process for Excel? (Be sure to look under the Details tab). If you kill that process, does that resolve the situation?

I have never used the ACE provider myself, but all through the years I've been monitoring SQL forums, there have been no end of posts where people have problems using the ACE engine. I can't escape the feeling that it is not a tenable solution. But then again, I never see posts from sites where everything is working OK.

Anyway, in Object Explorer: Server Objects->Linked Servers->Providers find the the provider, check "Allow inprocess" and restart the instance. It may help for this problem. But it could also create new ones. If Excel crashes, this could now bring the entire instance down.

James R. Hill 1 Reputation point

2023-04-10T10:20:06.5466667+00:00

The file is not open by another user. This error only happens when you change the connection to another SQL Server instance.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-10T20:51:53.06+00:00

If the error message says that the file is open by another user, it probably means that it is open by another user. Computers normally don't lie. Rather, at times, they can be unpleasantly blunt.

You have not been doing a good job describing your environment, but if that is the very same Excel file that you are trying to access from two different instances, you have your other user right there. Just because you disconnect from the first instance, does not mean that this instance will let go of the file. Keep in mind that it holds the file open for the benefit of all users on that instance.

Answer 2

Seeya Xi-MSFT 16,586

Hi @James R. Hill,

It seems that Excel file you are trying to query is already opened exclusively by another user.

Check if the Excel file is opened by another user or process. If yes, ask them to close the file and try running the query again.

Try adding the IMEX=1 parameter to the connection string in your query. This parameter sets the driver to use Import Mode, which may help in cases where the Excel file is opened in exclusive mode.

Please refer to this blog: http://www.sql-server-helper.com/tips/read-import-excel-file-p03.aspx Best regards,

Seeya

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

James R. Hill 1 Reputation point

2023-04-11T12:32:23.52+00:00

I apologize for not being clearer describing our environment: I am trying to schedule time with our server team to get specific errors from the windows logs. I will post those findings as well a clearer picture of our setup. I use one server to monitor all our SQL Instances, it runs on Windows server 2016 standard and SQL SERVER version 15.0.2000.5. I run the OPENROWSET command from this server successfully, but when i change the connection the another Instance( SQL Server 2) it fails: it provides error messages and I don't believe its a completely accurate description(i think its more related to the second part(Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". )
Msg 7399, Level 16, State 1, Line 1 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 1 Cannot get the column information from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)". If I RDP to SQL server 2, I am able to successfully run the OPENROWSET as well, but again, If I change the connection the SQL Server 3 or back to my monitoring server, it fails. I have confirmed that I have admin access to the windows share where to the .xlsx file exists, I have added to parameter IMEX=1 as someone suggested. I am going to have the server team verify verify that no other users are accessing the file(including myself). and report back those results.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2023-04-11T21:04:24.1833333+00:00

As I said, the instance you just left will continue to have the file open, so it is not surprising that you cannot access it from the other instance.

I note that you are running a very old build of SQL 2019, in fact, the original one. You should download and install the most recent Cumulative Update as soon as possible, to get access to many bug fixes and enhancements. I would not expect this to resolve this particular problem, though.

Share via

how change SSMS connection to remote server and run OPENROWSET on ACE.OLEDB.12.0

2 answers

Your answer