Create a linked server to Informix from Sql Server 2008 R2 by using ‘MS OLE DB Provider for ODBC Drivers’
EXEC master.dbo.sp_addlinkedserver @server = N'TEST02', @srvproduct=N'MSDASQL',
@provider=N'MSDASQL',@datasrc=N'eBay_EU_DUB',
@provstr=N'host=10.243.201.12'
Because in Windows ODBC, all changes to a same Server Name willreflect on all DSNs that use this server. If we create more than 1 DSNs withsame Server Name on one host. Then, we canuse above Provider string field to override the value in DSN. And Use theconnection string is code or SQL Server linked server configuration.
When creating ODBC DSN, if you test and saw following ‘localeinformation mismatch’ error,
Go to Environment tab and check ‘Use Server Database Locale’and retry.
Create a linked server to Informix from Sql Server 2008 R2 by using ‘IBM Informix OLE DB Provider’
EXEC master.dbo.sp_addlinkedserver @server = N'TEST1', @srvproduct=N'Ifxoledbc',@provider=N'Ifxoledbc',@datasrc=N'cms@cms_ol', @provstr=N'Host=10.243.201.12;Server=cms_ol;Database=cms;db_locale=en_US.57372'
However, there is some issue I am not able to fix.
OLE DB provider"Ifxoledbc" for linked server "TEST1" returned message"EIX000: (-25580) System error occurred in network function. ".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLEDB provider "Ifxoledbc" for linked server "TEST1".
In ASP.net code,
We can use it in web.config
<addname="eBay_EU_CMS_Primary_ConnectionString"connectionString="Dsn=eBay_EU_DUB;uid=odbc99;pwd=T3l3c0m!;database=/cms;deferlongfetch=No;fetchbuffersize=30;host=10.243.201.12;Server=cms_ol;Service=50001;nologinbox=No;readonly=Yes;Protocol=olsoctcp;usessl=No"providerName="System.Data.Odbc" />