OpenRowSet

Hello - I am trying to get the OpenRowSet function to work (open a file) from my laptop. I can get the function to work no problem when logged on the the windows server where SQL Server is installed.

However, when hitting a DB on the server from my laptop using SQL Server Mgmt Studio...it gives me an error.   See below:

 "The Microsoft Access database engine could not find the object 'Sheet1$'. Make sure the object exists and that you spell its name and the path name correctly. If 'Sheet1$' is not a local object, check your network connection or contact the server administrator."

Are there permissions I need to set up somewhere for this to work?

Here is a sample of the code I am running when I receive the error above.
SELECT *
FROM OPENROWSET('MSDASQL'
               ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}'
               ,'select * from   C:\Test\DataFile.csv')

Open in new window

Cynthia HillLead ConsultantAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
The path " C:\Test\DataFile.csv" must be relative to the server. Even if you run the script on your laptop the file must be at this path on the server. Think about it as a script running on the server. In order to make it availabe to the server you need to place the file in the shared folder on your laptop and access the file using a UNC path:
\\myLaptop\SharedFolder\DataFile.csv
0
Cynthia HillLead ConsultantAuthor Commented:
Chaau - Thanks for you post.

Still not working from my laptop. Works from the server just fine (when logged directly into the server), but not from my laptop using SSMS.

Here is the error I receive:

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" returned message "Could not find installable ISAM.".
Msg 7303, Level 16, State 1, Line 9
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

Is there a setting I need to change on my laptop? Or is it a setting on the server side?

Any help is greatly appreciated!
0
chaauCommented:
Thanks for providing the error code. The cause of your problem is in the permissions set on the server. The failed login did not have appropriate permissions to remotely activate the COM Class. See this article with the complete steps how to enable the permissions for the remote logins on the server

1) Grant permissions to the COM Class provided by OLE DB.

Start —>Control Panel —> Administrative Tools —> component Services
Expand Component Services —> expand Computers —> Expand My computer —> Expand DCOM config
Locate MSDAINITIALIZE and right click
Open Properties —> Security
In the “Launch and activation Permissions” click “customize” and “edit”
Add the login/User which failed to do the openrowset and in the permissions page tick ‘Local Launch’,’remote Launch’,’Local Activation’,’Remote Activation’ Click OK to close the permission page
Click ‘Apply’ and ‘OK’ on the MSDAINITIALIZE properties to close the window.

2) Grant Read Write access to the Login/User to the ‘temp’ directory on the server.
0
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Which version of Excel have you installed in your laptop?
I think Microsoft.ACE.OLEDB.12.0 provider is available with MS Office 2010.
0
Cynthia HillLead ConsultantAuthor Commented:
Chaau - Thanks for providing the steps you mention. It's still not working. It does seem very much like there is some setting on the server side that needs to be changed(?), but I worked with the DBA yesterday and we could not get things to work from my laptop. I have learned that perhaps we will still be able to log into the server remotely after our go-live date...so this may be a minimal issue.

Vitor - i have Office 365, which is using Excel 2013 at this point.
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Check in Control Panel\All Control Panel Items\Administrative Tools\ODBC, Drivers tab if Microsoft Access is listed. If afirmative then check the version (should be 12.00.????)
0
Cynthia HillLead ConsultantAuthor Commented:
Hey Guys - Thanks again for assisting with my question. The solution ended up being that I would just have to be logged in via remote desktop to run the openrowset  query.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cynthia HillLead ConsultantAuthor Commented:
I tried the options offered up by those who were nice enough io post to my question, but none of the solutions worked. I had to troubleshoot with other IT contacts internal to my company and they recommended the workaround mentioned in my final post (running the query while logged in via remote desktop).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.