Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / An exception occurred ...
Microsoft Dynamics AX (Archived)

An exception occurred in the query metadata execution

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

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.

*This post is locked for comments

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: An exception occurred in the query metadata execution

    Thank you Martin Drab.

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

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: An exception occurred in the query metadata execution

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: An exception occurred in the query metadata execution

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: An exception occurred in the query metadata execution

    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

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: An exception occurred in the query metadata execution

    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.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: An exception occurred in the query metadata execution

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

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: An exception occurred in the query metadata execution

    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).

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: An exception occurred in the query metadata execution

    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.

  • Martin Dráb Profile Picture
    Martin Dráb 230,198 Most Valuable Professional on at
    RE: An exception occurred in the query metadata execution

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans