Execute Stored Procedure from X++ code

To execute a stored procedure from X++ use the Server method, the Client method does not have permissions. Following is the screen shot of the code from Server method. you don't require any special privileges or permissions to execute a stored procedure. if used other then Server method, a message should appear like this 'Request for the permission of type 'SqlStatementExecutePermission' failed.'

create a job and write the above code as shown in the image.

When creating a table from AOT in Dynamics AX, developers often forget to right click and save the table, specially the developers habituated to SQL Server. There could be column names conflict if not saved in AOT and eventually the changes might be lost and cause other problems.

Note : If creating a table from AOT in Dynamics AX then there will be couple of default columns added to your table like RECID, RECVERSION, DATAAREAID and etc. Make sure to include and insert values into those fields.

Execute external database Stored Procedure from X++ code using ODBC connectivity

This is to show a method to execute external database stored procedures from X++ code. Create a job in AOT with following code. Follow the steps as explained here in the code like replace Server Name, Database name , Stored Procedure name. The below code is executed through the ODBC Connection.

 Collapse | Copy Code

static void execExternalDatabase(Args _args)
{
    LoginProperty loginProperty;
    ODBCConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    ResultSetMetaData resultSetMetaData;
    Counter counter;
    str sql;
    SqlStatementExecutePermission perm;
    ;

    loginProperty = new LoginProperty();
    loginProperty.setServer("SERVERNAME Here"); // Replace your Database Server Name here

    loginProperty.setDatabase("DemoDB"); //Replace your Database name here
     
    odbcConnection = new ODBCConnection(loginProperty); // setting odbc connection here.

     // ODBC Connection to create Statement
     statement = odbcConnection.createStatement();

    // Replace the StoredProcedure you want to execute.
    sql = strfmt('EXEC [myStoredProcedureName]');

    // Set code access permission to Execute
    perm = new SqlStatementExecutePermission(sql);
    perm.assert();

    try
    {
    // if Stored Procedure has Select query use executeQuery method.
       resultSet = statement.executeQuery(sql);
       resultSet.next();
       resultSetMetaData = resultSet.getMetaData();
        for (counter=1; counter <= resultSetMetaData.getColumnCount(); counter++)
        {
            switch (resultSetMetaData.getColumnType(counter))
            {
            case 0,1 :
            info(resultSet.getString(counter));
            break;
            case 3 :
            info(date2StrUsr(resultSet.getdate(counter)));
            break;
            }
        }
    }
    catch (exception::Error)
    {
        print "An error occured in the query.";
        pause;
    }

    // Code access permission scope ends here.
    CodeAccessPermission::revertAssert();
   }  

Used a simple select query in the stored procedure and the result will be displayed on the infolog.