Dynamics Community Forum Thread Details

Hi.

Actually our requirement is to know the pending Purchase Requisitions(PR's). i mean we want a report so that we can know like with which user and since which date the PR is pending.

So i wrote a stored procedure in SQL which does this task. This Stored procedure shows me all i require.

Basically i want to publish this Stored procedure in AX client to the users so that they can track the pending PR's.

So i want to call this stored procedure from AX.

I created a class then put my code inside it and assigned it to a menu item. Currently i am displaying the output in "Info". But i want to display my output as a report .

so for this i created a temperary table and trying to insert the records fetched from that stored procedure and later use that temp table and design a report. But i am getting a error.

Below is my code :

server static client void executeQuery()
{
    System.Exception netExcepn;
    Connection con = new Connection();
    Statement stmt = con.createStatement();
    ResultSet r;
    str sql;
    SqlStatementExecutePermission perm;

    int iCount;
    str resultString;
    TmpPRStatus TmpPRStatus;

;

     sql = strfmt('EXEC dbo.EMI_PRTrackPendingCases');    // My stored procedure Name
       perm = new SqlStatementExecutePermission(sql);
        perm.assert();

   try
   {
        R = stmt.executeQuery(sql);
        while ( R.next() )
            {

                 resultString = strfmt(' %1   : %2:    %3:    %4:    %5:    %6: %7 \n ',
                                                      R.getString(1),
                                                      R.getString(2),
                                                      R.getString(3),
                                                      R.getString(4),
                                                      R.getString(5),
                                                      R.getString(6),
                                                      R.getString(7));

                setprefix('Pending Purchase Requisition');
                info( resultString);

               ttsbegin;

                 while select forupdate TmpPRStatus
                 {
                    TmpPRStatus.Originator    = R.getString(1);
                    TmpPRStatus.PendingWith   = R.getString(2);
                    TmpPRStatus.PurchReqId    = R.getString(3);
                    TmpPRStatus.Description   = R.getString(4);
                    TmpPRStatus.SubmittedDate = R.getString(5);
                    TmpPRStatus.PendingSince = R.getString(3);

                 }
                    TmpPRStatus.insert();
                    ttscommit;
                   }

}
    catch (exception::Error)
    {
        netExcepn = CLRInterop::getLastException();
        print "An error occured in the query.";
        pause;
}
CodeAccessPermission::revertAssert();

}

when i run the above code i get this below error.

Request for the permission of type 'InteropPermission' failed.
(S)\Classes\InteropPermission\demand
(S)\Classes\CLRInterop\getLastException

Please Guide me with this error.

Thanks.

Community site session details

InteropPermission Error in calling a stored procedure from AX 2009

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates