Cynthia Hill
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.
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')
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!
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"
Msg 7303, Level 16, State 1, Line 9
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0"
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.
I think Microsoft.ACE.OLEDB.12.0 provider is available with MS Office 2010.
ASKER
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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).
\\myLaptop\SharedFolder\Da