Link to home
Start Free TrialLog in
Avatar of stu29
stu29Flag for United States of America

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')
Avatar of RiteshShah
RiteshShah
Flag of India image

have you followed the exact steps for creating linked server, I gave in my following article?

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

Avatar of stu29

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.
Avatar of Mark Wills
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'
Avatar of stu29

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.
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.
Avatar of stu29

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,
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial