How to connect SQL Server 7.0 to Informix - SQL, database, informix, oracle, SQL Server

SQL,ODBC,article,providing,information,connec

SQL Stats

Code: 31,327 lines
Jobs: 372 postings

Sponsored by:

You are in:

Latest Code Ticker for SQL.

Unix Date Convertor Function
By George Graff on 10/23

Insert pipe delimited rows into multiple rows.
By Charles Toepfer on 10/21

Logfiles by PL/SQL
By Stephan Rechberger on 10/21

To display a name in a default language if the given one doesn't exist
By Serge Alard on 10/18

Order by column except a few values
By Serge Alard on 10/18

Introduction to PL/SQL (Series 3) Cursors
By David Nishimoto on 10/14

Sorting a String using T-SQL
By Gaurav Pugalia on 10/12

Protecting against TSQL virii, worms and time bombs
By Joseph Gama on 10/11

Get size in bytes of SP, View, Trigger, UDF or Rule
By Joseph Gama on 10/11

(Screen Shot)

Click here to put this ticker on your site!

Add this ticker to your desktop!

Daily Code Email

To join the 'Code of the Day' Mailing List click here!

Affiliate Sites

How to connect SQL Server 7.0 to Informix

	Print
	Email

Submitted on: 8/23/2000 8:35:17 AM
By: Artur Angielski
Level: Intermediate
User Rating:

By 2 Users
Compatibility:SQL Server 7.0, Informix

Users have accessed this article 8525 times.

This article is providing information how to connect your SQL Server database to Informix database, and how to use queries exchanging data between Informix and SQL. It also might be helpfull, when you need connection to other ODBC sources.

Terms of Agreement:
By using this article, you agree to the following terms...
1) You may use this article in your own programs (and may compile it into a program and distribute it in compiled format for languages that allow it) freely and with no charge.
2) You MAY NOT redistribute this article (for example to a web site) without written permission from the original author. Failure to do so is a violation of copyright laws.
3) You may link to this article from another website, but ONLY if it is not wrapped in a frame.
4) You will abide by any additional copyright restrictions which the author may have placed in the article or article's description.

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)

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

Report Bad Submission

Your Vote!

Other User Comments

9/21/2000 5:53:00 AM:Nigel Callahan
Have you ever got the OLEDB API to work through linked servers. I can use DTS but not heterogenious queries. The setup seems critical.

5/25/2001 10:04:40 AM:Daniel DeMoura
Is it possible to extend the DMO with setup?

12/11/2001 9:41:57 AM:Mark Samuels
How does one find the name of service for informix

Add Your Feedback!

Note:Not only will your feedback be posted, but an email will be sent to the code's author in your name.

NOTICE: The author of this article has been kind enough to share it with you. If you have a criticism, please state it politely or it will be deleted.

For feedback not related to this particular article, please click here.