Create Linked Server with ODBC Provider in SQL 2008 x64 bit

Question

question

JD asked Jun 14, '10

Create Linked Server with ODBC Provider in SQL 2008 x64 bit

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..?

sql-server-2008 odbc 64-bit

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2010-06-18T19:32:59Z

JD answered Jun 18, '10

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

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 2 · 2010-06-15T01:50:11Z

Fatherjack answered Jun 15, '10

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.

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 3 · 2010-06-15T12:10:37Z

Ted Thibodeau Jr answered Jun 15, '10

@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....

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question