SBX - Search With Button

SBX - Forum Post Title

An exception occurred in the query metadata execution

Microsoft Dynamics AX Forum

Retheesh asked a question on 14 Sep 2018 4:41 AM
My Badges

Question Status

Verified

Hi,

I am trying to get a result set from stored procedure and insert into  a temp table.

While running I am gettting an error

"Cannot execute the required database operation.
The operation failed.
[Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor."

Code

Public void insert_packingSlip()
{
    Connection      connection;
    Statement       statement;
    str             query;
    Resultset       resultSet;
    ;

    connection = new Connection();

    statement = connection.createStatement();
    query = "Execute Rpt_Get_Packing_Slip1";
    new SqlStatementExecutePermission(query).assert();

    resultSet = statement.executeQuery(query);

    TmpPackingSlip.clear();
    while(resultSet.next())
    {
        TmpPackingSlip.CUSTACCOUNT = resultSet.getString(1);
        TmpPackingSlip.SALESID = resultSet.getString(2);
        TmpPackingSlip.STANDARDCONFIGID  = resultSet.getString(3);
        TmpPackingSlip.PRODUCTTYPECODE = resultSet.getString(4);
        TmpPackingSlip.DESCRIPTION = resultSet.getString(5);
        TmpPackingSlip.ARTICLE_CODE = resultSet.getString(6);
        TmpPackingSlip.PCS = resultSet.getReal(7);
        TmpPackingSlip.GROSSWT = resultSet.getReal(8);
        TmpPackingSlip.NETWT = resultSet.getReal(9);
        TmpPackingSlip.PUREQTY = resultSet.getReal(10);
        TmpPackingSlip.STONE = resultSet.getReal(11);
        TmpPackingSlip.PURITY = resultSet.getReal(12);
        TmpPackingSlip.TOTALVALUE = resultSet.getReal(13);
        TmpPackingSlip.MAKINGVALUE = resultSet.getReal(14);
        TmpPackingSlip.GOLDANDSTONEVALUE = resultSet.getReal(15);

        TmpPackingSlip.insert();
    }
    CodeAccessPermission::revertAssert();
}

thank you.

Reply
Martin Dráb responded on 14 Sep 2018 5:29 AM

Does the stored procedure work if you execute it directly in SQL?

Reply
Retheesh responded on 14 Sep 2018 6:22 AM
My Badges

Yes. And the actual error is

"Error 1 An exception occurred in the query metadata execution. The exception is CC491321309

Cannot execute the required database operation.

The operation failed.

[Microsoft][SQL Server Native Client 10.0][SQL Server]Executing SQL directly; no cursor.

"

"CC491321309" this is the custaccount field returning from the query.

Reply
Martin Dráb responded on 14 Sep 2018 7:23 AM

What exactly are you returning from your SP? Is it a result set?

Maybe you should us your code (or ideally a simplified code demonstrating the problem, without code not relevant to the issue).

Reply
Retheesh responded on 14 Sep 2018 7:29 AM
My Badges

I am returning a result set from SP and store that in a temporary table for SSRS report.

Reply
Martin Dráb responded on 14 Sep 2018 7:49 AM

I'm talking about the content of your SP. Things like the temporary table are in AX, therefore you're talking about something else. Please show us the code of your stored procedure.

By the way, what's the thing that you can't implement in AX and you're resorting to T-SQL? Maybe we can help you to find a solution.

Reply
Retheesh responded on 14 Sep 2018 7:58 AM
My Badges

Thank you for quick support.

I am a beginner in X++ coding. That's why using SP for data. My requirement is to print packing slip for a sales order . I need to pass sales id parameter to get the report.

I need to open the packing slip ssrs report on a button click in sales list(for selected sales order).

my SP

GO

/****** Object:  StoredProcedure [dbo].[Rpt_Get_Packing_Slip]    Script Date: 14/09/2018 4:50:54 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- =============================================

ALTER PROCEDURE [dbo].[Rpt_Get_Packing_Slip]

@saledID NVARCHAR(20)

AS

BEGIN

SET NOCOUNT ON;

SELECT        a.CUSTACCOUNT, b.DATAAREAID, b.SALESID, d.STANDARDCONFIGID + ' ' + d.PRODUCTTYPECODE + ' ' + e.DESCRIPTION AS DESCRIPTION, d.PRODUCTTYPECODE, d.ARTICLE_CODE, d.STANDARDCONFIGID,

                        SUM(b.PDSCWQTY) AS PCS, SUM(b.SALESQTY) AS GROSSWT, SUM(c.QTY) AS NETWT, SUM(c.PUREQTY) AS PUREQTY, SUM(b.SALESQTY) - SUM(c.QTY) AS STONE, SUM(c.PUREQTY) / SUM(c.QTY) AS PURITY,

                        SUM(b.LINEAMOUNT) AS TOTALVALUE, SUM(b.MKVALUE) AS MAKINGVALUE, SUM(b.LINEAMOUNT) - SUM(b.MKVALUE) AS GOLDANDSTONEVALUE

FROM            SALESLINEINGREDIENT AS c INNER JOIN

                        SALESLINE AS b ON c.SALESID = b.SALESID AND c.LINEITEMID = b.ITEMID AND c.DATAAREAID = b.DATAAREAID INNER JOIN

                        SALESTABLE AS a ON b.SALESID = a.SALESID AND b.DATAAREAID = a.DATAAREAID INNER JOIN

                        INVENTTABLE AS d ON b.ITEMID = d.ITEMID AND a.DATAAREAID = d.DATAAREAID INNER JOIN

                        ARTICLE_MASTER AS e ON d.ARTICLE_CODE = e.ARTICLE_CODE

WHERE        (b.PARTITION = '5637144576') AND (c.PARTITION = '5637144576') AND (c.METALTYPE = 1) AND (b.SALESSTATUS IN (2, 3)) AND (a.PARTITION = '5637144576') AND (d.PARTITION = '5637144576') AND

                        (e.PARTITION = '5637144576')

GROUP BY b.DATAAREAID, b.SALESID, a.CUSTACCOUNT, d.ARTICLE_CODE, d.STANDARDCONFIGID, e.DESCRIPTION, d.PRODUCTTYPECODE

HAVING        (b.SALESID = @saledID)

ORDER BY b.DATAAREAID, b.SALESID

END

Reply
Martin Dráb responded on 14 Sep 2018 8:38 AM

You shouldn't use direct SQL code in AX; it has many problems. Not only it's difficult and error-prone (as you've already found), but it also bypasses all compile-time checks, enforcement of security, caching and so on. You also have to deal with all implementation details (such as including DataAreaId in relations) manually.

If you do it in the right way in AX, you won't have to deal with partition IDs and data area IDs, you can let AX use table relations already defined in the application, you won't have to deal with numeric values of base enums and so on. AX will check that all names are correct, it will maintain cross-references of the code and so on and so on.

I strongly recommend you throw away your stored procedure and write the query inside AX.

Reply
Martin Dráb responded on 14 Sep 2018 8:49 AM

Here is a little piece of code to help you start:

Query q = new Query();
QueryRun qr;
SalesLine salesLine;

QueryBuildDataSource salesTableDs = q.addDataSource(tableNum(SalesTable);
QueryBuildDataSource salesLineDs = salesTableDs.addDataSource(tableNum(SalesLine);
salesLineDs.relations(true);

salesTableDs.addRange(fieldNum(SalesTable, SalesId)).value(queryValue("SO000123"));

salesTableDs.addGroupByField(fieldNum(SalesTable, CustAccount));

salesLineDs.addSelectionField(fieldNum(SalesLine, LineAmount), SelectionField::Sum);

qr = new QueryRun(q);

while (qr.next())
{
    salesLine = qr.get(tableNum(SalesLine));
}

It uses the query framework. You can also use select statements in X++, which is often easier to read, but it's less flexible.

Don't forget that AX comes with documentation, such as How to: Create Queries by Using X++ and Select Statements.

Reply
Retheesh responded on 16 Sep 2018 3:23 AM
My Badges
Verified Answer

Thank you Martin Drab.

My issue solved. I wrote query inside ax and now report is working fine.

Reply
Retheesh responded on 16 Sep 2018 3:23 AM
My Badges
Verified Answer

Thank you Martin Drab.

My issue solved. I wrote query inside ax and now report is working fine.

Reply

SBX - Two Col Forum

SBX - Migrated JS