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
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