locked
64-bit SQL Server 2008 R2, Linked Servers and 32-bit ODBC issue RRS feed

  • Question

  • Greetings Technetters,

    I have a Windows Server 2008 64-bit machine running SQL Server 2008 R2 64-bit.  I am using the SQL code I found on this site to create a linked server to an ODBC data source with a DSN thusly:

     EXEC sp_addlinkedserver
       @server = N'MyServerName',
       @srvproduct = N'',
       @provider = N'MSDASQL',
       @datasrc = N'LegacyAppDSN';
    GO

    The problem is that the legacy app only has a 32-bit driver, so I have to use the %windir%\SYSWOW64\odbcad32.exe to create the DSN as the driver doesn't show up in the 64-bit version.  SQL Server has trouble using the 32-bit driver for some reason.  The linked server code completes without error, but when I try to navigate the object browser to see the tables, I get an error message that says"

    "cannot initialize the data source of OLE DB provider "MSDASQL" for linked server "MyServerName".(Microsoft SQL Server Error:7303)

    If I drop the linked table and use the 64-bit odbcad to create the DSN, it works just fine and I can query data from the remote server as expected.

    Does anyone know how to get around this issue?  I have seen a lot of 64/32-bit discussion, but nothing about this specifically.

    Thanks in advance,

    Dan

    Wednesday, April 27, 2011 8:43 PM

Answers

  • Hi dpowell91,

     

    As Pak-Ming mentioned in this post, the 64-bit SQL Server cannot connect to a linked server via a 32-bit ODBC Driver. How about his suggestion that find a 64-bit OLEDB provider for your application and connect directly via its native OLEDB Provider?

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Monday, May 9, 2011 2:00 AM
    Monday, May 2, 2011 5:42 AM

All replies

  • Hello,

    Please see the following article about this issue:

    http://support.microsoft.com/kb/942976

    Hope this helps.


    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, April 27, 2011 8:55 PM
  • Thanks, Alberto.  That article is in the same neighborhood, but not quite what I am looking for.

    The problem I have is SQL Server 2008's ability to USE the DSN once created in the appropriate ODBC Administrator tool.  If I create a 64-bit DSN, no problem with linked server.  If I create a 32-bit DSN, it cannot be used.  SQL Server "sees" the linked server, but you can't navigate to it with the object browser or query data from it.  Any thoughts along those lines?


    Thanks

    Dan

    Wednesday, April 27, 2011 9:32 PM
  • Hi dpowell91,

     

    As Pak-Ming mentioned in this post, the 64-bit SQL Server cannot connect to a linked server via a 32-bit ODBC Driver. How about his suggestion that find a 64-bit OLEDB provider for your application and connect directly via its native OLEDB Provider?

     

    Thanks,
    Weilin Qiao


    Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
    • Marked as answer by WeiLin Qiao Monday, May 9, 2011 2:00 AM
    Monday, May 2, 2011 5:42 AM