Unable to create linked server using ODBC Connection

i trying use system dsn data source in linked server , following error when run query against linked server:
[ole/db provider returned message: [microsoft][odbc driver manager] data source name not found , 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 error.

 

if test connection in odcb data source administrator connection works fine, when attempt use in linked server fails.  have attempted run odbc trace received no data, there no further errors thrown in event logs or sql server logs.  sql server , informix servers in same domian , can communicate.

system:
sql server 2000 standard edition sp4 default instance
windows server 2003 r2 sp2 enterprise edition x64
i have installed 64 bit ole db provider odbc (msdasql) found here:
http://www.microsoft.com/downloads/details.aspx?familyid=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=en

scripts used create 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 32 bit server, there i'm missing configuring msdasql in 64 bit os?

 

 

there no 64 bit msdasql on windows server 2003, complaining driver not exist.  there issue when create data source, created specific architecture depending on odbcad32 run (either in system32 or syswow64).  if run syswow64, see provider (as have suggested) 32 bit provider, , writes dsn 32 bit registry keyspace (in wow registry hive instead of native hive).  need run 64 bit version of odbcad32 see native providers available , create dsn 64 bit connections.

 

hope helps,

 

john

SQL Server  >  SQL Server Data Access