Link to home
Start Free TrialLog in
Avatar of Cynthia Hill
Cynthia HillFlag for United States of America

asked on

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

Avatar of chaau
chaau
Flag of Australia image

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
Avatar of Cynthia Hill

ASKER

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!
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.
Which version of Excel have you installed in your laptop?
I think Microsoft.ACE.OLEDB.12.0 provider is available with MS Office 2010.
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.
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.????)
ASKER CERTIFIED SOLUTION
Avatar of Cynthia Hill
Cynthia Hill
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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).