Query must return exactly one result set - use Execute

[lcoelho]

Hi, I'm busy migrating from a BDE project to SDAC 5. On one of the queries (TMSQuery) I have migrated, I receive the following error:

'Query must return exactly one result set - use Execute.'

The only thing I changed was to migrate from InfoPower's TwwQuery component to TMSQuery.

Here is the SQL property of the query component:

declare @Next integer

select @Next = max(ProfileListHeadNbr) + 1
from Communicationprofilelisthead

if (@next is null)
select @next = 1000

insert into Communicationprofilelisthead
select distinct @Next,getDate(),null


select @Next NextKey

Here is the code that triggers the error:
with TMSQueryInsertCommHeader do
begin
close;
prepare;
open; // <-- Error occurs here (I have tried using Execute instead but it still gives this error)
end;
bCommHeaderGen := true;
StoreHeaderNumber :=
wwQueryInsertCommHeaderNextKey.value;

When I run the SQL code in SQL query analyzer, it works fine. Additionally, this error does not occur if I use Delphi's ADO components.

Kindly let me know where the problem could be.

Thank you.

[lcoelho]

Sorry, I've found the problem.

According to SDAC documentation, SQL statements which have output parameters and aren't stored procedures calls or some of system functions such as sp_setapprole, should be executed without prior call to the Prepare method.

Therefore, I removed the 'Prepare' statement before the 'Open' method and it worked fine.

Thank you

[AndreyZ]

It's good to see that you've found a solution.