You’re offline. This is a read only version of the page.
Announcements
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
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.
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 :
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:
Share
Report
All responses (
Answers (
try
{
... run your logic ...
}
catch (Exception::CLRError)
{
throw error(AifUtil::getClrErrorMessage());
}
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
[/quotehello 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.");
}
}
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.
thanks nikolaos for reply, ill try it and give you inform if it work
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:
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
#1
André Arnaud de Cal...
294,165
Super User 2025 Season 1
#2
Martin Dráb
232,968
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
