Link Excel on remote machine problem

Question

Link Excel on remote machine problem

Yihong

SSChasing Mays

Points: 624
More actions
July 2, 2009 at 12:56 pm

#155905

I was trying to link or query an excel file, and this file is on a remote server (remoteServer).
EXEC sp_addlinkedserver 'Excel',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'\\remoteServer\public\somefile.xls',
NULL,
'Excel 8.0'
go
EXEC sp_addlinkedsrvlogin 'Excel', 'false', null, null, null
go
but it gave me error :
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Excel" reported an error. The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "Excel".
Then I copied the file to the locate machine, and it runs fine.
what did i miss on the remote machine.
Thanks for help.

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

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

Does the login that SQL is running under have access to that share/path/file? Or is SQL running as local system? If it is then it will NEVER be able to get to the share..

Tell us about the sql login config and security on the remote end, is everybody in the same domain?

CEWII

Yihong SSChasing Mays Points: 624 More actions · Answer 2

I actually tried to add the sql login to the share\path\file on the remoteServer, and gave it a full control, but still didn't work. Yes, everybody is in the same domain.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

do an:

xp_cmdshell 'DIR \\remoteServer\public'

Does it return the directory or does it error out with an access denied message?

Yihong SSChasing Mays Points: 624 More actions · Answer 4

no "Access Denied" message returned.

it says Volume in drive \\remoteserver\public\ has no label... etc. and one row says "file not found" what does that mean?

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 5

Are there other files there? If not then there might be a permissions issue on that file alone.

I can't remember is an ending \ on the dir was required:

xp_cmdshell 'DIR \\remoteServer\public\'

CEWII

Yihong SSChasing Mays Points: 624 More actions · Answer 6

it says "The system cannot find the file specified."

Yihong SSChasing Mays Points: 624 More actions · Answer 7

I think the problem is that the actual sub folder name has a space, and xp_cmdshell doesn't like it

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 8

At this point you are not trying to operate on any sub-directories, you are trying to get a directory and sub-directories with spaces are easily handled by that process so I don't think that is the issue.. I guess the following isn't necessary.. I know this is a stupid question but if you login on that server as yourself can you see the contents of that share?

If not then you likely have a name resolution problem..

CEWII

Yihong SSChasing Mays Points: 624 More actions · Answer 9

First of all, thanks for your help.

Maybe I didn't make it clear, my excel file is under \\remoteServer\public\Sub Folder\, when I exec xp_cmdshell, nothing returned, but returned a list of all files if I executed \\remoteServer\public\OtherFolders\, it made me think space could be the issue.

Anyway, I found I could successfully query the \\remoteServer\public\Sub Folder\somefile.xls from sqlSever, but not from my own machine.