Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX forum
Answered

Run SQL command in ax 2012 with specific login

Posted on by 50

hello, i want to ask how to run SQL command directly on ax use this method.

it runs and get force close with no error

private void updateFromLargo(/*EU_TBL_PembagianLargoAXTmp _updateSTG*/)
{
str queryUpd;

EU_CLS_PembagianlargoAXconnect updateLargo = EU_CLS_PembagianlargoAXconnect::construct();

System.Exception exception;
System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlDataReader dataReader;

connection = updateLargo.getConnectionLargo();

queryUpd = strFmt( "UPDATE A " +
"SET [QTYSET] = B.[QTYSET], " +
"[QTYPCS] = B.[QTYSET] * B.[CYLFN2] " +
"FROM [AXDEV].[MicrosoftDynamicsAX].[dbo].[EU_TBL_PembagianLargoAXTmp] " +
"A INNER JOIN " +
"[BarcodeStaging].[dbo].[AXLABELFN2RESULT] B " +
"ON A.[PRODIDFN2] = B.[PRODID]" );  >> i only want to run this command on sql server using class ax 2012

command = new System.Data.SqlClient.SqlCommand(queryUpd, connection);
dataReader = command.ExecuteReader(); >> already use debugger and after this it force close

dataReader.Close();
connection.Close();
}

i know if datareader is empty on > private void updateFromLargo(/*EU_TBL_PembagianLargoAXTmp _updateSTG*/) it will become error.


but can i have another solution using this method to run only SQL command ?

THANKS IN ADVANCE

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    RE: Run SQL command in ax 2012 with specific login

    What exactly do you mean by "a server"? It's a very generic term. Do you mean another AX environment (as I suggested in my previous reply, but you didn't confirm it)?

    What's the business requirement?

    If you're talking about two AX environments, there is a business reason for having data spread in two environments and you don't want to use synchronization batches, then server 2 should expose a custom service returning the quantity which server 1 will call.

  • Ardhi A Rahman Profile Picture
    Ardhi A Rahman 50 on at
    RE: Run SQL command in ax 2012 with specific login
    [quote]Could you elaborate your requirement, please? Does "i have to updating qty from another server" means that you want to update values of another environment? If so, you're wrong in thinking that you have to write directly to database. In fact, that's exactly what you shouldn't do. I suggest you use a custom service for that.[/quote]

    Thanks for reply Martin,

    let me explain you a bit..

    so i have 2 server

    1 and 2

    when i get my SSRS Report done to load in server 1

    its filled with data of Server 1.

    on some field i need that filled with QTY B from Server 2.
    using UPDATE method run on SQL Query on server 2 to send to Server 1 so the QTY B updated in Server 1 and ready to sum..


    Sample :

    pastedimage1590046493072v2.png

  • Verified answer
    Martin Dráb Profile Picture
    Martin Dráb 225,610 Super User on at
    RE: Run SQL command in ax 2012 with specific login

    Could you elaborate your requirement, please? Does "i have to updating qty from another server" means that you want to update values of another environment? If so, you're wrong in thinking that you have to write directly to database. In fact, that's exactly what you shouldn't do. I suggest you use a custom service for that.

    Also, what do you mean by saying that you must update database to do a comparison?

    Regarding your original problem, what you call "it force close" means that there is an unhandled exception. You should catch the CLR exception to see what's going on. Like this:

    try
    {
        ... run your logic ...
    }
    catch (Exception::CLRError)
    {
        throw error(AifUtil::getClrErrorMessage());
    } 

  • Ardhi A Rahman Profile Picture
    Ardhi A Rahman 50 on at
    RE: Run SQL command in ax 2012 with specific login
    [quote

    Why do you want to run it as direct SQL query, bypassing x , security, AOS caches (possibly leading to outdated data on other AOSes)?

    Anyway, if you run direct SQL queries, you need to assert a SqlStatementExecutePermission: community.dynamics.com/.../executing-direct-sql-statements

    [/quote

    hello nikolaos im back.

    already tried via ODBC and already add user and pass with right logon. but the error is like this

    pastedimage1590030084923v1.png

    and here's the code:

    private void SingleConnect()
    {
        LoginProperty loginProperty;
        OdbcConnection odbcConnection;
        Statement statement;
        ResultSet resultSet;
        LoginProperty myLoginProperty;
        str sql, criteria;
        int output;
        SqlStatementExecutePermission perm;
        str myUserName="ardhi";
        str myPassword="Test12345";
        str myConnectionString;
        myConnectionString=strfmt("UID=%1;PWD=%2",myUserName,myPassword);
        myLoginProperty = new LoginProperty();
        myLoginProperty.setServer("STAGING-KITE");
        myLoginProperty.setDatabase("BarcodeStaging");
        myLoginProperty.setOther(myConnectionString);
    
    
        //Create a connection to external database.
        odbcConnection = new OdbcConnection(myLoginProperty);
    
        if (odbcConnection)
        {
            sql = 'UPDATE A SET [QTYSET] = B.[QTYSET], [QTYPCS] = B.[QTYSET] * B.[CYLFN2] FROM [AXDEV].[MicrosoftDynamicsAX].[dbo].[EU_TBL_PembagianLargoAXTmp] A INNER JOIN [BarcodeStaging].[dbo].[AXLABELFN2RESULT] B ON A.[PRODIDFN2] = B.[PRODID]';
            info(sql);
            
            //Assert permission for executing the sql string.
            perm = new SqlStatementExecutePermission(sql);
            perm.assert();
            
            //Prepare the sql statement.
            statement = odbcConnection.createStatement();
            output = statement.executeUpdate(sql);
            
            statement.close();
        
        }
        else
        {
            error("Failed to log on to the database through ODBC.");
        }
    
    }


    am i miss something ?

  • Ardhi A Rahman Profile Picture
    Ardhi A Rahman 50 on at
    RE: Run SQL command in ax 2012 with specific login

    thanks for reply Will, but indeed if i cant touch directly. but i had to, because i have to updating qty from another server(but not to touch and open the SQL Management again), so that can be calculated between my server and another server. comparing the result of the qty.

  • Ardhi A Rahman Profile Picture
    Ardhi A Rahman 50 on at
    RE: Run SQL command in ax 2012 with specific login

    thanks nikolaos for reply, ill try it and give you inform if it work

  • Verified answer
    WillWU Profile Picture
    WillWU 22,344 on at
    RE: Run SQL command in ax 2012 with specific login

    Hi Ardhi,

    Another point, you have to execute your code on server tier, please see the DOC:

    docs.microsoft.com/.../code-access-security

    Also, as Nikolaos said, please don't use direct SQL unless absolutely necessary.Please have a look at Martin's Blog:

    dev.goshoom.net/.../

  • Verified answer
    nmaenpaa Profile Picture
    nmaenpaa 101,148 on at
    RE: Run SQL command in ax 2012 with specific login

    Why do you want to run it as direct SQL query, bypassing x++, security, AOS caches (possibly leading to outdated data on other AOSes)?

    Anyway, if you run direct SQL queries, you need to assert a SqlStatementExecutePermission: community.dynamics.com/.../executing-direct-sql-statements

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans