Linked Server (Secure Access Database) Problem

hi all;
I m facing problem in setting linked server to Secure Access Database.. I
have created a system DSN name "Costing" and set the user name and password
also. Then I created linked server using enterprise manager with following
property values
Linked Server = CostingServer
Provide name = Micorsoft OLEDB Provider for ODB Drivers
Data Source = Costing
when I press ok and click on "tables" icon it shows all the tables of secure
database... it means that connection successfull..
but when I try to access tables using following query
SELECT * FROM COSTINGSERVER...MaterialGroup
it gives the following error
"Invalid schema or catalog specified for provider 'MSDASQL'."
but if remove password from the database all work is going fine without any
error...
Any suggestion, help would be higly appreiciated in this regard..
AnsariCould you please run the following-
dbcc traceon(7300,3604)
and then execute a query. You'll get a more detailed error message.Please
paste it here for us to review.
Cheers,
Vikram Jayaram
Microsoft, SQL Server
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||hi Vikram
Thanks for your suggestion. I am sorry to say that I have written down some
incorrect information. I was using Microsoft.JET.OLEDB provider instead
Microsoft OLEDB Provider for ODBC Drivers. I have created the linked server
in following way
EXEC sp_addlinkedserver
@.server = 'CostingServer',
@.provider = 'Microsoft.Jet.OLEDB.4.0',
@.srvproduct = 'ACCESS',
@.datasrc = 'C:\COSTING2.mdb'
go
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, 'ansari'
go
dbcc traceon(7300,3604)
go
select * from costingServer...materialgroup
here is the error message after runing dbcc traceon(7300,3604)
OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication
failed.
[OLE/DB provider returned message: Not a valid account name or password.]
OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'
IDBInitialize::Initialize returned 0x80040e4d: Authentication failed.].
and if I unset the database password and run the
sp_addlinkedsrvlogin CostingServer, false,sa, Admin, null
it work fine..
And in last the error i have posted i.e
"Invalid schema or catalog specified for provider 'MSDASQL'."
is due to not supplying catalog name. if i change my query
SELECT * FROM COSTINGSERVER...MaterialGroup
to
SELECT * FROM COSTINGSERVER.[c:\costing]..MaterialGroup
it also work fines
ansari
"Vikram Jayaram [MS]" <vikramj@.online.microsoft.com> wrote in message
news:m$2w#s$YEHA.3316@.cpmsftngxa06.phx.gbl...
> Could you please run the following-
> dbcc traceon(7300,3604)
> and then execute a query. You'll get a more detailed error message.Please
> paste it here for us to review.
> Cheers,
> Vikram Jayaram
> Microsoft, SQL Server
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>