Connect to Informix DB through SQL Server stored-procedure

Hi, All!

What I need is to access an Informix database using a MS SQL Server 2000 stored-procedure. These are the steps I need to develop:

1. Connect to an Informix database using an ODBC connection which I already have configured;
2. Fetch data from the Informix database (it's a very simple SELECT statement);
3. Write the result of that query to a text file, using however any file manipulation objects (I believe they exist).

In conclusion, what I need to know is:

1. How to use Informix ODBC connection on a SQL Server 2000 stored-procedure.
2. How to create (or not) any kind of cursors which can help me fetch data.
3. How to write to a text file or what objects may I use to edit text files on the hard disk.

Can someone please help on this one? I don't really know much about stored-procedures in SQL Server, however I have good programming background, which will make it easy for me to understand any solution or information you may provide me.

Thankx in advance!

Paulo
LVL 17
Paulo PimentaProject ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gabesoSolution ArchitectCommented:
It'll be something like this but you'll need to identify the exact name of the informix driver in {Informix}: Just fill in all the bits between <> characters.

SELECT
  *
FROM OPENROWSET(  'MSDASQL', 'DRIVER={Informix};SERVER=<Server>;UID=<username>;PWD=<password>',   <table>)

You run this in sql server.

If you export the data from Informix to sql server you can then manipulate it there.

As for getting data to text files, csv files or whatever you can use DTS from the sql server enterprise manager - it will do everything you want.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Programming

From novice to tech pro — start learning today.