I have just went through this if you are trying to create a linked server through sql Server 2000.
I did use OLEDB not ODBC though. I did experiment with ODBC but was not able to make ODBC work reliably
This is how I made OLEDB work:
Procedure for creating an OlE DB linked server to Informix.
Prior to creating the connection, be certain that Informix Client SDK 2.90 or newer is installed on the server.
Be certain to uninstall any earlier versions first, then reboot, install the newest SDK and reboot again.
IBM provides 2 scripts that need to be run against the Informix database:
execute "doledbp.sql" first then execute
"coledbp.sql"
from the sysmaster database as user informix .
These scripts are included with the installation. The current path on the dev server is:
C:\Program Files\IBM\Informix\Client-SDK\etc
The scripts are backward compatable, but must be run for any upgrades.
Should the SDK be updated in the future, this procedure must be followed as well
1. Use Enterprise Manager.
It is possible to use sp_addlinked server, but I had difficulties with the Informix connection string
using sp_addlinkedserver and have not fully researched the proper connection string for Informix.
2. Select the SQL Server instance you wish to link the Linker server to.
3. Select "Security" from the tree then right click "Linked Servers"
4.Click New Linked server
a. You should be on the general tab of the properties form
1.Name the Linked Server. For this document, I am naming it IFXexample.
2.Select "Other Data Source"
3.Select IBM Informix OLE DB Provider from the drop down box.
4. Click Provider options.
Note: This button will be grayed out except while creating a new linked server
The options selected apply to all linked servers that use the Informix OLE DB provider.
Should you need to change this options later you must create a new linked server, change these options
click OK and return to the Properties form, then cancel the new server. (Do not blame me!!!!!)
a. Select the proper options:
I have Dynamic Parameters and Allow In Process checked.
DO NOT CHECK "Nested Queries". Informix does not support nested queries in the FROM clause and SQL Server will send a nested query to Informix when there are 2 or more joins to a table residing on the linked server This was a very difficult bug to find.
b. Select OK and return to the property form.
5.Property name = ifcoledb
6. Data Source = dbname@tservernameThis
7. Provider String = decasr8=R8;DB_LOCALE=en_US.819
The decasr8=R8 is required. I do not believe the other variable actually effects anything since I believe it is the Informix default, but I do not want to risk breaking anything I know works right now.
a. Go to Security tab
1. Set Security options
2 Our current security requires selecting "Be made using this security context" and entering the Informix db login and password.
b. Server options tab
1. Check "Collation Compatable" or performance will be horrible.
2 Check Data Access (one of the defaults) and Use Remote Collation (another default)
c. Click OK and close properties
5. Refresh and you should be good to go.
Programming Notes:
1. Access the linked server only using stored procedures. Otherwise, the performance will be very poor.
2. Use the 4 part naming convention. ie: IFXexample.dbname.schema.tablename.
3. database schema is case sensitive. This seems to be something to do with the way SQL sennds the schema to Informix contained within
double quotes. It might be due to the Informix Client SDK as well, but regardless, I can find no way to remove the schema case requirement.
NOTE: Data is not case sensitive, only the schema
4. Create Stored procedures from Query Analyzer only.
Use this example:
set Ansi_Nulls On
set Ansi_Warnings ON
GO
CREATE PROCEDURE [dbo].[aaa_BBBBB]
a. Once created you can still edit the sp as normal, but you must set Ansi Nulls and Ansi Warnings on explicity for each sp
There is still a bug from IBM for certain datatypes.
SQL will not accept a stored procedure for example where the Informix DB has an Informix time datatype.
Work Around
Create a view in Informix either leaving out that column or casting it to a different data type.
For a time data type in Informix you must use the EXTEND Keyword to change a time to a datetime.
Example: EXTEND (x0.timefld ,year to minute) where x0 is the table alias and timefld is a time value.
Do not forget to parse out the time in the SQL stored procedure since the extend seems to give the current date!
I know of no other datatype bugs and of course, if the field is not needed, do not include it in the view
