Oninit cdi / Archive
Talk to an expert →

SqlServer linked server to informix

Posted in 2008

Topics: Server Administration, Security, Permissions & Auditing

ANTHONY JUDISH — — source: IIUG Forums & Mailing Lists

We are using IDS 11.1 on HPUX 11.11, SqlServer 2005. We have created a linked server in SqlServer to IDS. We are have strange behavior when trying to update one table in the IDS instance. Table schema: { TABLE "informix".csta_user_whse row size = 43 number of columns = 4 index size = 40 } create table "informix".csta_user_whse ( csta_user_whse_id char(10) not null , user_code nchar(20), whse_code char(12), active nchar(1) ); revoke all on "informix".csta_user_whse from "public"; create index "informix".csta_user_whseix1 on "informix".csta_user_whse (user_code) using btree ; create unique index "informix".csta_user_whseix2 on "informix" .csta_user_whse (csta_user_whse_id) using btree ; alter table "informix".csta_user_whse add constraint primary key (csta_user_whse_id) constraint "informix".user_code_pk ; Sample data. csta_user_whse_id user_code whse_code active 8 SMILLER 0402 Y 9 smiller 0500 N 10 smiller 0401 11 smiller 0400 N When the following update is run from within the SQlserver query runner, it updates the wrong row. If the update should affect the last row, it actually updates the first row. If it is done on any other row,it works correctly. update ERP_TRNG.fms441.informix.csta_user_whse set whse_code = '2323' where csta_user_whse_id = '11' If we do the update in dbaccess, it works correctly. If we use OpenQuery and the following syntax, the update works correctly. update rt set whse_code = '2323' from openquery(ERP_TRNG, 'select * from csta_user_whse where csta_user_whse_id = ''gfdsa''') as rt I know this is more than likely a SqlServer question, but wanted to see if anybody out there has seen this. Thanks, AJ

↪ replying to ANTHONY JUDISH
Sheshnarayan Agrawal — — source: IIUG Forums & Mailing Lists

>When the following update is run from within the SQlserver query runner, it >updates the wrong row. If the update should affect the last row, it actually >updates the first row. If it is done on any other row,it works correctly. >update ERP_TRNG.fms441.informix.csta_user_whse >set whse_code = '2323' >where csta_user_whse_id = '11' Could you take ODBC trace and SQLI trace and see what's the query being sent to IDS? -Shesh "ANTHONY JUDISH" <ajudish@lextron-inc.com> Sent by: ids-bounces@iiug.org 21/05/2008 22:55 Please respond to ids@iiug.org To ids@iiug.org cc Subject SqlServer linked server to informix [12173] We are using IDS 11.1 on HPUX 11.11, SqlServer 2005. We have created a linked server in SqlServer to IDS. We are have strange behavior when trying to update one table in the IDS instance. Table schema: { TABLE "informix".csta_user_whse row size = 43 number of columns = 4 index size = 40 } create table "informix".csta_user_whse ( csta_user_whse_id char(10) not null , user_code nchar(20), whse_code char(12), active nchar(1) ); revoke all on "informix".csta_user_whse from "public"; create index "informix".csta_user_whseix1 on "informix".csta_user_whse (user_code) using btree ; create unique index "informix".csta_user_whseix2 on "informix" ..csta_user_whse (csta_user_whse_id) using btree ; alter table "informix".csta_user_whse add constraint primary key (csta_user_whse_id) constraint "informix".user_code_pk ; Sample data. csta_user_whse_id user_code whse_code active 8 SMILLER 0402 Y 9 smiller 0500 N 10 smiller 0401 11 smiller 0400 N When the following update is run from within the SQlserver query runner, it updates the wrong row. If the update should affect the last row, it actually updates the first row. If it is done on any other row,it works correctly. update ERP_TRNG.fms441.informix.csta_user_whse set whse_code = '2323' where csta_user_whse_id = '11' If we do the update in dbaccess, it works correctly. If we use OpenQuery and the following syntax, the update works correctly. update rt set whse_code = '2323' from openquery(ERP_TRNG, 'select * from csta_user_whse where csta_user_whse_id = ''gfdsa''') as rt I know this is more than likely a SqlServer question, but wanted to see if anybody out there has seen this. Thanks, AJ ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Glossary