MS SQL – Mohsin Khalid

Once your db restore is done. Before starting services make sure you do the following:

Stop any batch jobs to avoid posting or pushing data to systems configured in production. This can be done by below SQL

UPDATE [DynamicsAXDev].[dbo].[BATCHJOB] SET [STATUS] = 0

WHERE [STATUS] IN (1,2,5)

An Admin User would already exist, Use below query to set your user as Admin:

UPDATE USERINFO
SET Name = ‘Mohsin’,
SID = ‘S-1-5-21-542508066-1570976776-XXXXXXX-54688’,
NetworkDomain = ‘XXXX.XX’,
NetworkAlias = ‘XXXX’
WHERE ID = ‘Admin’

Start services and replace the settings described in the video below to set your batch servers.

Here is how you can connect to an external database using X++ SqlClient class. This is among the few others approaches you can connect to an external database.

public System.Data.SqlClient.SqlConnection getConnection()
{
str connectionString;

System.Exception exception;
System.Data.SqlClient.SqlConnection connection;

connectionString = “Persist Security Info=False;User ID=UserID;Pwd=Password;Initial Catalog=DatabaseName;Data Source=localhost;”;

try
{
connection = new System.Data.SqlClient.SqlConnection(connectionString);
connection.Open();

return connection;
}
catch (Exception::Error)
{
error(“An exception has occurred.”);
}
catch (Exception::CLRError)
{
error(“A CLR exception has occurred.”);

exception = CLRInterop::getLastException();

if (exception != null)
{
info(exception.ToString());
}
}

return null;
}

To run a query after a successful connection, use this code:

System.Data.SqlClient.SqlConnection conn;
System.Data.SqlClient.SqlCommand cmd;
System.Data.SqlClient.SqlDataAdapter da;
System.Data.SqlClient.SqlDataReader dr;
System.Exception netExcepn;

str sql, attributeName, attributeValue;
str 30 materialCode;

mzkConnectToDB = new MzkConnectToDB();
conn = mzkConnectToMSDgen.getConnection();

sql = strFmt(“select * from tablename;”);
cmd = new System.Data.SqlClient.SqlCommand(sql,conn);

dr = cmd.ExecuteReader();

while(dr.Read())
{
try
{
materialCode = dr.get_Item(‘material_id’);

}
}
catch(Exception::Error)
{
error(“@SYS85688”);
}
}

//Close the connection.
dr.Close();
conn.Close();
}