Connect to Informix DB through SQL Server stored-procedure

Posted on 2006-04-03
Last Modified: 2006-11-18
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!

Question by:paulop1975
    1 Comment
    LVL 9

    Accepted Solution

    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.

    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.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Join & Write a Comment

    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    A short article about problems I had with the new location API and permissions in Marshmallow
    In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now