Run SQL command in ax 2012 with specific login

(1) Share

Report

Posted on by Ardhi A Rahman

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

I have the same question (0)

All responses (8)

Answers (4)

Verified answer

nmaenpaa 101,160 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
Verified answer

WillWU 22,361 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Ardhi A Rahman 50 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Ardhi A Rahman 50 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Ardhi A Rahman 50 on at

Like (0)

Report
[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

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 ?

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (1)

Report
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()); }

Was this reply helpful? Yes No
Ardhi A Rahman 50 on at

Like (0)

Report

[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 :

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,795 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No