3

I am attempting to run the below statement:

INSERT INTO table SELECT * FROM 
OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;
Database=//server/folder/file.xls;
HDR=YES;',
'SELECT * FROM [Sheet1$]')

However, I am receiving the below error:

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 '\\server\folder\file.xls'. It is already opened exclusively by another user, or you need permission to view and write its data.".

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)".

I am running SQL Server 2005 on a 32 bit machine. The code being executed will eventually be from c# code behind on a IIS6 web server. However, currently I'm just trying to get it to run on the SQL Server. I'm logged into the SQL Server using SQL Auth, but the file must be accessed using Windows Auth specific to the share drive (an AD account on our network). Ad-Hoc priveleges have been given to the SQL Auth account in order to allow OPENROWSET.

I have tried adding UID=user;PASS=pswd into the OPENROWSET code as below:

INSERT INTO table SELECT * FROM 
    OPENROWSET('Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;
    Database=//server/folder/file.xls;
    HDR=YES;
UID=user;
PASS=pswd',
    'SELECT * FROM [Sheet1$]')

This yielded the same error. I also set up an SQL CREDENTIAL with the user id and pass of the windows auth Active Directory account with access to that share drive, but the error remained.

I considered using a proxy, but this isn't an sql server agent job. It's an adhoc call.

I have also logged into the SQL Server using Windows Auth with a user that has rights to the share, and get the same error.

I've researched this a lot, but can't seem to find an answer that works. Through my searches, it seems to be a problem many people have. Any help will be greatly appreciated. I'm stumped.

Improve this question
6
  • Please provide information about server operating system. Is it a 32 or 64bit architecture? Have you installed proper OleDb drivers? Where are you trying to execute above query: on server side on local computer? Commented Dec 30, 2013 at 15:15
  • @MaciejLos It is a 32bit architecture. OleDb works fine without using the share drive, as stated, so I know those drivers are working fine. Also noted above, I will eventually execute from C# code running as CodeBehind on a webserver. For now, however, I simply want this to work when I run a general SQL adhoc query logged on using SQL Server Auth. I am running management studio to run the query. Commented Dec 30, 2013 at 15:26
  • MSDN wrote: To use BULK INSERT or INSERT...SELECT * FROM OPENROWSET(BULK...) to bulk import data from another computer, the data file must be shared between the two computers. To specify a shared data file, use its universal naming convention (UNC) name, which takes the general form, \\Servername\Sharename\Path\Filename. Additionally, the account used to access the data file must have the permissions that are required for reading the file on the remote disk. Commented Dec 30, 2013 at 16:45
  • @MaciejLos That I understand, but it seems that no matter what I do, I cannot get the correct combination. I have tried, as stated above, several credential matchings. Can you speak to why those did not work or something I have not yet tried? Commented Dec 30, 2013 at 17:26
  • I promise to aks last - maybe stupid question - Have you tried to restart SQL Server or WinOS? Sometimes system "holds" opened Excel file in a memory... Find and close any opened instance of Excel. Commented Dec 30, 2013 at 19:27

2 Answers 2

Reset to default
1
+100

I'm quite sure that when you use OpenRowSet that it's the Service user for the SQL Server that needs access to the file.

And since it appears that you are trying to access a file on another server, it can be a challenge to get it to work, if the SQL server is running as Local System or another local user account.

I have several times solved this by either changing the SQL server to run as a AD user (with the security implications it has) and then give that user access to the file on the network, or placed the external file (in this case the Excel) on the same computer as the SQL server.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

I don't have the ability to move the file to the SQL Server, although it works fine if it is there. You're right that accessing another server is the issue here. I'm trying not to log in as an AD users, but I may have to go that route if there is no other way. Do credentials affect openrowset at all? This site jasonstrate.com/2013/07/… seems to indicate that credentials can be used to access files on other servers.
If both computers are on the same domain, you may be able to grant the Local System account (or whatever account the SQL Server is running as) on the SQL Server computer access to the //server/folder share.
How do I determine what account the SQL Server is running on?
You can find the information on the Log On tab of the SQL Server service (Administrative Tools->Services)
I found the account marked under the log on tab, gave it rights to the share drive, and the error did not change.
|
0

There is possible to use 2 different connection strings:

  1. ACE OLEDB 12.0
    2. or
  2. JET OLEDB 4.0

Try both.

Sometimes Excel contains untypicall data type. Try to read entire data as a text using option "IMEX=1".

In case of further issues, try below solutions:
Linked Server using Microsoft.Jet.OLEDB.4.0 problem
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.