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')
LVL 9
stu29Asked:
Who is Participating?
 
Mark WillsTopic AdvisorCommented:
If set to 0 originally then it uses the sp_configure settings which is 20 seconds for remote connection by default and 10minutes for remote query.

You can check that with :

SELECT * FROM sys.configurations
ORDER BY name ;
GO

So, it really depends on how long you are prepared to wait - fine if always going to connect and return data, but not so good if there is an issue. I would suggest just trying to change the Connection timeout and set to 60 first up and progress up to 300 which is a 5 minute wait, and an eternity if you are sitting watching it. For Query timeouts, would be inclined to leave at 0 which should default to the system config which should be 600 (ie ten minutes), you can increase, but if you are currently getting results back within a few minutes, then it is most likely the connection timeout that it is tripping over.

So, first try 60 for connect   and 0 for query timeouts.


0
 
RiteshShahCommented:
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

0
 
stu29Author Commented:
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.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Mark WillsTopic AdvisorCommented:
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'
0
 
stu29Author Commented:
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.
0
 
Mark WillsTopic AdvisorCommented:
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.
0
 
stu29Author Commented:
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,
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.