One year ago I accountered this situation: We had one "MS SQL Server" that helds central product nomenclatures and several "Informix 7.x SQL Servers" with operational databases.
My task was to automate sinchronisation of these nomenclatures. We tryed several approaches, but none was enough stable and transparent to guarantee "hands free" operations.
Then I decidet to try to use the linked server connectivity of MSSQL. Actually writing through "linked server" is not supported, but you can call a parameterised procedure that resides in the Informix Server.
The final result was a pack of procedures in MSSQL and a pack of procedures in Informix. The transport was relatively quick and supported transactions and error handling in the Informix side.

That scheme automated our nomenclatures and worked all alone for one year.
Now we work with new IS and all that is history.
I'm cleaning my wardrobe so if someone needs such tools, just write back your e-mail and I'll send some code.

All the best

I would be very interested in some of the utilities that you have used/written.

Jeremy

Sory for the delay, but I was busy pasking it all

The sources I've sent you are grouped in 3 parts.

1) The first part contains the tables and procedures that reside in the MSSQL server database. Some of the tools in that part are universal so they could be used for general purposes. The main instrument is the procedure "pds_tran_handler1".
This proceduere is used to start transaction in the informix side of the transfer, to transfer data and commands and to commit the transaction in the informix side. It also handles error conditions in both Informix and MSSQL sides.
The base of the data transfer to the informix database is procedure "pds_ttab2params2". It converts a temporary table into character data stream which is received by procedure "pds_put_tcmds" in the informix side.

2) The second part of the script contains the tables and procedures that reside in the Informix server database. The first three of the procedures are the primary instruments:
- "pds_get_tcode": starts the transaction in the informix side
- "pds_put_tcmds": receives the character stream of data, transforms it and puts it into the transaction buffer
- "pds_exec_tran": commits the transaction in the informix side; In that procedure you had to add code for operations you want to execute;
Procedure "pds_h_newcli" is an example how you can expand the flexibility of operation execution schema.

3) The third part of the script is an example stored procedure for MSSQL server that illustrates how data could be sent and written to the informix.



All of that code I wrote because I didn't found a way to write directly from MSSQL to Informix. If there is a simple way I will probbably look stupid but this thing has done its jobs well and stable.

If I missed something in the pack just write back and I'll complete it. If these tools work well I'll be glad to hear your story.

Good Luck and All The Best

Hi Mr.Danko,
I'm interested for your samples because I have a problem. I also have an Infomix server configured as linked server to a SQL Server 2000 server. I can read Informix data via a syntax like "select * from openquery(INFORMIX,'select * from a_informix_table')" and write data using "select * from openquery(INFORMIX,'execute procedure an_informix_storproc(''par1'',''par2'')')". My problem it's that I can not execut succesfully ROLLBACK onto the INFORMIX server. I don't give error messages but the data stay changed. Obviously I call the Informix stored procedure inside a explicit transaction as:
BEGIN TRAN
select * from openquery(INFORMIX,'execute procedure an_informix_storproc(''par1'',''par2'')')
ROLLBACK

Please, can you send me your utilities?

My Best Regards

Pierpaolo Simoncini
TXT e-solutions s.p.a.

Hi Pierpaolo !

I'm sending 3 script files which are partially described in my previous post in the forum. I've tryed to document the sources inside the scripts but it's possible that i've missed some. So feel free to mail if you need additional info.

If it works (as I hope) skratch a word. I'll be glad

Wish you success.

Hi, if it's also possible to send the scripts to me as well, it would be much appreciated. Thanks!

Hi Danko,

I know that this post is a little bit old, but I will be very helpful for me if I can get your scripts. Thanks in advance and keep up with the good work .

Thanks!