Oninit cdi / Archive
Talk to an expert →

Re: SQL Server and Informix

Posted in 2008

Topics: Storage & Space Management, Security, Permissions & Auditing, Platform-Specific Issues, Versions, Editions & End-of-Life

Sheshnarayan Agrawal — — source: IIUG Forums & Mailing Lists

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.

↪ replying to Sheshnarayan Agrawal
Jerry Hamilton — — source: IIUG Forums & Mailing Lists

Thanks. I'm going to try CSDK 3.5 and see how that works. --- On Thu, 8/7/08, Sheshnarayan Agrawal <shagrawal@in.ibm.com> wrote: > From: Sheshnarayan Agrawal <shagrawal@in.ibm.com> > Subject: Re: SQL Server and Informix [13020] > To: ids@iiug.org > Date: Thursday, August 7, 2008, 9:07 AM > 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. > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in > the discussion forum.

Glossary