I have a requirement that i want to connect to a database on SQLEXPRESS and get the data from there.
How can i connect to the sqlexpress database and bring the data here on AX form to display.
Thanks.
Please go through the below link having piece of code for connecting database by odbc
The problem is that i want to connect to SQLEXPRESS.
I have a code that is working fine when i am using the SQL Standard Edition but the same code is giving error.
This is the job which is working fine but when i change the following following value
loginProp.setServer(“MY-PC1\SQLEXPRESS”);
This change of code is making the problem.
static void ReadODBC(Args _args)
{
OdbcConnection connection;
LoginProperty loginProp;
Statement statement;
ResultSet result;
str sqlStmt;
SqlStatementExecutePermission permission;
;
// Create an object of the LoginProperty class
loginProp = new LoginProperty();
// Set the servername and database to the
// LoginProperty object
loginProp.setServer(“MY-PC1”);
loginProp.setDatabase(“DynamicsAx1”);
// Check to see if the executing user has access
try
{
// Create a new connection based on the information
// set in the LoginProperty object
connection = new OdbcConnection(loginProp);
}
catch
{
error (“You do not have access to the database specified”);
return;
}
// Set the select statement to the string variable
sqlStmt = ‘SELECT dataareaid, accountnum, name from dbo.custtable’;
// Check if it’s ok to use sql statement
permission = new SqlStatementExecutePermission(sqlStmt);
permission.assert();
// Create and prepare a new Statement object
// from the connection
statement = connection.createStatement();
// Execute the query and retrieve the resultset
result = statement.executeQuery(sqlStmt);
// Loop through the records in the resultset and print
// the information to the infolog
while (result.next())
{
info (strfmt("(%1) %2: %3", result.getString(1),
result.getString(2), result.getString(3)));
}
// Revert the permission assert
CodeAccessPermission::revertAssert();
}
Instead of setting all credentials like server, database into the loginproperty, try adding a sql server data source in odbc settings of control panel and directly use setDSN method of LoginProperty.
Setting of DSN in ODBC.
Go to Control Panel → Administrative Tools → Data Sources (ODBC) → System DSN → Add : a) Select SQL Server driver; finish b) Type in a name for your datasource c) Select an SQL Server ie the name of your server instance; next d) Select your authentication set-up; next e) Check the ‘change the default database to’ whatever database you are trying to connect to; next f) No need to change anything in the next panel g) Finish