none
Unable to create linked server using ODBC Connection

    Question

  • I am trying to use a System DSN as a data source in my linked server and get the following error when I run a query against the linked server:
    [OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]
    OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005:   ].
    Msg 7399, Level 16, State 1, Line 1
    OLE DB provider 'MSDASQL' reported an error.

     

    If I test my connection in ODCB Data Source Administrator the connection works fine, when I attempt to use it in the linked server it fails.  I have attempted to run an ODBC trace but received no data, there are also no further errors thrown in the Event Logs or SQL Server logs.  My SQL Server and Informix servers are in the same domian and can communicate.


    System:
    SQL Server 2000 Standard Edition SP4 as a default instance
    Windows Server 2003 R2 SP2 Enterprise Edition x64
    I have installed the 64 bit OLE DB Provider for ODBC (MSDASQL) found here:
    http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en


    Scripts I used to create the Linked Server:
    EXEC master.dbo.sp_addlinkedserver
       @server = N'aServer'
     , @srvproduct=N'INFORMIX 3.00.FC2 64 BIT'
     , @provider=N'MSDASQL'
     , @datasrc=N'MySystemDSN'

     

    EXEC master.dbo.sp_addlinkedsrvlogin
       @rmtsrvname=N'aServer'
     , @useself=N'False'
     , @locallogin=N'SCOTTSDALE1\DMASCIANGELO'
     , @rmtuser=N'REPORTS'
     , @rmtpassword='######'

     

    This same configuration works on a 32 bit server, is there something I'm missing configuring the MSDASQL in the 64 bit OS?

     

    Sunday, June 22, 2008 4:33 PM

Answers

  •  

    There is no 64 bit MSDASQL on Windows Server 2003, so it is complaining that the driver does not exist.  There is also the issue that when you create a data source, it is created for a specific architecture depending on which odbcad32 you run (either in system32 or sysWoW64).  If you run it from sysWoW64, you will see the provider (as you have suggested) but it is only the 32 bit provider, and writes the DSN only to the 32 bit registry keyspace (in the WoW registry hive instead of the native hive).  You would need to run the 64 bit version of odbcad32 to see which native providers are available and to create a DSN for 64 bit connections.

     

    Hope that helps,

     

    John

    Tuesday, June 24, 2008 12:34 AM

All replies

  •  

    There is no 64 bit MSDASQL on Windows Server 2003, so it is complaining that the driver does not exist.  There is also the issue that when you create a data source, it is created for a specific architecture depending on which odbcad32 you run (either in system32 or sysWoW64).  If you run it from sysWoW64, you will see the provider (as you have suggested) but it is only the 32 bit provider, and writes the DSN only to the 32 bit registry keyspace (in the WoW registry hive instead of the native hive).  You would need to run the 64 bit version of odbcad32 to see which native providers are available and to create a DSN for 64 bit connections.

     

    Hope that helps,

     

    John

    Tuesday, June 24, 2008 12:34 AM
  • Sunday, November 23, 2008 10:44 AM