Windows Server

James White · Guest

Rob,

I hate to trouble you, but sources for information on HIS2004 are not in
great supply. I’m using the MsDb2 data provider, and calling a simple stored
procedure on an AS400. When I execute the command, I always get an error (no
description) that mentions SQLState 54001 and SQLCode 101 which relates to
the query being “too complex”. The stored procedure is pretty simple (a
select statement) and using the exact same syntax with the DataDirect
provider everything works fine. I’d prefer to use your provider though and
would welcome any assistance you can provide.

Thanks!

James

Error:

A SQL error has occurred. Please consult the documentation for your
specific DB2 version for a description of the associated Native Error and SQL
State. SQLSTATE: 54001, SQLCODE: -101

Procedure:

CREATE PROCEDURE DEVJPW.GETINVEMLORD ( )
RESULT SETS 1
LANGUAGE SQL
------------------------------------------------------------------------
-- SQL Stored Procedure
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR FOR
SELECT * FROM AMFLIBEXT.TRADEADF
WHERE NTFYIT <> '1';

-- Cursor left open for client application
OPEN cursor1;
END P1

Code:

Microsoft.HostIntegration.MsDb2Client.MsDb2Connection connection = new
Microsoft.HostIntegration.MsDb2Client.MsDb2Connection(
"Network Address=192.168.123.5;Network Port=446;User
ID=TEST;Password=TEST;Initial Catalog=S103Y13M;Network Transport
Library=TCP;Host CCSID=37;Package Collection=QGPL");

connection.DBMSPlatform =
Microsoft.HostIntegration.MsDb2Client.DBMSPlatform.DB2AS400;

connection.Open();

Microsoft.HostIntegration.MsDb2Client.MsDb2Command command = new
Microsoft.HostIntegration.MsDb2Client.MsDb2Command("DEVJPW.GETINVEMLORD",
connection);

command.CommandType = CommandType.StoredProcedure;

using (IDataReader reader = command.ExecuteReader()) // ß Error
happens here
{
while (reader.Read())
{
object o = reader["AORD$T"];
}
}

connection.Close();

James White · Guest

<Question Omitted>

Ok, so answering my own question, the problem was either the number of
columns, or the number of rows that the procedure was returning. When I
changed * to a couple of columns (yeah, I know, * bad.. * bad..) and filtered
down to a much smaller result set the error went away. I have to think that
this is a bug in the provider though because the DataDirect version had no
trouble passing back all the data.

Thanks!

James

Jeff Derstadt [MSFT] · Guest

Hi James,

How many columns are being returned (and do you know their types and
sizes)? If you return the full result set, but limit the columns, does it
still work? Error 54001 is a generic IBM error for some limit system limit
being encountered. It also deals with the cumulative sizes of the columns
and any LOB columns. The HIS 2004 provider does not support LOB data types,
so if the result set was returning a LOB data item, the HIS provider would
not be able to support its retrieval and it could cause problems returning
the query in general.

Jeff Derstadt
Microsoft

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of any included samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm

--------------------
Thread-Topic: MsDb2Client & Stored Procedure = "Too Complex (54001/101)" ?
thread-index: AcVVqw9iy9+ptP/MQe6nEamVvciLmw==
X-WBNR-Posting-Host: 12.32.229.6
From: "=?Utf-8?B?SmFtZXMgV2hpdGU=?=" <James White@discussions.microsoft.com>
References: <725B118E-B656-4E45-854E-A44176B65CFB@microsoft.com>
Subject: RE: MsDb2Client & Stored Procedure = "Too Complex (54001/101)" ?
Date: Tue, 10 May 2005 14:56:03 -0700
Lines: 12
Message-ID: <296A01B2-5E2B-4208-9154-E1EBB0129132@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
Newsgroups: microsoft.public.hiserver.general
NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 10.40.2.250
Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTNGXA03.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.hiserver.general:1656
X-Tomcat-NG: microsoft.public.hiserver.general

<Question Omitted>

Ok, so answering my own question, the problem was either the number of
columns, or the number of rows that the procedure was returning. When I
changed * to a couple of columns (yeah, I know, * bad.. * bad..) and
filtered
down to a much smaller result set the error went away. I have to think that
this is a bug in the provider though because the DataDirect version had no
trouble passing back all the data.

Thanks!

James

MsDb2Client & Stored Procedure = "Too Complex (54001/101)" ?

	Windows Server Forum Index -> Host Integration Server	All times are GMT
Page 1 of 1