CODEWITHSUNDEEP
sqlsql-server-2008ms-accesslinked-server
Tom
Tom

Reputation: 4577

Linked server issue in SQL 2008 R2

We're in the middle of migrating from SQL 2000 to SQL 2008 R2.

Have everything almost complete except I cannot replicate a linked server we have in 2000 on 2008.

It's a linked Access database on our network.

The problem seems to be that on the SQL 2000 server I have the option to select Microsoft Jet 4.0 OLE DB Provider from the list of providers but that is not an option on the SQL 2008 server and any of the providers I do try and configure return an error.

The providers I have to choose from on the SQL 2008 server are as follows:

I had thought the simple solution would be to try and install the Jet provider but I can't find it anywhere.

Anyone know what I am missing here?

Thanks

Upvotes: 1

Views: 3781

Answers (3)

kemalserkanyildirim
kemalserkanyildirim

Reputation: 19

In your SQL Server Management Studio try to execute this;

exec sp_enum_oledb_providers this stored procedure show you the list of providers.

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294287

Is the R2 instance 64bit by any chance? In that case you need to make sure you have the 64bit Jet drivers installed.

Upvotes: 3

p.campbell
p.campbell

Reputation: 100567

Try using a script to create the linked server for you?

USE [master]
GO
EXEC master.dbo.sp_addlinkedserver 
          @server = N'My_Access_DB', 
          @srvproduct=N'ACCESS 2007', 
          @provider=N'Microsoft.ACE.OLEDB.12.0', 
          @datasrc=N'Z:\data\Access\Northwind2007.accdb'
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'My_Access_DB', 
             @locallogin = NULL , 
             @useself = N'False'
GO

Upvotes: 1

Related Questions