Create Linked Server with ODBC Provider in SQL 2008 x64 bit

Question

Hi,

I'm Trying to create a Linked Server to an OBDC System DSN as Data Source in a SQL server 2008 64bit.

I run this script:

EXEC master.dbo.sp_addlinkedserver @server = N'SISTEMAS',
  @srvproduct = 'SISTEMAS', 
  @provider = 'MSDASQL', @datasrc = 'FOXODBC',
  @location = '',
  @provstr = '', 
  @catalog = ''
GO

Linked Server is created, but when I test the connection, it returns an error:

The OLE DB Provider 'MSDASQL' has not been registered

I search the books online and found this:

*To create a linked server to access an ODBC database when you are using an ODBC data source: Create a System data source on the computer on which SQL Server is installed. Execute sp_addlinkedserver to create the linked server, specifying MSDASQL or NULL as provider_name, and the name of an ODBC system data source as data_source.*

So, It's any way to register this provider to SQL2008..?

Answer 1

0

Hi,

Finally I can register the 64 bit provider for ODBC (MSDASQL), but it's only run with 64 ODBC, so no conection to 32bit foxpro tables is possible.

Thanks for your answers

Here is the ODBC provider

answered Jun 18 '10 at 07:32 PM

JD
104 ● 3 ● 6 ● 7

(comments are locked)

Answer 2

0

I think you may have run into a 64bit issue. Some linked server drivers have not been updated and you can only create some (sorts of) linked servers on 32 bit SQL Server. I thought it was either Access or Excel (memory fails me and sadly I am about to leave home right now so hopefully someone else can complete the details for you/me.

answered Jun 15 '10 at 01:50 AM

Fatherjack ♦♦
38.8k ● 56 ● 73 ● 104

(comments are locked)

Answer 3

@Fatherjack has the meat of the matter.

64-bit SQL Server requires a 64-bit OLE DB Provider for ODBC (e.g., MSDASQL) and a 64-bit ODBC driver.

32-bit SQL Server requires a 32-bit OLE DB Provider for ODBC, and a 32-bit ODBC driver.

Microsoft has been hot-and-cold on support for their OLE DB Provider for ODBC, so you may want to look into a third-party solution, such as this one from my employer. More details on using this solution are found here.

That said, there are many limitations to SQL Server Linked Server functionality, so you may want to clearly define your goals, and see what other solutions are available....

Create Linked Server with ODBC Provider in SQL 2008 x64 bit

Follow this question

Related Questions