Ilegal Reference error while creating sales order lines / Purchase order lines / Transfer order lines

Ilegal Reference error while creating sales order lines / Purchase order lines / Transfer order lines  is due to Number Sequences of InventTrans Table –> InventTransId field.

 

Go to Number seq – select the InventTransId sequence number , clear all numbers that are on hold. “use manual clean up button”

 

Connecting External Database from X++

Before starting few configuration need to set :- 1. Create DSN : To create a Data Source Name (DSN) go to Administrative Tools > Data Sources (ODBC). click add select Sql server native client – provide server name -Next- click on  change default Database and select the database “MyExternalDB”.   2. CODE

// X++, Main method in a class.
static public void Main(Args _args)
{
    LoginProperty loginProperty;
    OdbcConnection odbcConnection;
    Statement statement;
    ResultSet resultSet;
    str sql, criteria;
    SqlStatementExecutePermission perm;
    ;

    // Set the information on the ODBC.
    loginProperty = new LoginProperty();
    loginProperty.setDSN("dsnName");
    loginProperty.setDatabase("MyExternalDB");

    //Create a connection to external database.
    odbcConnection = new OdbcConnection(loginProperty);

    if (odbcConnection)
    {
        sql = "SELECT * FROM MYTABLE WHERE FIELD = "
            + criteria
            + " ORDER BY FIELD1, FIELD2 ASC ;";

        //Assert permission for executing the sql string.
        perm = new SqlStatementExecutePermission(sql);
        perm.assert();

        //Prepare the sql statement.
        statement = odbcConnection.createStatement();
        resultSet = statement.executeQuery(sql);

        //Cause the sql statement to run,
        //then loop through each row in the result.
        while (resultSet.next())
        {
            //It is not possible to get field 3 and then 1.
            //Always get fields in numerical order, such as 1 then 2 the 3 etc.
            print resultSet.getString(1);
            print resultSet.getString(3);
        }

        //Close the connection.
        resultSet.close();
        statement.close();
    }
    else
    {
        error("Failed to log on to the database through ODBC.");
    }
}

Shrinking the Database Log File , Backup LDF & MDF Files

Shrinking the Database Log File:

1.Take complete database backup before performing any operation

First you need to take the Backup of .mdf and .ldf files, follow the below steps to complete process

2. Stop the Dynamics Ax Services:-

Start -> Run-> Type Services.msc -> Locate the Dynamics Ax Object Server -> Stop

3. Take the Database Offline Mode :

Open the SQL Server Management Studio and connect to appropriate Database Server

Right click on the database -> Tasks -> Take Offline

 

It will release all the resources gracefully.

4. Copy the .mdf and .ldf  files :

Generally these file exist on the path C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

If you are not sure about the database location Right Click on the Database -> Properties ->Select File (Located on Left Side Corner)

Than you can find the path for both .mdf and .ldf files

 5. Bring the database online Mode:

After taking the backup, we need to make the database online mode

Right Click on the Database -> Task -> Bring Online

6. Query to Shrink log database file :

Run the following Query to shrink the log database file

Note:  Do not run this query until you have proper backup

USE DynamicsAXLive (.mdf file name)
GO
ALTER DATABASE DynamicsAXLive (.mdf file name)
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE(DynamicsAX_log, 5000); (log database file Name & minimum size to shrink)
GO
ALTER DATABASE DynamicsAXLive (.mdf file name)
SET RECOVERY FULL;
GO

Getting SID’s in Domain and updating in the table(userinfo)

Getting SID’s in Domain:-

type in cmd

–> wmic useraccount get name,sid /format:csv > c:\output.csv

Updating the SID’s from csv file :-

UserInfo usrInfo;
string50 userId;
Dialog dialog;

DialogField dialogFileName;
SysOperationProgress simpleProgress;
Filename filename;
CommaIo csvFile;
container readCon;
Container filterCriteria;
int numLines;
int cnt;
FileIOPermission permission;
TextIO textIO;
NoYesId Data;

textBuffer tb = new textBuffer();

#File
#avifiles
;

dialog = new Dialog(‘Select CSV File’);
dialogFileName = dialog.addField(typeid(Filenameopen), “File Name”);
filterCriteria = [‘*.csv’];
filterCriteria = dialog.filenameLookupFilter(filterCriteria);
dialog.run();

if (dialog.run())
{
filename = dialogFileName.value();
if(!filename)
info(“Filename must be filled”);

permission = new fileIOpermission(filename,#io_read);
permission.assert();
textIO = new TextIO(filename,#io_read);
if (!textIO)
throw error(“Error reading file”);

tb.fromFile(filename); //File name with Path …
numLines = tb.numLines();
csvFile = new CommaIo(filename, ‘r’);
csvFile.inFieldDelimiter(” “); // Delimiter…
simpleProgress = SysOperationProgress::newGeneral(#aviUpdate, ‘Importing data’,1000);

try
{
if (csvFile) //Checking for csv file.
{
readCon = csvFile.read();//reading the file.

ttsbegin;
for(cnt=1;cnt<=numLines;cnt++)
{
readCon = str2Con(tb.nextToken(true));
userId = Conpeek(readCon,2); // username
select forupdate usrInfo where usrInfo.Id == userId;
if(usrInfo)
{
usrInfo.sid = Conpeek(readCon,3); // sid
usrInfo.networkDomain = Conpeek(readCon,1) ; // network domain
usrInfo.doUpdate();
info(strfmt(“the user updated are :%1”,usrInfo.id));
}
else
{
info(strfmt(“the users not updated are :%1”,Conpeek(readCon,2)));
}
}
ttscommit;
}
}
catch
{
throw error(“”);
}
}
}

Finding SID of all users

type in cmd

–> wmic useraccount get name,sid /format:csv > c:\output.csv

 

Find Current User who as Login in 2009

SysCompanyUserInfo   sysCompanyUserInfo;

;

select * from sysCompanyUserInfo where sysCompanyUserInfo.UserId == curuserid();

info(strfmt(“%1”, sysCompanyUserInfo.EmplId));