IDS Forum

Re: SQL Server and Informix

Posted By: Sheshnarayan Agrawal
Date: Thursday, 7 August 2008, at 10:07 a.m.

In Response To: SQL Server and Informix (Jerry Hamilton)

I am not sure if this gives you any clues ..but this is what I tested and
found to be working fine ...

IDS : 11.50.TCB3 On Windows XP
CSDK : 3.50.TC1 on Windows XP

SQL Server 2008 on Windows XP

I created LINKED SERVER to IDS from SQL Server using Informix OLEDB
Provider. Created following schema ...

create table comments
(
report_name char(30) not null ,
fiscal_year smallint not null ,
accounting_period smallint not null ,
fh_report_section char(30) not null ,
group_id char(15) not null ,
seq_nbr decimal(15,0) not null ,
comment_text char(250) not null ,
comments char(292)
);

and ran the openquery as follows,

select * from openquery (OLEDB_IFMX, 'select * from comments');

I didn't get any error, it didn't return any row as it doesn't have any.
Also I tried only one column as follows with 292 bytes of data and it
retrieved data successfully ...

create table iiug_292 (col1 char(292);
insert into iiug_292 values (<292 bytes of data>);

And ran the openquery as follows ... I got entire 292 bytes of data
retrieved successfully ...

select * from openquery (OLEDB_IFMX, 'select * from iiug_292');

HTH
-Shesh

"Jerry Hamilton" <bigdaddyjerry@yahoo.com>
Sent by: ids-bounces@iiug.org
05/08/2008 20:04
Please respond to
ids@iiug.org

To
ids@iiug.org
cc

Subject
SQL Server and Informix [12988]

Environment:
HP-UX 11.11
Windows 2003 sp2

IDS 9.40FC7
SQlServer 2005

SDK 3.00TC1

Issue:

Cannot select data from Informix when column is greater than 291 bytes.

I created an Informix table called jerry_comments:

create table "psoft8".jerry_comments
(

report_name char(30) not null ,

fiscal_year smallint not null ,

accounting_period smallint not null ,

fh_report_section char(30) not null ,

group_id char(15) not null ,

seq_nbr decimal(15,0) not null ,

comment_text char(250) not null ,

comments char(292)
) in frpapp extent size 16 next size 16 lock mode row;
revoke all on "psoft8".jerry_comments from "public";

The select from SQL Server looks like:
select * from openquery (fsdev82, 'select * from jerry_comments')

The error looks like:
Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the
server. (provider: Shared Memory Provider, error: 0 - No process is on
the other end of the pipe.)

The select will work if I change the comments field to anything less
than 292 bytes.

We also tested reading from one SQLServer database to another SQLServer
database with the comments column greater than 292 and it works fine for
any size...

Anyone else have an issue trying to read more than 291 bytes into SQL
Server?

*******************************************************************************

Forum Note: Use "Reply" to post a response in the discussion forum.

Messages In This Thread

• SQL Server and Informix
  Jerry Hamilton -- Tuesday, 5 August 2008, at 10:34 a.m.
  • Re: SQL Server and Informix
    Sheshnarayan Agrawal -- Thursday, 7 August 2008, at 10:07 a.m.
    • Re: SQL Server and Informix
      Jerry Hamilton -- Friday, 8 August 2008, at 12:06 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.