First, this is an IB database so be gentle. It's a version 6.0.2 open source which is around the time of the great fork off to FB so I'm hoping it's not too out of line here. It's also a dialect 1 database.

Here's the scenario: The client experienced an error, and true to Murphy's law, they neither wrote down nor noted what it was. It involved a certain table called MATINVENTORY. There is a stored procedure which returns records for the user and the program has used that for a long time. Now, however, the SP throws the subject line error message.

***********************************************
Examples of a call that works:

select * from InventoryList(20)

select * from InventoryList(20) where ItemNum = 1

select * from InventoryList(20) where ItemNum is not null

***********************************************
Examples of calls that don't work:

select * from InventoryList(20) where ItemNum is null

select * from InventoryList(20) where HeatCode containing 'xxxx' {where xxxx could be numbers}

select * from InventoryList(20)
union
select * from InventoryList(22)
***********************************************

Now, here's an oddity. The passed parameter code can be one of (20 - 25). Only certain codes (20, 24, 25) cause the problem. Codes 21 - 23 work fine for all queries.

Here's another twist. I can copy the query directly from the SP code and it works fine under all circumstance as well. That leaves me to conclude that something in the transfer of the data from the query to the output parameters of the SP must be what trigger the error. I don't think it's the problem, only the indication of it. None of the fields in question have been changed or altered so it's not a size incompatibility problem, IMO.

I have run backup/restore, gfix, sweep, etc. and nothing clears up the problem. I copied the table structure and the records and the same thing exists on the new table when creating a new SP to use it. I dropped and re-created the SP just to be sure it wasn't corrupted somehow.

Any ideas? DML can be posted if necessary.

Woody (TMW)



[Non-text portions of this message have been removed]