sp_addlinkedserver trouble

Posted on 2003-04-01
Medium Priority
Last Modified: 2007-12-19
I am trying to link to an access 97 database in SQL2000. This is what I have tried (taken from the help file):
sp_addlinkedserver 'ECYFlash', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',

The linked server is added (and visible through Enterprise Mgr), but when I try to view the tables through EM it locks up, i have tried waiting it out to see if an error message is returned, but i never get one...end result : 3 finger salute on Enterprise Mgr...

I also tried using a mapped drive rather than UNC...

Any Ideas?
Question by:egeil
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2

Author Comment

ID: 8246447
I did finally get an error when trying to execute a SELECT statement in Query Analyzer :

[Microsoft][ODBC SQL Server Driver]Operation canceled
[Microsoft][ODBC SQL Server Driver]Timeout expired
ODBC: Msg 0, Level 16, State 1
Communication link failure

Connection Broken
LVL 43

Expert Comment

by:Eugene Z
ID: 8246734
did you use 'sp_addlinkedsrvlogin'  after 'sp_addlinkedserver' ..?

Another solution:
 use create DSN on SQL server to connect ACCess amd use 'ODBC data source' with provider_name=MSDASQL
see BOL    

Author Comment

ID: 8247973
I have used 'sp_addlinkedsrvlogin', the result is the same...can you post an example of the "Create DSN" method? Greatly appreciated...

LVL 43

Accepted Solution

Eugene Z earned 300 total points
ID: 8248119
login on sql server
Start-> conrol panel ->Admin tools->Data sources (ODBC) create one

you create DSN and use BOL example;
try to use my working example (I use it in monthly job)
use master
EXEC sp_addlinkedserver
   @server = 'YourAccesDBName',  --or any name
   @provider = 'Microsoft.Jet.OLEDB.4.0',
   @srvproduct = 'OLE DB Provider for Jet',
   @datasrc = '\\server1\e$\accessDB.mdb'
EXEC sp_addlinkedsrvlogin 'YourAccesDBName', 'false',
'domain\yourlogin', 'admin', ''

use sqlserverDB

insert into #tempTable
select * from openquery(YourAccesDBName,'select * from accessTable order by entry_date')

----- drop linked server if you need
use master
sp_dropserver 'YourAccesDBName','droplogins'

Expert Comment

ID: 9275398
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question