How to connect SQL Server 7.0 to Informix

				
SQL Server 7.0 is capable to work in 
heterogenious IT enterprise environment. Although 
configuration of data exchange between Informix 
and SQL is not very complicated, I had
some problems to complete it, because of 
confusing error messages comming from SQL Server.
Necessary steps for configuring connection:
1. Install Informix drivers for ODBC on your 
 machine, that will be used for connection. 
 The source of that driver is Informix-CLI 
 package (for example).
2. Using Informix SetNet32 configure your 
 connection to Informix database. You have to
 specify your environment, server information 
 and host information (see tabs on SetNet32).
 You need to know what is the name of service 
 for Informix - if it is not specified in 
 "services" file (in Windows or Winnt 
 directory), you have to contact your Informix 
 database administrator.
3. Then, using ODBC Data Source Administrator, 
 configure ODBC connection to your Informix
 database (of course, that database must exist 
 on Informix server, and also you have to
 have account on that host). Be sure to specify 
 your login name and password in that
 connection.
 You can verify your connection using 
 Informix "ILogin 32 Demo" which cannot return
 any error message. You can also use Excell for 
 example, and connect to database using your
 ODBC connection.
All other steps must be completed in Query 
Analiser (SQL Server):
4. Be sure that you have logged in as 'sa' or 
 other user with administration rights, and
 you have using 'master' database.
 Execute following statement:
EXEC sp_addlinkedserver 
@server = 'PNDON7', -- defined in 


-- SetNet32 on tab 'Server information',
-- field 'Informix Server'

@provider = 'MSDASQL', -- DO NOT CHANGE !
@datasrc = 'base_x', -- name of the 


-- ODBC connection defined in step 3

@srvproduct = 'Informix-CLI 2.5 (32 bit)'


-- depends on version you have



 You have to see 'Server added.' message, which means succes. NOTE: this message dosn't mean that your linked server is configured correctly, it only means that you have added it to the SQL Server!
5. Now, you have to map logins from SQL server to logins on Informix server.
 Execute statement:
 EXEC sp_addlinkedsrvlogin 'PNDON7',
 'false', 'sa', 'login_name', 'password'
You have to put your machine login name and 
 password of course.
Now you are ready to execute your SQL queries, 
using following syntax:
SELECT * FROM PNDON7.base_x.table_owner.table_name
Here is stored procedure "inf", which might be usefull
because it prints owner of given table.
CREATE PROCEDURE inf @base varchar(30), @table varchar(40) 
AS
DECLARE @spbody nvarchar(2000)
 
SET NOCOUNT ON
SET @spbody=N'SELECT tabname, owner FROM PNDON7.' + CONVERT(nvarchar(20),@base) + '.informix.systables 
WHERE tabname LIKE ''%'+@table+'%'''
EXEC sp_executesql @spbody
SET NOCOUNT OFF




 
There might be necessarry to install Service Pack 
2 for SQL Server 7.0. It is probably possible to 
execute stored procedures on Informix - you have 
to configure your linked server in 'Security' 
tree in MMC. Steps 4 and 5 may also be completed 
using MMC.
Good luck.
Artur