OpenRowSet

Question

OpenRowSet

bicky1980

SSCrazy

Points: 2079
More actions
October 14, 2009 at 10:11 am

#212112

Hello I am running the following command:
select * from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test.xls',
'select * from [sheet1$]')
This works fine...
But when I change the path of the file to a file on the network (which I have comfirmed is accessible using windows explorer from the server) It comes back with the following error:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error.
The code I am trying to execute is:
select * from OpenRowset(
'Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=\\anothercomputer\c$\test.xls',
'select * from [sheet1$]')
Any help with this would be great!
Thanks in advance
Bicky

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Piotr.Rodak SSCrazy Eights Points: 9376 More actions · Answer 1

Hi,

I guess this is due to the fact, that SQL Server does not access the file as you, but rather as the account it is running under. If it is running as Local System, it will not be able by default to reach to network resources. Try (if you can) to run the sql server service under your account.

Here's excerpt from BOL:

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. The user account that is used by SQL Server must have been granted the permissions that are required for reading the file on the remote disk.

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/18a64236-0285-46ea-8929-6ee9bcc020b9.htm

Regards

Piotr

...and your only reply is slàinte mhath

GSquared SSC Guru Points: 260824 More actions · Answer 2

Permissions for the SQL account is usually the problem with these.

I've also had problems when someone else (or me) had the file open already.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

bicky1980 SSCrazy Points: 2079 More actions · Answer 3

Thanks for the replies!

How do I check what the server is running as (whether its local host or not)

Thanks

tosscrosby SSCoach Points: 18045 More actions · Answer 4

On the server, go into services, find SQL Server Agent and double click it. Under the "Logon" tab it'll show the account running the service. That's the account that will need appropriate rights.

-- You can't be late until you show up.

bicky1980 SSCrazy Points: 2079 More actions · Answer 5

I have checked what ther server is running as and its using the logon details from the SQL Administrator so surely they should have full rights?

Is there a way to check this?

Thanks

tosscrosby SSCoach Points: 18045 More actions · Answer 6

Sql administrator as in "sa"? Or a domain admin account? sa will never hae rights to a data share. A domain account will need explicit rights to the share.

-- You can't be late until you show up.

bicky1980 SSCrazy Points: 2079 More actions · Answer 7

bicky1980

SSCrazy

Points: 2079

October 15, 2009 at 1:56 pm

#1066504

Both Domain and SQL Admin...

tosscrosby SSCoach Points: 18045 More actions · Answer 8

See the last three replies not made by you. Each essentially states the same thing, the SQL Admin domain account needs permissions to your file share. If you do not know how to check it, get your network admin to help (I'm a little iffy on this task).

-- You can't be late until you show up.