Does x3 accept dynamic sql in stored procedure?

SUGGESTED

If my stored procedure contains the statement below. It works.

select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from ...ITMMASTER

If I change to dynamic sql below,

declare @sqlTextAll nvarchar(max)
set @sqlTextAll='select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from ...ITMMASTER'
exec @sqlTextAll

it say "

[Microsoft][SQL Server Native Client 11.0][SQL Server]EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time. : SQL Server Error

"

This is a sample to explain my problem. My case has to use dynamics table and field names. That is why dynamics sql is involved.

  • 0

    What happens if you change your call to the following, accounting for the difference in the schema of course. 

    set nocount on
    declare @sqlTextAll nvarchar(max)
    set @sqlTextAll='select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from SEED.ITMMASTER'
    exec (@sqlTextAll)

    It would also be nice to see your stored procedure and how you called it. 

  • 0 in reply to Delamater

    Sorry for my type. It was exec (@sqlTextAll) in my test. I removed the schema in my question since it was reference to my client name. Here is the correction:

    declare @sqlTextAll nvarchar(max)
    set @sqlTextAll='select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from [folder name].ITMMASTER'
    exec (@sqlTextAll)

  • 0 in reply to Delamater

    Here is the stored procedure. I replaced the schema name with [folder name]. I am sure it is correct since if I change to the select statement directly, I can get the result in X3 codes. I am not using these parameters. The values are all ''.

    ALTER PROCEDURE [dbo].[sp_CUSTPROPRISTOTEST]
    -- Add the parameters for the stored procedure here
    @poscod nvarchar(10), @customer nvarchar(30), @item1 nvarchar(250), @item2 nvarchar(250), @item3 nvarchar(250), @item4 nvarchar(250), @item5 nvarchar(250), @item6 nvarchar(250), @item7 nvarchar(250), @item8 nvarchar(250), @item9 nvarchar(250), @item10 nvarchar(250), @item11 nvarchar(250),
    @site1 nvarchar(10),@site2 nvarchar(10),@site3 nvarchar(10),@site4 nvarchar(10),@site5 nvarchar(10)

    AS
    BEGIN

    declare @sqlTextAll nvarchar(max)
    set @sqlTextAll='select top 10 ITMREF_0 as ITMREF,ITMDES1_0 as ITMDES from [folder name].ITMMASTER'
    exec (@sqlTextAll)

    end

    Here is how it calls in X3:

    KREQSTR(1)="EXEC [database].dbo.[sp_CUSTPROPRISTOTEST] @poscod = N'"+POSCOD+"',@customer = N'"+BPCNUM+"'"
    KREQSTR(2)=",@item1 = N'"+ITMSEARCH+"'"
    KREQSTR(3)=",@item2 = N'"+ITMSEARCH1+"'"
    KREQSTR(4)=",@item3 = N'"+ITMSEARCH2+"'"
    KREQSTR(5)=",@item4 = N'"+ITMSEARCH3+"'"
    KREQSTR(6)=",@item5 = N'"+ITMSEARCH4+"'"
    KREQSTR(7)=",@item6 = N'"+ITMSEARCH5+"'"
    KREQSTR(8)=",@item7 = N'"+ITMSEARCH6+"'"
    KREQSTR(9)=",@item8 = N'"+ITMSEARCH7+"'"
    KREQSTR(10)=",@item9 = N'"+ITMSEARCH8+"'"
    KREQSTR(11)=",@item10 = N'"+ITMSEARCH9+"'"
    KREQSTR(12)=",@item11 = N'"+ITMSEARCH10+"'"
    KREQSTR(13)=",@site1 = N'"+SITES(1)+"',@site2 = N'"+SITES(2)+"',@site3 = N'"+SITES(3)+"',@site4 = N'"+SITES(4)+"',@site5 = N'"+SITES(5)+"'"
    KREQSTR(14)=" with RESULT SETS ((ITMREF nvarchar(30), ITMDSC nvarchar(30),ITMDSC2 nvarchar(30)))"

    For (Char ITMREF,Char ITMDSC,Char ITMDES3) From "5" Sql
    & KREQSTR(1..15) As [YSITES]
    Infbox num$([YSITES]ITMREF)
    Break

    Next

    The interesting thing is I have to add one more field "ITMDSC2" into result sets. Otherwise, x3 will return SQL function error without any details.

    The store procedure is under another database, not X3 itself. The stored procedure has permitted to x3 schema.

  • 0 in reply to Sheila Zhang
    SUGGESTED

    Did you add the nocount in? Does it help? Also, is there a trigger sending back information as well? If so, temporarily disable the trigger and re-test. 

    Also, can you execute this statement in SSMS? I would expect it to complain because your result set specifies 3 columns but your stored procedure returns two. Thus, I would expect you to receive an error 11537 from SQL as a result. 

    In any case, when the runtime executes a read or a FOR loop it has to leverage the ODBC drivers for SQL or the SQL Native Client (SNAC ODBC), which then returns a result set in the form of the tabular data stream back to the runtime. In order to give the 4GL developers a way to interact with these sets we map the results into buffers. At that time we determine the result set along with the data types and fill the buffers. I would have to look, but if TDS is responding with two different schema specifications, I think that could be a problem as the data type and name are potentially different than what we mapped into the buffers already. 

    When you execute without the RESULT SETS clause does it work for you? I'm asking because I can think of two different options, which is to query without the stored proc or to execute the proc into a table and then query the table which would then allow you to alias the columns as you like.