Linked server issue in SQL 2008 R2

1
votes

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:

  • Microsoft OLE DB Provider for SQL Server
  • Microsoft OLE DB Provider for Analysis Services 10.0
  • OLE DB Provider for Microsoft Directory Services
  • SQL Server Native Client 10.0
  • Microsoft OLE DB Provider for ODBC Drivers
  • Microsoft OLE DB Simple Provider
  • Microsoft OLE DB Provider for Indexing Service

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

3
sqlsql-server-2008ms-accesslinked-server
You shouldn't need the jet driver - the OLE DB driver for Sql Server should work fine. Did you try it? Errors? - M.R.
Using the OLE DB driver for SQL Server returns a slew of errors. Invalid parameter, network-related instance-specific error... - Tom

3 Answers

3
votes

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

1
votes

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
0
votes

In your SQL Server Management Studio try to execute this;

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