stu29
asked on
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server
I'm getting the following error when running a query on a linked server for ODBC DSN (Access):
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "JOBBOSS".
The properties of the linked server:
Linked server: JOBBOSS
Server type: Other data source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: JOBBOSS
Data source: JOBBOSS32 <- I have this named DSN configured under System DSN
Query I'm running:
select * from openquery(JOBBOSS, 'select * from Source')
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "JOBBOSS" returned message "[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "JOBBOSS".
The properties of the linked server:
Linked server: JOBBOSS
Server type: Other data source
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: JOBBOSS
Data source: JOBBOSS32 <- I have this named DSN configured under System DSN
Query I'm running:
select * from openquery(JOBBOSS, 'select * from Source')
ASKER
I'm not linking using Jet. I'm using an ODBC DSN and I wrote down all the properties of it above. The Access thing I need to connect to has a custom .mdw so I have to go with ODBC instead of directly linking to the .mdb file with the registry modification.
after adding the linked server, you will need to add linked server logins, even if a default for 'Admin' . It needs someone to link as.
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
e.g.
sp_addlinkedsrvlogin
@rmtsrvname = 'JOBBOSS' ,
@useself = 'FALSE' ,
@locallogin = NULL ,
@rmtuser = 'rmtuser' , -- this will be your user and password set up in the Access DB. Or just use 'Admin' and no password
@rmtpassword = 'rmtpassword'
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'TRUE' | 'FALSE' | 'NULL']
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
e.g.
sp_addlinkedsrvlogin
@rmtsrvname = 'JOBBOSS' ,
@useself = 'FALSE' ,
@locallogin = NULL ,
@rmtuser = 'rmtuser' , -- this will be your user and password set up in the Access DB. Or just use 'Admin' and no password
@rmtpassword = 'rmtpassword'
ASKER
Thanks for your input!
I already had that set under Properties -> Security using the login given to me by the Access guys.
(checked) Be made using this security context:
Remote login: xyz
With password: xyz
I ran your sp anyway to see if it would make any difference for both the "xyz" and Admin users. It's still the same error msg.
I already had that set under Properties -> Security using the login given to me by the Access guys.
(checked) Be made using this security context:
Remote login: xyz
With password: xyz
I ran your sp anyway to see if it would make any difference for both the "xyz" and Admin users. It's still the same error msg.
Just a lateral thought, can you access that data via the DSN in say Excel ?
Could be a DSN issue, or maybe the custom component is causing problems for MSDASQL.
Could be a DSN issue, or maybe the custom component is causing problems for MSDASQL.
ASKER
Yes, the DSN works. I verified that in VS.NET Studio. Also, there's Test Connection when right clicking on the server. I increased both of below to 1000000 and it seems working. What are good values of these? My Access file is in a remote server and the connection is via a DSL line.
Properties -> Server Options -> Connection Timeout & Query Timeout.
Thanks,
Properties -> Server Options -> Connection Timeout & Query Timeout.
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html