x

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

more ▼

asked Jun 14 '10 at 08:24 PM in Default

JD gravatar image

JD
104 6 7 8

(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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

more ▼

answered Jun 18 '10 at 07:32 PM

JD gravatar image

JD
104 6 7 8

(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Jun 15 '10 at 01:50 AM

Fatherjack gravatar image

Fatherjack ♦♦
41.2k 72 77 107

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Jun 15 '10 at 12:10 PM

Ted Thibodeau Jr gravatar image

Ted Thibodeau Jr
1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1816
x37
x24

asked: Jun 14 '10 at 08:24 PM

Seen: 8861 times

Last Updated: Jun 15 '10 at 01:47 AM