• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 455
  • Last Modified:

sp_addlinkedserver trouble

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',
    '\\mmcecyapp01\data\reporting\database\cdr.mdb'

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?
0
egeil
Asked:
egeil
  • 2
  • 2
1 Solution
 
egeilAuthor Commented:
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
0
 
Eugene ZCommented:
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    
0
 
egeilAuthor Commented:
I have used 'sp_addlinkedsrvlogin', the result is the same...can you post an example of the "Create DSN" method? Greatly appreciated...

Eric
0
 
Eugene ZCommented:
login on sql server
Start-> conrol panel ->Admin tools->Data sources (ODBC) create one

you create DSN and use BOL example;
-----------------------------------
or
A2000
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'
GO
EXEC sp_addlinkedsrvlogin 'YourAccesDBName', 'false',
'domain\yourlogin', 'admin', ''
go

use sqlserverDB

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

----- drop linked server if you need
use master
go
sp_dropserver 'YourAccesDBName','droplogins'
0
 
CleanupPingCommented:
egeil:
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 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now