Linked Server Problems

W

WEB

I have a query regarding linked servers on SQL Server 2000. The SQL
server service runs with a particular network account, and the linked
server in question is setup to connect to an excel spreadsheet located
on a network drive. I've set the security on the linked server to be
'Be made without using a security context'.

I've made sure that both the sql server service account and the user
running the query both have access to the network drive containing the
excel spreadsheet but regardless of what I try when accessing via query
analyser from a remote network location I get the

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. The
provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80004005: The provider did not
give any information about the error.].

When I login to the server via remote desktop I curiously can query
successfully via query analyser on the server from the master database
but not the application database that I want to see the data. Heres my
query

select * from OPENQUERY(LINKEDSERVERNAME, 'select * from
[ExcelWkShtName$]' )

I've checked the known issue with sql server trying to save a
temprorary file to c:\temp, making sure its not the users
c:\Documents....\temp folder via the environment variables settings,
and that users (both the end user and the sql service account) have
write permissions to the temp folder etc

Even if I get the linked server to reference a copy of the excel file
on the local drive on the sql server machine, I still can't connect
from a remote machine query analyzer and just ge the error.

Does anyone know how to ultimately be able to query from a remote
network machine using windows authentication to speak to the sql server
db, and in turn link to the linked server ?

Many Thanks,

Alistair
 
W

WEB

Further to my first email I've discovered that I CAN'T in fact query
the linked server using query analyser on the master table. Even
though I update the data source and apply the change in enterprise
manager - it still seems to still pull the information form the
previous data source (the successful one where the linked server excel
source file is pulled from the local drive) unless I restart the server
to refresh !

Any help would be greatly appreciated.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top