Read XML Using XMLReader

Reading an XML file using the XMLReader which should be faster compared to DOM classes

    XmlReader   taskDetails;
    str attributeName, attributeValue;
    int i, j;
    ;


    //using the XML Reader class
    taskDetails = XmlTextReader::newXml( tasks.ScriptText );

    while ( taskDetails.read() )
    {
        switch ( taskDetails.nodeType() )
        {
            case XMLNodeType::Element:
                while ( taskDetails.moveToNextAttribute() )
                {
                    attributeName = taskDetails.name();
                    attributeValue = taskDetails.value();
                   
                    info( strfmt("AttributeName =%1, AttributeValue = %2", taskDetails.name(), taskDetails.value() ) );
                }
                break;

            default:
                info( strfmt("Nodetype = %1, NodeName = %2, NodeValue = %3, InnerXML = %4, readAttributeValue = %5"
                            , taskDetails.nodeType()
                            , taskDetails.name()
                            , taskDetails.value()
                            , taskDetails.readInnerXml()
                            , taskDetails.readAttributeValue()
                            ) ) ;

                while ( taskDetails.moveToNextAttribute() )
                {
                    attributeName = taskDetails.name();
                    attributeValue = taskDetails.value();

                    info( strfmt("AttributeName =%1, AttributeValue = %2", taskDetails.name(), taskDetails.value() ) );
                }

                break;

        }
    }

Read XML using DOM

Please find the X++ code snippet below to read a XML stream using DOM


    XmlDocument xmlDoc;
    XmlNode xmlRoot;
    XmlNodeList xmlRecordList;
    XmlElement xmlRecord;

    XmlNamedNodeMap attributeList;
    str attributeName, attributeValue;
    int i, j;
    ;

    xmlDoc = new XmlDocument();
    xmlDoc.loadXml( tasks.ScriptText );
    xmlRoot = xmlDoc.root();
    xmlRecordList = xmlRoot.childNodes();


    for (i=0; i < xmlRecordList.length(); i++)
    {
        xmlRecord = xmlRecordList.item( i );

        attributeList = xmlRecord.attributes();

        for (j=0; j < attributeList.length(); j++)
        {
            attributeName = attributeList.item(j).name();
            attributeValue = xmlRecord.getAttribute( attributeName ) ;
        }
    }

XML Data Model

To parse a XML string it is very important to correctly understand the data model of XML here is my understanding of the same

XMLNode is a basic object in a DOM tree
XMLDocument class extends the node class and support methods for performing operations on the document as a whole

A node can have multiple childs nodes below it however each node would only have one parent. Each node can have multiple name-value pairs which are known as Attributes. If an application does not require the structure and editing capabilities provided by DOM then XMLReader and XMLWrite classes can be used as they are faster and are meant to provide a non-cached, forward only access to an XML stream.

Further to this a node could be of different types. Identifying the node type helps to determine what actions can be performed and what properties can be set or retrieved. To understand the different types of nodes lets use the example below

There is another common question that i have encountered as to what is the difference between a node and an element as we will see in the example below that an element is infact a node type

Example
<?xml version="1.0"?>
<!-- This is a sample XML document -->
<!DOCTYPE Items [<!ENTITY number "123">]>
<Items>
  <Item>Test with an entity: &number;</Item>
  <Item>test with a child element <more/> stuff</Item>
  <Item>test with a CDATA section <![CDATA[<456>]]> def</Item>
  <Item>Test with a char entity: &#65;</Item>
  <!-- Fourteen chars in this element.-->
  <Item>1234567890ABCD</Item>
</Items>

Input	Output	Node Type
<?xml version="1.0"?>	<?xml version='1.0'?>	XmlNodeType.XmlDeclaration
<!-- This is a sample XML document -->	<!--This is a sample XML document -->	XmlNodeType.Comment
<!DOCTYPE Items [<!ENTITY number "123">]>	<!DOCTYPE Items [<!ENTITY number "123">]	XmlNodeType.DocumentType
<Items>	<Items>	XmlNodeType.Element
<Item>	<Item>	XmlNodeType.Element
Test with an entity: &number;</Item>	Test with an entity: 123	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<Item>	<Item>	XmNodeType.Element
test with a child element	test with a child element	XmlNodeType.Text
<more>	<more>	XmlNodeType.Element
stuff	stuff	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<Item>	<Item>	XmlNodeType.Element
test with a CDATA section	test with a CDATA section	XmlTest.Text
<![CDATA[<456>]]>	<![CDATA[<456>]]>	XmlTest.CDATA
def	def	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<Item>	<Item>	XmlNodeType.Element
Test with a char entity: &#65;	Test with a char entity: A	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
<!-- Fourteen chars in this element.-->	<--Fourteen chars in this element.-->	XmlNodeType.Comment
<Item>	<Item>	XmlNodeType.Element
1234567890ABCD	1234567890ABCD	XmlNodeType.Text
</Item>	</Item>	XmlNodeType.EndElement
</Items>	</Items>	XmlNodeType.EndElement

Using gmail as a SMTP Server

Firstly you will not be able to do a direct telnet as gmail using secure connections so there is a free utility that you can use it is called OpenSSL and works pretty much the same as telnet however you will have to enter your username and password in 64 bit MIME (Multipurpose Internet Mail Extensions) based content transfer encoding with base64

Dont worry if didnt quite understand the last bit :-) this encoding is just a standard used to encode non-text 8 bit and binary data. Sometimes used for text data that frequently uses non-US-ASCII characters. There is an internet site which can encode your text data into MIME base64 using this free online utility http://base64-encoder-online.waraxe.us/

1. To begin with download the OpenSSL for windows from http://code.google.com/p/openssl-for-windows/downloads/list
2. Encode your gmail username and password using the encoding utility
3. Go to the DOC command prompt and start the OpenSSL utililty
openssl s_client -ssl3 -connect smtp.gmail.com:587 -starttls smtp -crlf
Loading 'screen' into random state - done
CONNECTED(0000015C)
depth=1 /C=US/O=Google Inc/CN=Google Internet Authority
verify error:num=20:unable to get local issuer certificate
verify return:0
---
Certificate chain
0 s:/C=US/ST=California/L=Mountain View/O=Google Inc/CN=smtp.gmail.com
i:/C=US/O=Google Inc/CN=Google Internet Authority
1 s:/C=US/O=Google Inc/CN=Google Internet Authority
i:/C=US/O=Equifax/OU=Equifax Secure Certificate Authority
---
Server certificate
-----BEGIN CERTIFICATE-----
MIIDWzCCAsSgAwIBAgIKaM9uMQADAAAirTANBgkqhkiG9w0BAQUFADBGMQswCQYD
VQQGEwJVUzETMBEGA1UEChMKR29vZ2xlIEluYzEiMCAGA1UEAxMZR29vZ2xlIElu
dGVybmV0IEF1dGhvcml0eTAeFw0xMTAyMTYwNDM4MDlaFw0xMjAyMTYwNDQ4MDla
MGgxCzAJBgNVBAYTAlVTMRMwEQYDVQQIEwpDYWxpZm9ybmlhMRYwFAYDVQQHEw1N
b3VudGFpbiBWaWV3MRMwEQYDVQQKEwpHb29nbGUgSW5jMRcwFQYDVQQDEw5zbXRw
LmdtYWlsLmNvbTCBnzANBgkqhkiG9w0BAQEFAAOBjQAwgYkCgYEAzv9SacnXKcAx
+0B4yVH2qdpVUXnCk0Nvkn/y2Y0nkzFaWsWMAkOLTMtsa5yP69pnsYb9tUrs4KiL
DkRnf7RdSVlmpIGAknMAPDY/rja23ltutuN7Q4EjadsY8Q+NX5uWjlPsA5BE8GuI
9JobEl4iGFUAfF+2iOGlwFeFbpCaDJECAwEAAaOCASwwggEoMB0GA1UdDgQWBBQK
stTMUTHcQEpEpgaXuVVXJeIHxjAfBgNVHSMEGDAWgBS/wDDr9UMRPme6npH7/Gra
42sSJDBbBgNVHR8EVDBSMFCgTqBMhkpodHRwOi8vd3d3LmdzdGF0aWMuY29tL0dv
b2dsZUludGVybmV0QXV0aG9yaXR5L0dvb2dsZUludGVybmV0QXV0aG9yaXR5LmNy
bDBmBggrBgEFBQcBAQRaMFgwVgYIKwYBBQUHMAKGSmh0dHA6Ly93d3cuZ3N0YXRp
Yy5jb20vR29vZ2xlSW50ZXJuZXRBdXRob3JpdHkvR29vZ2xlSW50ZXJuZXRBdXRo
b3JpdHkuY3J0MCEGCSsGAQQBgjcUAgQUHhIAVwBlAGIAUwBlAHIAdgBlAHIwDQYJ
KoZIhvcNAQEFBQADgYEAxxXNJTE3LS1vmaqNZcFbNeUQtF/9DHpTfGGTtQCAjeMR
uhwSpAmc3/TxeERkT8cBckQxZWlMn2sHa418+DNv0/0QB4SZs0Fus4mXq/Erz91Y
Ouo+mV5BJSkDXH/qbG6wiBdEIypseBEbG+XJMxTSaYVgUjY313rBbAvQ0Uf7ZGQ=
-----END CERTIFICATE-----
subject=/C=US/ST=California/L=Mountain View/O=Google Inc/CN=smtp.gmail.com
issuer=/C=US/O=Google Inc/CN=Google Internet Authority
---
No client certificate CA names sent
---
SSL handshake has read 1932 bytes and written 337 bytes
---
New, TLSv1/SSLv3, Cipher is RC4-SHA
Server public key is 1024 bit
Compression: NONE
Expansion: NONE
SSL-Session:
Protocol : SSLv3
Cipher : RC4-SHA
Session-ID: F08336D4E6FBF5787A197A46D52333B1B2F19B1DA908C213184DD4797B92E60A

Session-ID-ctx:
Master-Key: 18C7490AA3E755D0BCE0EFB80CF99FD0D245A0C6FE58F4D1AD0176E0D826667B
CB8B5ECCE9AA6C67FE680139FF3C771D
Key-Arg : None
Start Time: 1324960518
Timeout : 7200 (sec)
Verify return code: 20 (unable to get local issuer certificate)
---
250 ENHANCEDSTATUSCODES
AUTH LOGIN
334 VXNlcm5hbWU6
c2hhaUBzaGFpLWFydml4ZS5jb20=
334 UGFzc3dvcmQ6
bjw1JWptPm5+OWtjVjdpRi9IIl5HcEhnaHMwN
235 2.7.0 Accepted
mail from:<snk@ae.columbusit.com>
250 2.1.0 OK b20sm85444224ibj.7
rcpt to: <snk@ae.columbusit.com>
250 2.1.5 OK b20sm85444224ibj.7
data
354 Go ahead b20sm85444224ibj.7
subject:Hello
Hello World from gmail SMTP
.
250 2.0.0 OK 1324960636 b20sm85444224ibj.7
quit
221 2.0.0 closing connection b20sm85444224ibj.7
read:errno=0

Find Missing Indexes

Once again the dynamic management views to rescue. Found this from brent ozar's blog havent really used it as i was working on production environment when i came across this but it would be worth a try to carefully deploy these one by one and check for the usage.


SELECT sys.objects.name
, (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) AS Impact
, 'CREATE NONCLUSTERED INDEX ix_IndexName ON ' + sys.objects.name COLLATE DATABASE_DEFAULT + ' ( ' + IsNull(mid.equality_columns, '') +
CASE WHEN mid.inequality_columns IS NULL THEN
''
ELSE
CASE WHEN mid.equality_columns IS NULL THEN
''
ELSE
','
END + mid.inequality_columns
END + ' ) '
+
CASE WHEN mid.included_columns IS NULL THEN
''
ELSE
'INCLUDE (' + mid.included_columns + ')'
END + ';' AS CreateIndexStatement
, mid.equality_columns
, mid.inequality_columns
, mid.included_columns
FROM sys.dm_db_missing_index_group_stats AS migs
INNER JOIN sys.dm_db_missing_index_groups AS mig
ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details AS mid
ON mig.index_handle = mid.index_handle
AND mid.database_id = DB_ID()
INNER JOIN sys.objects WITH (nolock)
ON mid.OBJECT_ID = sys.objects.OBJECT_ID
WHERE
(
migs.group_handle IN (
SELECT TOP (500) group_handle
FROM sys.dm_db_missing_index_group_stats WITH (nolock)
ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC
)
)
AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable' )=1
ORDER BY 2 DESC , 3 DESC


click here to view brents video

Identifying indexes which can be deleted

Dynamic management views contain a lot of important information which can be used to manage the data more efficiently. The first instinct when asked to performance tune a database is to add new indexes and improve the not so well performing queries. However a more logical view is to beign with removing the unused indexes first before adding any new ones as adding an index also has an overhead.

When working on indentifying the unsed indexes the following query which is basec on the dynamic management view sys.dm_db_index_usage_stats can be used :

SELECT o.name
, indexname=i.name
, i.index_id
, reads = user_seeks + user_scans + user_lookups
, writes = user_updates
, rows = (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id)
, CASE WHEN s.user_updates < 1 THEN
100
ELSE
1.00 * (s.user_seeks + s.user_scans + s.user_lookups) / s.user_updates
END AS reads_per_write
FROM sys.dm_db_index_usage_stats s
INNER JOIN sys.indexes i
ON i.index_id = s.index_id AND s.object_id = i.object_id
INNER JOIN sys.objects o
on s.object_id = o.object_id
INNER JOIN sys.schemas c
on o.schema_id = c.schema_id
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND s.database_id = DB_ID()
AND i.type_desc = 'nonclustered'
AND i.is_primary_key = 0
AND i.is_unique_constraint = 0
AND (SELECT SUM(p.rows) FROM sys.partitions p WHERE p.index_id = s.index_id AND s.object_id = p.object_id) > 10000
ORDER BY reads


The column reads_per_write is used to identify the indexes which are the least used. The indexes which are most often written to and the least read from are the ones which should be gotten rid of. The most useful index is the one which is the most used.

Navision Application Roles

Navision secuirty works on application roles which are created in the SQL Server. For each user that is created in Navision an application role is created and the effective permissions for the user are applied to the appplication role by the navision client.

During a performance tuning excercise I modified some of the SIFT tables to increase the performance. The problem set in when due to these changes the navision security got disturbed and the users were automatically revoked permissions on the SIFT tables. I was required to constantly make changes to the SIFT tables and it was painful to synchronize all the user again and again so i figured out that we could grant the access permissions to these tables for all the application roles which would solve the problem and not require the login synchronization each time.

To get a list of all the application roles i used the following query :
select name 'rolename', uid 'roleid', isapprole from sysusers where isapprole = 1

The grant query was also easy to generate as shown below
select 'grant select, insert, update on [TableName] to ' + name
from sysusers where isapprole = 1

Transaction Log Shipment

Firstly step by step process to setup TLS
http://omaralzabir.com/how_to_setup_sql_server_2005_transaction_log_ship_on_large_database_that_really_works/
I also realized that the restore opertation is faster when done using the norecovery option (recovery option rollsback uncommitted transactions and returns the database to a consistent state hence takes additional time) so the trick to save time when applying multiple transactions logs is to restore each log with norecovery and after the last log is recovered put the database to the standby mode using a command as follows

RESTORE DATABASE mydatabase WITH STANDBY = 'C:\mydatabase_undo.dat'

Object IDs

There are times when we get into situtations with ID conflicts in these cases rather than changing the IDs directly in the SQL Dictionary we could use the helper methods in the ReleaseUpdateDB class:

changeFieldByAOTName
changeFieldByName
changeFieldId
changeNameByFieldId
changeTableByAOTName
changeTableByName
changeTableId


We get into ID conflicts when the layers are used as deployment vehicles. Lets take up a a few examples

Case 1

1. Error Message

TableName: EmplTable
Illegal data conversion from original field EMPLTABLE.CITACTUALTITLE to EMPLTABLE.SendMail: Unable to convert data types to anything but character field type (0 to 4). Synchronize database Cannot execute the required database operation. The SQL database has issued an error.

when the error message for table synchronize is displayed it has two parts from orginal details and to details. The from part displays the details existing in the SQLDictionary whereas the "to" part displays the current details as existing in the AOT.

Thus from the above message we can infer that currently there is a field named SendMail in the EmplTable in AOT and the same field id exists with a name CITActualTitle in the database and maybe the data types are also not compatible. So if we change the existing defination in the SQL Dictionary and move the field to a different id so that it does not clash with SendMail field we should be able to resolve the issue.

2. Care a job to renumber the field
ReleaseUpdateDB::changeFieldId(103, 30001, 30002, "EmplTable", "CITACTUALTITLE");


Case 2

1. Error Message
TableName:PYLEmplAccrualCarryFwd
Illegal data conversion from original field CITEMPLMEDICALCUREDATA.RESIDENCENO to PYLEMPLACCRUALCARRYFWD.accrualStartDate: Unable to convert data types to anything but character field type (0 to 3).
Illegal data conversion from original field CITEMPLMEDICALCUREDATA.CURETYPE to PYLEMPLACCRUALCARRYFWD.AccrualEndDate: Unable to convert data types to anything but character field type (0 to 3).
Illegal data conversion from original field CITEMPLMEDICALCUREDATA.ANNUALCURE to PYLEMPLACCRUALCARRYFWD.CarryFwdAccrualEndDate: Unable to convert data types to anything but character field type (0 to 3).
Illegal data conversion from original field CITEMPLMEDICALCUREDATA.TELNO to PYLEMPLACCRUALCARRYFWD.PostingDate: Unable to convert data types to anything but character field type (0 to 3).
Illegal data conversion from original field CITEMPLMEDICALCUREDATA.ADDRESS to PYLEMPLACCRUALCARRYFWD.ActualCalcDate: Unable to convert data types to anything but character field type (0 to 3).
Illegal data conversion from original field CITEMPLMEDICALCUREDATA.EMPLNAME to PYLEMPLACCRUALCARRYFWD.Completed: Unable to convert data types to anything but character field type (0 to 4).
Synchronize database Cannot execute the required database operation. The SQL database has issued an error.

This seems like a bigger issue however thatz not the case the hint in in the fist message if we notice we can see that in this message between the original and new part the table name is also changed. What this means is that the with the id of the current object PYLEMPLACCRUALCARRYFWD there is already a table existing in the database CITEMPLMEDICALCUREDATA now from this point onwards all the field ids in the table would also differ hence all the balance messages.

If we change the table id in the SQLDictionary for CITEMPLMEDICALCUREDATA then the new table PYLEMPLACCRUALCARRYFWD would be created fresh without any conflict with the fields

2. Renumber the table id
ReleaseUpdateDB::changeTableId( 30001, 30016, "CITEMPLMEDICALCUREDATA");

Dynamics AX Command Line Parameters

Axapta Command Line


Following are Axapta Command line parameters and its description

-allowunauth
When using Windows Authentication, this option enables users that do not pass the authentication process to be allowed logging in using user name and password (traditional Axapta logon sequence). If not enabled, users will be rejected if not authenticated.

-aol="s"
aol is an acronym for Application Object Layer. Valid layers are:
sys, syp, gls, glp, dis, dip, los, lop, bus, bup, var, vap, cus, cup, usr and usp.

-aolcode="s" Access code for aol.

-aos=host:port
Connect to the AOS running at given port number on specified host. Host is either a DNS hostname (for example server1.damgaard.com) or an IP address (192.88.253.41). The port number is the number specified for the AOS instance that should be connected to. No instance name needs to be specified because only one instance can be running at a given port on a given machine. Using this option will connect the client directly to the AOS using TCP traffic only and bypass the initial search for the AOS and thereby eliminates the need for networking that supports UDP traffic. This eases firewall configuration and NAT appliances

-aos=instance@host
Connect to the specified AOS instance running at the specified host machine. Instance is the name (for example 'Axapta'), and host is DNS name or IP address of the machine running the AOS. Specifying -aos=MyAOS@MyHost equals setting -servermask=MyAOS and -internet=MyHost (or specifying these in corresponding fields on the server tab in the Configuration Utility).

-aos=ad("adsn")
Use Active Directory integration. "adsn" is the "Active Directory Server Name to search the Active Directory for. Same as "By name - find a specific AOS" in the Axapta Configuration Utility

-aos=adbrowse
Use Active Directory integration. Search the Active Directory for "Active Directory Server Names" in User objects and Organizations Units. Same as "By organization - browse for per-user or per-organizational specific AOS" in the Axapta Configuration Utility.

-aos=ad
Use Active Directory integration - search the Active Directory for any AOS. Same as "Simple - find any AOS" in the Axapta Configuration Utility

-applexclusive
The application files are opened in exclusive mode. Note applexclusive" and applshare cannot both be given. If you do give both, the one given last on the command line will take effect. You will not get any error messages.

-application=s
Specify the name of the Axapta application. Default: Standard

-applshare
The application files are opened in shared mode. This is default. Note applexclusive" and applshare cannot both be given. If you do give both, the one given last on the command line will take effect. You will not get any error messages.

-broadcast= xx.xx.xx.xx
Specify a broadcast address to be used in CLIENT mode. A request is sent to all the broadcast addresses to obtain identification of the available application servers. The address consists of four decimal values between zero and 255 separated by dots.

-bwsim=speed:latency
where speed states the bandwidth simulated (in bytes per second). The latency is specified as a number designating number of ms spent for communication round trip (the fixed overhead (time used) the network applies to sending a package to the server and receive one back). To verify....

-client
Connect to an AOS and run as a three-tier thin client. Use -aos= to specify which AOS.
client = thin : Connect to an AOS and run as a three-tier thin client.
Use -aos= to specify which AOS.
client = fat : Connect to an AOS and run as a three-tier fat client.
Use aos= to specify which AOS.

-company=s Select initial company s. Default: dat

-connectionidletimeout=seconds
Set the time in seconds to leave an idle database connection open before closing it. Shorter idle time will decrease database server and Axapta memory usage, but will potentially cause time-consuming re-logins on the fly. Default: 60 seconds is the default for Microsoft SQL Server, 30 minutes the default for Oracle.

-createdsn=microsoftsqlserver or-createdsn=oracle
Have the data source created automatically in the ODBC manager.

-createdsn_tcpipport=integer
TCP/IP port number required for Oracle. This parameter is relevant only when
-createdsn=oracle is given. The parameter is ignored if given with
-createdsn=microsoftsqlserver.

-database=s
Use database s when connecting to the database server. Default: The default option is to use the database set in the ODBC driver.

-dbcli= [ODBC][OCI]
Runs Axapta in either ODBC or OCI mode. -DBCLI=ODBC is the default.

-dbserver=s
Use server s during login. Default: The default option is to use the server set in the ODBC driver.

-dbunicodeenabled=0 1 Initialize database for Unicode

-directory=s Specify the Axapta root-directory.

-doclanguage=s
Use this option if you would like to have the documentation in a different language than the one used in menus and dialogs.
Example:
-doclanguage=da : will give you the documentation in Danish.
Default: The default is that the documentation language is identical to the language used in the system. This is set by the -language option.

-dsn=s
Use ODBC driver data source s. Default: BMSDSN

-featurekeysystem
The 3.0 security system is ON by default.Use this parameter to enable the old featurekey system.
-fetchahead=n A maximum of records retrieved from the database at a time. Default: 100

-hint=n
Apply database dependent SQL hint(s). Default: Empty for default settings.

-internal=relaxedsyntax The 3.0 kernel defaults to strict X++ syntax checking.
For relaxed syntax checking, use this parameter to ease restrictions.

-internet=s
Specify an Internet address to be used in �client mode. A request is sent to all the Internet addresses to obtain identification of the available Object servers.

-job=s
Run external job s prior to any other database-related action during startup.
Default: The default is not to run a job.

-language=s
Select language s for the user interface. Default: Language must be selected during setup.

-log=s
Name the SQL error log file (may include a full drive and path specification).
Default: trcAxaptaError.log in the standard Axapta log-directory.

-logdir=s
Use an alternative directory for the log files generated when you compile, import or export in Axapta. Default: The default is that the log files are generated in the Log folder.

-noauto
Use this parameter to bypass system related application calls made by the Axapta kernel. This includes the ability to bypass startup code, and some timer based calls. This parameter will allow you to startup Axapta in order to fix problems that were introduced in the application code. Normally these problems would prevent you from starting Axapta. For example, if code is introduced in the startup method that causes Axapta to go into infinite loop, and therefore, never finishes the startup procedure, . To change this, start with the �NOAUTO switch, correct the code and restart without the �NOAUTO to have the startup code included again.

-opencursors=n
A maximum of n database cursors are kept open per connection for cursor reuse.
Default: 90 cursors

-port=integer : TCP port for the AOS

-preloadthresholdmsec=milliseconds Time used for preloading.
For example, -preloadthresholdmsec=3000 results in the issue of a warning whenever preloading exceeds 3000 milliseconds. This value can not be specified per user in Tools, Options, SQL, Warnings. This threshold is only activated when warnings are enabled.
-preloadthresholdrecords=records Number of records preloaded.
For example, -preloadthresholdrecords=300 results in the issue of a warning whenever preloading exceeds 300 records. This value can not be specified per user in Tools, Options, SQL, Warnings. This threshold is only activated when warnings are enabled.

-querytimelimit =[table:][milliseconds]
Save queries running longer than a given number of milliseconds to file. If the value of QuerytimeLimit is zero (0), which is the default, no queries are logged. This parameter supports directing output to a table, i.e. SysTraceTable (default is disk-file). Use -QuerytimeLimit=ms for tracing all SQL statements exceeding the ms milliseconds threshold and -QuerytimeLimit=table:ms to do the same to table.

-regconfig=name Use a Registry configuration called name.
A configuration can be created using the Axapta Configuration Utility.

-regimport=file name Import a configuration to the Registry.
The import is performed prior to the evaluation of any other options. This means that you can import a configuration using �regimport and then select it using �regconfig.

-repair
Any non-zero value will force a re-synchronization of SQL system tables during startup. The use of this command-line parameter is logged in the Event log. Use this option to handle situations when problems in SQL system tables prevent Axapta from starting, for example missing indexes.

-retry=n
Delay in seconds before re-executing after a deadlock. Default: 5 seconds

-securityprovider=s
Selects the security provider to use with Windows Authentication and is only relevant for Object Server configuration. For AOS running on Windows NT the only valid option is "NTLM" which provides authentication based on the NTLM security provider. For Windows 2000 systems 'Kerberos' is also a valid security provider. For Windows 2000 networks with solely Windows 2000 servers and clients 'Negotiate' is also an option. This will elect the best suitable security provider automatically.

-serveridletimeout=seconds
Specifies how long (in seconds) the AOS instance should be allowed to be running without servicing clients. When this timeout expires without having clients connected, the instance will be shut down automatically. This option is well suited to be combined with setting instance startup mode to OnDemand making the server auto-start upon request from client and shutdown when no clients need service for at given amount of time.

-servermask=s
Specify the mask s for selecting a subset of object servers when running in CLIENT mode. if this option is not specified, and multiple object servers are found, all available object servers will be presented in a selection box.

-share
Share label and identifier files between several applications. if not specified, the files will not be shared.

-singleuser
Run the program in single user mode.

-sqlbuffer=n Set the upper limit in Kbytes of the fixed internal data retrieval buffer. Default: 24 Kbytes

-sqlcomplexliterals=n
About literals and placeholders. Setting sqlcomplexliterals to the value 1 enables this feature, the value 0 disables this feature.

-sqlformliterals=n About literals and placeholders.
Setting sqlformliterals to the value 1 enables this feature, the value 0 disables this feature.

-sqloraclefirstrowsfix=n
Oracle Versions 8.05, 8.06 and 8.15 occasionally selects a poor query plan for queries using the Axapta keyword firstFast row. The symptom is that an index matching the order by specification is preferred, even though another index much better serves the where part and the number of rows returned is small. Axapta includes a workaround for this problem, which you should only enable if you have verified that the above problem is the cause for poor performance. The Axapta Query Analyzer can be used for detecting this. A value of 1 enables this work around, a value of 0 disables this feature.

-sqlparm=s
Add additional parameters s upon database login. The format follows the ODBC standard: key1=value1;key2=value2. An example: DIR=c:\db;ID=9. Default: The default is no additional parameters.

-sqlpwd=s
Use password s upon login to the SQL database. Default: bmssa_pwd

-sqltrace[=Table]
Invoke SQL statement tracing to log file or table. Use -sqltrace for tracing all generated SQL statements to file and -sqltrace=table to do the same to table. Default: No tracing.

-sqluser=s
Use user name s during login to the SQL database. Default: bmssa

-startupmsg=s
Text to be displayed during Axapta startup.

-startupcmd=MyCommand
A string that is passed to Axapta and can be used to have your own startup commands executed. The string is passed in the calls appl.startup(MyCommand) info.startup(MyCommand) appl and info are instantiated objects of the application classes Application and Info respectively. The application classes are inherited from the system classes xApplication and xInfo. Learn more in the Developer's Guide. You can access the guide from Axapta's Help menu.

-useis Use integrated security during SQL database login
and thus disabling values set by using parameters sqluser and sqlpwd.

-user=s Log on as user s.

-useserverprinters
Have the client direct all printing to the printer connected to the server.

-warnings[=table]
Enable various run-time warnings which are logged to a file, or table. Use -warnings to trace all developer warnings to file and -warnings=table to trace all warnings to a table. Default: No warnings.

-windowsauth={01}
This option disables/enables Windows Authentication which, when enabled, is providing Single Sign-On and Authentication of client machine account and the user logging in.

Dynamcis AX Macros

I wanted to know how i could use string substitution to change the code in X++ using Macros. As a part of playing around with macros below are the findings. Macors in AX can be define in two ways

1. #define.TableRead(select * from EmplTable where EmplTable.EmplCode == %1;)
#TableRead("E0006")
info( emplTable.EmplCode );

2. #localmacro.TableRead
select * from EmplTable where EmplTable.EmplCode == "%1";
#endmacro

#macrolib.training
#TableRead(E0006)
info( emplTable.EmplCode );

The second way is the preferred way of creating multi line macros also if the macro includes some special symbols like ) or " then the send method is preferred

#macroLib is used when the macros are defined in a macro definations node instead of the local method scope.

Inheritance and Macros
The macros defined in the class declaration of the base class are available in the sub classes however if one of the class in the hierarcy undefines the macro then that point onwards the macro are not available in the child classes.

Move Object Layer

This was one interesting excercise we did where we moved the objects from the VAR layer to the BUS layer for one of the live implementations. The challenge was that we wanted to retain all the data and all the references.

1. To begin with we had two installations once was the dev instance where the objects were in the bus layer and the other was the live instance where the objects were in the var layer.

2. We know that while the no sequence of the objects starts from 30000 onwards in the var layer it starts from 20000 onwards in the bus layer. So once we resoted the bus layer (aod) the existing object id's in the 30,000 series became irrelevant. So we deleted all the entries in the sql dictionary table between 30000 and 40000, backup the records from the dev environment where tableid was between 20000 and 30000and restored it on the live.

3. We also know that when the table names are more than 30 chars the synchronize routine replaces the last 4 chars of the table with the objectid. So we manully looked for such objects and changed the tableid suffix in such objects from the 30000 series to the 20000 series.

4. Once we did the above we were getting a duplicate record while entering data into most of the table it was no long before we realized that the systemsequences table contains the last record id and is used to generate the record if for the new record. In this case as the object ids changed a corresponding entry for them was required in the systemsequences table with the new object ids. I wrote a script to generate a script for the right updates.

select ' insert into systemsequences
( ID,NEXTVAL,MINVAL,MAXVAL,CYCLE,NAME,TABID,DATAAREAID,RECVERSION,RECID ) '
+ 'select '
+ ' -1, isnull( max( recid ) + 1, 1 ) , 1, 9223372036854775807, 0, ''SEQNO'', ' + cast( SD.TableID as varchar) + ',''dat'', 1, -1'
+ ' from ' + SD.SQLName
from sqldictionary SD
left join systemsequences SS
on SS.tabid = SD.tableID
where 1=1
and SS.Name is null
and SD.tableid between 20000 and 30000
and SD.fieldid = 0

Remote scan during linked table Update

Faced this issue while building an interface from a SQL Server to a remote MYSQL database. We had a set of insert, update statements to synchronize a set of table however the update statements were taking forever to execute. On analyzing the execution plan we got to know that there was a remote scan being performed for each update which was slowing down the update as for each row update a remote scan for 6500 rows was being performed. On investigating further i found that this is a documented behaviour and what MSDN says is:

For linked server DELETEs or UPDATEs, SQL Server retrieves data from the table, performs any filtering that is necessary, and then performs the deletes or updates through the OLEDB rowset. This processing can result in a round-trip to the remote server for each row that is to be deleted or updated

SQL Server 2000 adds the ability to send a DELETE or UPDATE to a linked server as a single SQL statement; however, this feature only covers linked servers to another SQL Server 2000 or SQL Server 7.0 instance

Please refer knowledge base article
http://support.microsoft.com/kb/309182

So the only option we have to update without any performance overhead on the remote server is if we use stored procedures for insert and udpates so that the actual filtering and update is actually performed on the remote server rather then in a rowset at the local server.

“System does not support setup 'continuous' of number sequence

Realized that the issue was that the getNewNum function was called without transaction processing

PYLEmplLeaveDetailsTable.LeaveAppNum = NumberSeq::newGetNum(HRMParameters::numRefLeaveAppNum()).num();

just got resolved by encapsulating the same within a transaction

ttsbegin;

PYLEmplLeaveDetailsTable.LeaveAppNum = NumberSeq::newGetNum(HRMParameters::numRefLeaveAppNum()).num();


ttscommit;

Deploying AOD files directly

One of the methods of deploying customizations onto an AX installation is by carrying the aod files and replacing them on the target installation. However there is one issue with this approach if the newly updated aod file contains some new security and configuration keys the same would not get registered in the security metadeta of AX which i believe is stored in the database.

So we might land up with a menu with missing menu items as the security is not recognized for them even for the Administrator.

the way to get around this issue is to disable and enable the concerned configuration key doing so will get it registered in the AX Security related tables

Macros

For a long time i wondered if string substitution is all that the macros offer for until i realized the power of macros when it comes to a programming language.

Macros are strings which are substituted by the pre-compiler before they reach the compiler and it is this behavior which makes macros extremely powerful.

Hence macro substitutes a string in the source code which is then compiled and processed by the compiler.

A classic example of this behavior is the pack unpack pattern applied in AX. It starts with the definition of a macro which refers to all the memory variables we need to maintain state for. The pack method returns a container effectively before this source reaches the compiler the macro is substituted and the container list contains all the memory variables which are processed by the compiler and the actual memory values are composed into the container.

similarly in the unpack method when we say
[version,#CurrentList] = _packedClass;
before the above statement is processed by the compiler the macro is substituted by all the memory variables which then get initialized as this statement is executed.


Ideally the source code written once should not be repeated elsewhere coz as per the best practices it should be reused however if for some reason a piece of code needs to be duplicated through a routine the macros are the ideal candidates to maintain a single central copy of this source.

MorphX has a built-in macro-preprocessor. The purpose of macros is to make statements easy to reuse and you can declare macros wherever you can write X++ statements.

There are basically three kinds of macros: macros in macro libraries, stand-alone macros and (local) macros inside methods. All three types of macros have identical syntax and functionality. The only differences are that

• A stand-alone macro is created using the macro-node of the Application Object Tree

• A macro library is a stand-alone macro, which contains (local) macros

• A local macro is declared within a method, or stand-alone macro

X++ has these macro constructs:

#DEFINE	This statement defines a macro variable, which can be used as a constant or to make macros perform differently according to the macro variable value.
#UNDEF	This statement cancels a macro variable.
#MACROLIB	Loads a macro library. Libraries must be loaded before the macros within them can be used.
#GLOBALMACRO	Declares a global macro (header). A global macro can be used everywhere in MorphX after declaration.
#LOCALMACRO	Declares a local macro (header).
#ENDMACRO	Signals the end of a macro declaration.
#IF	Tests if the macro variable following the #IF has as given value. Includes the statements between #IF and #ENDIF.
#IF.EMPTY()	Tests the argument within the brackets, and includes the statements between #IF and #ENDIF if the argument is empty.
#IFNOT.EMPTY()	Tests the argument within the brackets, and includes the statements between #IF and #ENDIF if the argument is not empty.
#ENDIF	Signals the end of a conditional macro declaration (#IF).
#LINENUMBER	Returns the current line number. Mainly useful for debugging

Using define

You can use the define macro construct to declare symbolic constants in your program as follows:

#Define.MaxLength(100)

int intarray[#MaxLength]

Macro declaration

Macros are declared by this syntax:

Macrodeclaration = #localmacro. Macroname { text } #endmacro

Where text can be anything. Within the macro declaration, it is possible to use parameters. The parameters are named %1, %2, %3 etc. All macros are considered as text, and are unfolded before compilation. This means that there is no means of checking syntax or functionality within macros (all that appears is a compiler error in the macro with no specification of error location).

A local macro, called AnExample is declared as follows:

#localmacro.AnExample

// Some statements or text

#endmacro

A more complex macro using parameters is declared as follows:

#localmacro.ComplexMacro

#DEFINE.ARG(%1); // Defines a macro variable ARG

#IF.EMPTY(%1) // If parameter1 is empty

print “The first parameter is empty (not used)”;

#ENDIF

#IFNOT.EMPTY(%1) // If it is not empty

print “The first parameter is NOT empty, it is %1”;

#ENDIF

%2 = %3 + %4; // param2 is assigned

// param3+param4

#IF.ARG(1) // IF parameter1 is 1

print “Parameter1 has the value 1”;

#ENDIF

#UNDEF.ARG // The macro variable ARG is undefined

#endmacro

Referencing macros

You reference macros by their name prefixed with a hash mark (#). To reference the complex macro shown above, you would write:

#complexMacro(parameter1, parameter2, parameter3, parameter4)

The parameters (1-4) have a story: Parameter 1 is optional, as it is only used in the macro, if it exists (#IFNOT.EMPTY) whereas parameters 2, 3 and 4 are used unconditionally (in the statement %2 = %3 + %4) - and therefore must be included in the call. Assuming you have declared a variable called A, a valid reference would be):

ComplexMacro(1,a,3,4);

Which would expand the macro into the statements below:

#DEFINE.ARG(1); // Defines a macro variable

#IF.EMPTY(1) // If parameter1 is empty

print “The first parameter is empty (not used)”;

#ENDIF

#IFNOT.EMPTY(1) // If it is not empty

print “The first parameter is NOT empty, it is 1”;

#ENDIF

A = 3 + 4; // param2 is assigned param3+param4

#IF.ARG(1) // IF parameter1 is 1

print “Parameter1 has the value 1”;

#ENDIF

#UNDEF.ARG // The macrovariable ARG is undefined

The results of the above statements are two prints and an assignment.

A is assigned the value 7 and two lines are printed on the screen: “The first parameter is NOT empty, it is 1” and “Parameter1 has the value 1”.

If you referenced the complex macro without parameters 2-4, the result would have been:

= + ; // param2 is assigned param3+param4

And hence a compilation error occurs though you cannot see that the macro is actually expanded in this way (which makes the error harder to find).

Using macro libraries

To use a macro library, you must create the library (say TestMe) using the Application Object Tree and then load the macro using the following construct:

#TestMe;

A more preferred way of referring macro libraries is using the #macrolib keyword the advantage of using this keyword is that you will be able to reference macors with special characters and multiple lines look at the example below where a special character (") doube quote had to be used in a macro
1. #define.TableRead(select * from EmplTable where EmplTable.EmplCode == %1;)
#TableRead("E0006")
info( emplTable.EmplCode );

2. #localmacro.TableRead
select * from EmplTable where EmplTable.EmplCode == "%1";
#endmacro

#macrolib.training
#TableRead(E0006)
info( emplTable.EmplCode );

The second way is the preferred way of creating multi line macros also if the macro includes some special symbols like ) or " then the second method is preferred if you notice in the first method the double quote are passed in the parameter to the macro as they could not be a part of the macro as in the macro definition the macro would end at the first instance of the double quote.

Inheritance and Macros
The macros defined in the class declaration of the base class are available in the sub classes however if one of the class in the hierarcy undefines the macro then that point onwards the macro are not available in the child classes.

37 "Signals" From 37 Signals

1) Great businesses have a point of view, not just a product or service.

2) Writing a plan makes you feel in control of things you don’t actually control.

3) You have the most information when you’re doing something, not before you've done it.

4) Stuff that was impossible just a few years ago is simple today.

5) Failure is not a prerequisite for success.

6) Don’t make assumptions about how big you should be ahead of time.

7) Don’t sit around and wait for someone else to make the change you want to see.

8) When you build what you need, you can assess quality directly instead of by proxy.

9) Solving your own problem lets you fall in love with what you’re making.

10) What you do matters, not what you think or say or plan.

11) When you want something bad enough, you make the time.

12) The perfect time to start something never arrives.

13) Start a business, not a startup.

14) You need a committment strategy, not an exit strategy.

15) Huge organizations talk instead of act, and meet instead of do.

16) Build half a product, not a half-assed product.

17) Getting to greatness starts by cutting out stuff that’s merely good.

18) The real world isn’t a place, it's an excuse. It's a justification for not trying.

19) The big picture is all you should be worrying about in the beginning. Ignore the details.

20) Decide. You’re as likely to make a great call today as you are tomorrow.

21) The longer it takes to develop, the less likely it is to launch.

22) It’s the stuff you leave out that matters.

23) Focus on substance, not fashion. Focus on what won't change.

24) When good enough gets the job done, go for it.

25) When you make tiny decisions, you can't make big mistakes.

26) Pour yourself into your product.

27) You rarely regret saying no but you often regret saying yes.

28) Better your customers grow out of your product, than never grow into them.

29) You can’t paint over a bad experience with good marketing.

30) All companies have customers. Fortunate companies have audiences too.

31) Instead of out-spending your competitors, out-teach them.

32) Let customers look behind the curtain.

33) Leave the poetry in what you make, there is beauty in imperfection.

34) Marketing is not a department, it's the sum total of everything you do.

35) Don’t hire for pleasure; hire to kill pain.

36) Don’t make up problems you don’t have yet.

37) A business without a path to profit is a hobby.

Unlock a protected excel sheet

I had this excel sheet which was a RFP document and needed to copy a part of it as a response but this sheet was protected and selection was disabled on the sheet for the desired columns

Came across this amazing macro which would disable the password and make the columns selectable.

http://mcgimpsey.com/excel/downloads/allinternalpasswords.xls

basically when this excel sheet was opened a new addin button is created which could be used to unlock the sheets.

SID in AX Security Tables

The problem arose where we are trying to restore the live production databases into a test environment

Historically DAX has had its own users when it was integrated to windows security model the windows user's SID (SID is a unique identifier for a user or a group in Windows) was used to act like a user.

SIDs are specific for a particular domain, i.e. domain admin accounts thus in two different domains the same user will have different SIDs.

The problem is that when the DAX database is moved from the production environment to a test environment it carries the SID of the production environment in the security tables which is different from a SID of an account that is being used in the test environment.

The solution is as below:

* find out the SID of the user you use to run a DAX client after DB migration
o you can use whoami tool shipped with Windows - just run whoami /user in a console and you'll see a long string like S-1-5-21-15052... next to your login;
o if you use WinXP or an earlier Windows version you'll have to install Windows XP Support Tools to get the tool and afair it has a slightly different command line params... you'll need to use /sid option instead of /user
* run a tool like Ms SQL Management Studio and connect to the restored DAX database
* navigate to the UserInfo table, open it in a grid and update the SID value for user "admin" with the one you've found out previously
* start DAX client - you should login as an admin now

SQL Server sp_execute

We commonly come across this statement when profiling SQL Server i have wondered a lot about what it is and there was some explanation i found for it in the BOL.

The ODBC API defines prepared execution as a way to reduce the parsing and compiling overhead associated with repeatedly executing a Transact-SQL statement. The application builds a character string containing an SQL statement and then executes it in two stages. It calls SQLPrepare once to have the statement parsed and compiled into an execution plan by the database engine. It then calls SQLExecute for each execution of the prepared execution plan. This saves the parsing and compiling overhead on each execution. Prepared execution is commonly used by applications to repeatedly execute the same, parameterized SQL statement.

thus sp_execute is a system stored procedure used with "prepared" statements from a client. The number you see is an internal pointer to the execution plan on the server. The values following that number are the parameters for a particular invocation of the prepared statement


Now when we see sp_execute in the profile and we need to know the text behind it then first thing we can do it to check if there is an "sp_prepare" with the same number in the thread, if there is then you include sql_handle in the trace and you look can up the text for the cache entry using the following statement

select text
from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(plan_handle)
where session_id = [spid]


you can also enable Prepare SQL event in the profiler to get the handle of the execution plan

I found this problem where a prepared sql was taking much longer to execute when i executed the same sql in the management studio it was actually fast so i am wondering what could make a unprepared sql faster then a prepared sql?

I thought maybe the statistics are out so created the statistics for this table
with a FULLSCAN option and that really helped still not convinced why? maybe i will have to study the execution plan in both the cases and spot the difference i believe that different execution plans are being used in these cases.

Bring a Standby Database online

When Transaction Log Shipment is setup a copy of the transaction database in maintained in read-only and standby mode on a pre-configured server.

If for any reason the primary server fails and the standby server needs to be promoted use the following command to make the database available

RESTORE DATABASE [databasename] WITH RECOVERY

Dynamics AX Application file types

There are lots of files with different file extensions in the Application\Appl\Std folder. Got these details from Harish Mohanbabu's blog quite a handy information

Yes. There are quite a lot of files in Axapta Application\Appl\Standard folder. Please note that all these files will be updated whenever a new version is released. Most important them are -

1. .aod - Acronym for Application Object Data file. Each Application object layer is saved in a separate file called
"Ax< layer >.aod". For example, Axsys.aod for the SYS layer, Axusr.aod for the USR layer and so on.

2. label files : If we look at our label files in the AOT we see 4 different extensions for our labels

· ALI Axapta Label Index

· ALC Axapta Label Comments

· ALT Axapta Label Temp, Store

· ALD Axapta Label Dictionary



The ALD file is readable/editable with a text editor. General speaking you only need the ALD file. When the AOS is restarted the ALI and ALC will be generated on the fly. (or updated when the time stamp of the ALD file is bigger than the timestamp of the ALC or ALI file)

Next, a developer creates new labels. These labels will be stored in the ALT file. Not yet in the ALD file. When the final AOS will stop. AX will update the ALD file this way. It will copy the ALD file to an ALB file. Next the changes in the ALT file will be stored in the ALB file. Finally this ALB file is placed back in the ALD file and the ALB file will be deleted. (HINT: make the ALD file read only and you will see it your self)

When your AOS has creased the changes are not stored in the ALD file. Even when you start and stop the AOS again the file is not updated. To solve this issue start an AX client search for the label in the Label editor. Next stop your client and the AOS. Now the label file is updated.

3. .udb - Acronym for Axapta User Database. As the acronym indicates, Axapta stores its user details in this file. Also this file is responsible for allocating session ID to users. If this file is deleted, then Axapta would regenerate whenever the system is started.

Tip : Some times in Axapta 3-tier installations, when you look at online users form, it could return false information. For example, let us assume 5 users are currently online. But this form, might show 7 or 8 users online. Or some times, though you have enough licenses, you might receive "maximum users reached" error message from Axapta.

In such cases, the solution would be deleting the "axdat.udb" file. But before deleting it, make sure that all users are exited and AOS is properly shut down. Once this is done and the file is deleted, as mentioned above, Axapta would regenerate the "Axdat.udb" file. Please note that you might not even come across this problem. But to be on the safer side, particularly on 3-tier installations, it is always advisable to regularly delete "axdat.udb" file.

But in some special conditions, online user form may be empty in Axapta ver 2.5. This condition may happen if your license has an expiry date. Even though your license may not have expired, you might still come across this condition. In such cases please go to Technet website and do a search with this key words "The online user form is empty". You would come across an Export file. Import that export file in your installation. That would solve the problem.

4. .ktd - Acronym for Kernel Text Data.

Tip : Some times when you upgrade your installation (say for example you have installed a new SP), you might not be able to see the new features of the installed SP after the upgrade is complete.

The solution for this would be manually copying the .ktd files from Client\Bin\ directory to Axapta Application\Bin directory. The reason being - when Service pack is installed, for some reasons files in Axapta Application\bin directory are not updated. Only the files that are there in Client\Bin are updated.

5. .aoi - Acronym for Application Object Index. As the name indicates this is an index file for the Application objects.

Tip : Some times you may get funny error messages. Though you might have hard disk space, sometimes the following error might popup -

Error in file: ...\standard\axapd.aoi while reading in record ...
Error code: 38 = hard disk is full

For all the error messages involving axapd.aoi, the solution would be deleting the "axapd.aoi" file from Axapta Application\Appl\Standard directory. When you restart the system again, Axapta would rebuild this index file.

If the problem persists even after deleting the file, then check whether "Open application files in exclusive mode" is enabled (Axapta Configuration Utility --> Under General Tab). If enabled, then disable it. This should solve the problem.


6. What is "Master.aoc" file and how can I create it ?

aoc stands for Axapta Object Cache. This functionality was introduced in Axapta ver 2.5 mainly intended for best performance possible.

When running a 3-Tier Navision Axapta 2.5 client, we will cache different objects as we use the application. All objects are cached in the memory on the client machine. When the client is shutdown, the cache objects are written to disk, in a file with the extension .aoc (Axapta Object Cache). The next time a user starts the client, the executable ax32.exe checks for the .aoc file and if one exists it is loaded into memory. This gives us a performance gain, as we do not have to cache these objects again.

How to create a preconfigured cache file?

This is very simple. We configure a 3-tier client and go through the parts of the application all clients will use. Ex - General Ledger, Sales Order and Accounts Receivable. Doing this will create an .aoc file, with the following naming convention - "ax_AOS [Instance name]@[Hostname]_[username].aoc". In my case, I got a file like this -
ax_Annai@Himalaya_Harish.aoc. This file, if used as the preconfigured cache file, must be renamed to "master.aoc". Please note that this naming convention of the .aoc file would be different if you have configured an Object Server Cluster. (Ex - ax[cluster name][username].aoc)

When installing the Navision Axapta 2.5 client software, the setup.exe program will look for the master.aoc file in the directory where setup.exe is located. After completing the installation of the client, the setup.exe program will, if a master.aoc file exists, copy this file to the \Log directory, located in the default directory structure of the installed client.

When a user starts the client software for the first time, the executable as32.exe, will look for a cache file that belongs to the user, who is currently logged in. If one exists then this cache file is loaded into memory. If none exists, then the ax32.exe will look in the \Log directory for the master.aoc file. If one exists, the objects in this cache file would be loaded into memory.

Dynamics AX unpack type mismatch

I had this strange problem where in when a form loaded it gave an error on the unpack method stating type mismatch. The code was find and was not able to find any issues with it until i realized that i had done a xpo import recently which means the pack was done on the previous version and now the system was trying an unpack on a new version after the import.

in this new version there was a new parameter added which was not packed thus i had to clear the cached data and the issue was resolved.

The cached data could be cleared using the link
Tools

Trigger to Stop Database Drop

CREATE TRIGGER [ddl_trig_Prevent_Drop_Database]
ON ALL SERVER
FOR DROP_DATABASE
AS

--log attempt to drop database
DECLARE @db VARCHAR(209)
SET @db = (SELECT 'Database Dropped Attempted by ' + CONVERT(nvarchar(100), ORIGINAL_LOGIN()) +
' executing command: '+ EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]','VARCHAR(229)'))
RAISERROR(@db, 16, 1)WITH LOG

--prevent drop database
ROLLBACK
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

--turn on trigger
ENABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER


--test trigger
CREATE DATABASE test1

DROP DATABASE test1

Msg 50000, Level 16, State 1, Procedure ddl_trig_Prevent_Drop_Database, Line 11
Database Dropped Attempted by TestSQLUser executing command: DROP DATABASE test1
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.


--turn off trigger
DISABLE TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO


/****** Object: DdlTrigger [ddl_trig_Prevent_Drop_Database] Script Date: 01/11/2010 19:22:28 ******/
IF EXISTS (SELECT * FROM master.sys.server_triggers WHERE parent_class_desc = 'SERVER' AND name = N'ddl_trig_Prevent_Drop_Database')
DROP TRIGGER [ddl_trig_Prevent_Drop_Database] ON ALL SERVER
GO

--cleanup current errorlog
sp_cycle_errorlog

List of Actively running commands by SPID in SQL Server

This following code snippet would list all the actively running commands by SPID


select session_id, Text

from sys.dm_exec_requests r

cross apply sys.dm_exec_sql_text(sql_handle) t

SQL Server Transaction Log

DBCC LOG([,{0|1|2|3|4}])

0 - Basic Log Information (default)

1 - Lengthy Info

2 - Very Length Info

3 - Detailed

4 - Full

Example:

DBCC log (MY_DB, 4)

And it displays the following transaction log information:

* Current LSN
* Operation (string starts with LOP_XXXXX)
* Context (string starts with LCX_XXXX)
* Transaction ID
* Tag Bits (Hex)
* Log Record Fixed Length (int)
* Log Record Length (int)
* Previous LSN
* Flag Bits (Hex)
* Description (string)
* Log Record (Hex)



There is another command which is used to read the transaction log ::fn_dblog. I found an interesting hack by Paul S Randal on how to find which user dropped an object using the transaction log.

SELECT [Transaction Id], [Begin Time], [UID], [SPID]
FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Name] = 'DROPOBJ'

The (NULL, NULL) is the starting LSN and ending LSN to process - NULL means process everything available.


Now, this only shows us that a table was dropped, not which table it was. There's no way to get the name of the table that was dropped, only the object ID - so you'll need to have some other way to determine what the table ID is if there are multiple table drops and only one of them is malignant.

For SQL Server 2000, the code to find which object ID we're talking about is as follows (dropping the Transacation Id into the WHERE clause):

SELECT DISTINCT [Object Name] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:000000e0'
AND [Context] = 'LCX_IAM';
GO

Object Name
--------------------
(2009058193)

The object ID in parentheses is the ID of the table that was dropped.

For SQL Server 2005 and 2008, the code is as follows (with the same Transaction Id substitution):

SELECT TOP (1) [Lock Information] FROM ::fn_dblog (NULL, NULL)
WHERE [Transaction Id] = '0000:00000587'
AND [Lock Information] LIKE '%SCH_M OBJECT%';
GO

Lock Information
--------------------------------------------
ACQUIRE_LOCK_SCH_M OBJECT: 8:2073058421:0

The 8:2073058421 is the database ID and object ID of the table that was dropped.

PS If you find the you don't get enough info from ::fn_dblog, try turning on trace flag 2537. It allows the function to look at *all* possible log, not just the active log.

Install DAX without Domain Controller

The DAX installer compares current user's domain with the local machine netbios name. If they match the installer thinks that it has been run under a local user and throws an error

«You are logged on with a local computer account. You must be logged on with a domain account to run Microsoft Dynamics AX Setup».

To avoid this check change local machine netbios name in the registry (HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName) AND after that change UserDnsDomain environment variable to the same value prior to running the setup.

You can run cmd.exe, set the environment variable and run the setup from the command line so that it can "see" the changed value. After installation change ActiveComputerName in the registry back to its original value.

Transaction log not truncating

I landed into this strange problem where i was not able to truncate the log of the database i tried the backup log and then shrinkfile a no of times with no luck.

Then i remembered that the database was being replicated using transactional log shipments so maybe there was some issue there. Thus started to look for any open transactions which might be holding the transaction logs used the command
DBCC OPENTRAN(db_name)

Transaction information for database 'BATEELNAV5'.

Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (28941:199:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Got a message which confirmed that there was a replication transaction which was holding the database I got rid of the same using

use
go
sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
go


I was then able to truncate the log successfully.

AX 2009 Document Management FilePath

We are doing this project where it is required that the attachments are stored into a folder on the HDD now thatz a default configurable parameter in AX the complication is that in this case we are expecting the attachment size to be huge so much so that it exceeds the storage capacity of the server.

Well in the modern world no one takes a decision of making a change in the system to accomodate for such a scenario as the easiest option is to increase the storage space but in this case it seems that we do not have an option to further expand the storage on the server and once the server disk is filled up the only option is to either get a new server which has more slots to push more HDD's or to backup the files onto a different server.

AX allows us to configure a folder path for each file type but the issue is that it only maintains the file name in the transaction tables and the path is maintained in the parameter table which means if the path is changed at a later date AX will not be able to trace the old attachements coz when those were made the path was different

I found where my attachments for a customer were being stored and realized that the path was being referred to from the parameters table and the full file name with path was being build in a function in the docuref table called completeFileName

select DV.FileName, DV.FileType, DR.* from docuref DR
inner join docuvalue DV
on DR.ValueRecId = DV.RecId
where RefTableId = 77
and PartyId = 191


so the solution in this case is that we decide to store the file path with the file when the attachments are made so that the system remembers multiple paths for the files.

Create Clustered Index Script

I had this requirement where we identified that a no of tables in AXapta were missing the clustered index which was resulting in some performance issues. It was then recommended by Microsoft to create clustered indexes on all these tables this is what we did.

--list of all tables missing an clustered index
select SO.name, SI.*
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'



--script to generate the script
select 'create clustered index [IDX_' + SO.name + '] on [' + SO.name + '] (recid, dataareaid )'
from sysobjects SO
left join sysindexes SI
on SI.id = SO.id
and SI.indid = 1
where SI.indid is null
and SO.type = 'U'




another important query is to list all the columns in a index in this case clustered index
--list of columns in the clustered index
select OBJECT_NAME(SI.id) TableName, SI.name IndexName, SIK.indid IndexId, SC.name ColName
from sysindexes SI
inner join sysindexkeys SIK
on SI.id = SIK.id
and SI.indid = SIK.indid
inner join syscolumns SC
on SC.id = SIK.id
and SC.colid = SIK.colid
where SIK.id = OBJECT_ID( 'ContactMapping' )
and SIK.indid = 1
order by SIK.indid, SC.colorder

Space used by each Table in SQL

SQL 2005 Version
================
BEGIN try
DECLARE @table_name VARCHAR(500) ;
DECLARE @schema_name VARCHAR(500) ;
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
, schemaname VARCHAR(500) collate database_default
);
DECLARE @temp_table TABLE (
tablename sysname
, row_count INT
, reserved VARCHAR(50) collate database_default
, data VARCHAR(50) collate database_default
, index_size VARCHAR(50) collate database_default
, unused VARCHAR(50) collate database_default
);

INSERT INTO @tab1
SELECT t1.name
, t2.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

DECLARE c1 CURSOR FOR
SELECT t2.name + '.' + t1.name
FROM sys.tables t1
INNER JOIN sys.schemas t2 ON ( t1.schema_id = t2.schema_id );

OPEN c1;
FETCH NEXT FROM c1 INTO @table_name;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT OBJECT_ID FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(@table_name))
BEGIN
INSERT INTO @temp_table EXEC sp_spaceused @table_name, false ;
END

FETCH NEXT FROM c1 INTO @table_name;
END;
CLOSE c1;
DEALLOCATE c1;
SELECT t1.*
, t2.schemaname
FROM @temp_table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,tablename;
END try
BEGIN catch
SELECT -100 AS l1
, ERROR_NUMBER() AS tablename
, ERROR_SEVERITY() AS row_count
, ERROR_STATE() AS reserved
, ERROR_MESSAGE() AS data
, 1 AS index_size, 1 AS unused, 1 AS schemaname
END catch





Previous Versions of SQL
========================
DECLARE @table_name VARCHAR(500)
DECLARE @schema_name VARCHAR(500)
DECLARE @tab1 TABLE(
tablename VARCHAR (500) collate database_default
,schemaname VARCHAR(500) collate database_default
)

CREATE TABLE #temp_Table (
tablename sysname
,row_count INT
,reserved VARCHAR(50) collate database_default
,data VARCHAR(50) collate database_default
,index_size VARCHAR(50) collate database_default
,unused VARCHAR(50) collate database_default
)

INSERT INTO @tab1
SELECT Table_Name, Table_Schema
FROM information_schema.tables
WHERE TABLE_TYPE = 'BASE TABLE'

DECLARE c1 CURSOR FOR
SELECT Table_Schema + '.' + Table_Name
FROM information_schema.tables t1
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN c1
FETCH NEXT FROM c1 INTO @table_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @table_name = REPLACE(@table_name, '[','');
SET @table_name = REPLACE(@table_name, ']','');

-- make sure the object exists before calling sp_spacedused
IF EXISTS(SELECT id FROM sysobjects WHERE id = OBJECT_ID(@table_name))
BEGIN
INSERT INTO #temp_Table EXEC sp_spaceused @table_name, false;
END

FETCH NEXT FROM c1 INTO @table_name
END
CLOSE c1
DEALLOCATE c1

SELECT t1.*
,t2.schemaname
FROM #temp_Table t1
INNER JOIN @tab1 t2 ON (t1.tablename = t2.tablename )
ORDER BY schemaname,t1.tablename;

DROP TABLE #temp_Table

Inconsistencies in G/L Entry posting

The transaction cannot be completed because it will cause inconsistencies in the G/L Entry table. Check where and how the CONSISTENT function is used in the transaction to find the reason for the error. Contact your system manager if you need assistance. Parts of the program mark tables as inconsistent during very comprehensive tasks, such as posting. This prevent data from being updated incorrectly.


1. Rounding errors could be one of the issues making a line inconsistent.


Finding the transaction which cause the issue ? there is a solution got this from Rashed Amini courtesy mibuso

1. create the following CU


OBJECT Codeunit 50000 Single Instance CU
{
OBJECT-PROPERTIES
{
Date=10/11/07;
Time=[ 2:50:02 PM];
Modified=Yes;
Version List=MOD01;
}
PROPERTIES
{
SingleInstance=Yes;
OnRun=BEGIN
IF NOT StoreToTemp THEN BEGIN
StoreToTemp := TRUE;
END ELSE
FORM.RUNMODAL(0,TempGLEntry);
END;

}
CODE
{
VAR
TempGLEntry@1000000000 : TEMPORARY Record 17;
StoreToTemp@1000000001 : Boolean;

PROCEDURE InsertGL@1000000000(GLEntry@1000000000 : Record 17);
BEGIN
IF StoreToTemp THEN BEGIN
TempGLEntry := GLEntry;
IF NOT TempGLEntry.INSERT THEN BEGIN
TempGLEntry.DELETEALL;
TempGLEntry.INSERT;
END;
END;
END;

BEGIN
END.
}
}






2. And in CU 12 I add the following Code in function FinishCodeunit


FinishCodeunit()
WITH GenJnlLine DO BEGIN
IF GLEntryTmp.FIND('-') THEN BEGIN
REPEAT
GLEntry := GLEntryTmp;
IF GLSetup."Additional Reporting Currency" = '' THEN BEGIN
GLEntry."Additional-Currency Amount" := 0;
GLEntry."Add.-Currency Debit Amount" := 0;
GLEntry."Add.-Currency Credit Amount" := 0;
END;
GLEntry.INSERT;
//MOD01 Start
SingleCU.InsertGL(GLEntry);
//MOD01 End
IF NOT InsertFAAllocDim(GLEntry."Entry No.") THEN


3. Once you've made the changes. You run the SinleInstanceCU Once.
4. Then do what ever you do to get the consistency error.
5. Then Run the SingleInstanceCU again.
6. You'll see a list of GL lines. You will see why the transaction is not balanced.

CONTEXT_INFO Connection wide variable

Need for a connection wide variable at the database level arose when I was creating an interface between Navision and MSCRM

The requirement was that when an Item was created in Navision it was pushed to CRM and when any changes were made in CRM they were reflected back in Navision we used triggers and things were fine but due to a two way interface it was required that the trigger should ignore any updates which were being made during the synch process and only the entries which were being made by the user on the front end should be captured for sync.

This is what we did. The procedure which was updating the CRM item table set the context_info as shown below

--context_info is used in the target tables triggers to ignore updates
--else it results in endless updates
declare @CONTEXT_INFO varbinary(128)
select @CONTEXT_INFO = CAST ('SynchronizeNAV' as varbinary(128) )
set CONTEXT_INFO @CONTEXT_INFO


In the trigger on the Item table in CRM we checked if the update/ insert was being made by the user or by the sync procedure as below

declare @sourceProc varchar(20)
select @sourceProc = cast ( context_info() as varchar)
set @sourceProc = ltrim( rtrim( @sourceProc ) )
if ( @sourceProc = 'SynchronizeCRM' )
return


one thing to ensure while using the context_info is that once the use is done it should be reset to null else it will continue to exist with the set value
thus at the end of the process we user

set context_info null

Convert timestamp to String

select master.dbo.fn_varbintohexstr(@@DBTS)

Get a list of Objects modified after a given date from AOT

1. Goto AOT - Expand "System documentation" - Expand "Tables" - Tablebrowse "UtilElements" or Tablebrowse - "UtilIdElements"
2. Right click in field "Utillevel" - select Filter By Field - type - usr - press ok
3. Right click in field "RecordType" - select Filter By Field - type - SharedProject - press ok
4. Right click in field "CreatedDate" - select Filter By Field - type - 01-Jan-2010..

Oracle Linked Server

Following are the steps involved in creating a linked server and working with stored procedures on the Oracle Database

1. Install the Oracle Client software on the server
2. Create a linked server as follows
EXEC sp_addlinkedserver
'OracleLinkedServer', 'Oracle',
'MSDAORA', 'OracleServer'

The name of the linked server is Oracle-LinkedServer.

The second parameter, product name (Oracle),is optional.The third parameter specifies the OLE DB provider.

The third parameter MSDAORA is the name of the Microsoft OLE DB Provider for Oracle.

Thr fourth parameter is the data source name of the Oracle Server (This is the TNS name of Oracle server which should work when pinged using a utility called TNSping and the servername this utility is installed with the client software of Oracle.)

3. Add Login information for the linked server
EXEC sp_addlinkedsrvlogin '
OracleLinkedServer ', false,
'SQLuser', 'OracleUser',
'OraclePwd'

The first parameter, Oracle Linked Server, specifies the name of the linked server system that you created.

The second parameter determines the name of the login to be used on the remote system.A value of True indicates that the current SQL Server login will be used to connect to the linked server. This requires that the logins on the two database servers match, which is typically not the case.A value of False means you'll supply the remote login.

The third parameter specifiesthe name of a SQL Server login that this remote login will map to.A value of NULL indicates that this remote login will be used for all connections to the linked Oracle server. If the Oracle system uses Windows authentication, you can use the keyword domain\ to specify a Windows login.

The fourth and fifth parameters supply login and password values for the Oracle system.


3. To be able to use stored procedures on Oracle the RPC paramter should be enabled on the server which is done as follows:-
sp_serveroption 'ORALINK', 'rpc out', 'true'
sp_serveroption 'ORALINK', 'rpc', 'true'


4. To access the tables on a linked server, use a four-part naming syntax: linked_server_name.catalog_ name.schema_name.table_name.

For example, to query the sample Oracle Scott database, you'd enter the statement

SELECT * FROM OracleLinkedServer..SCOTT.EMP
(Please mark the double dots after the server name which means that the catalogue or database name is not required and used by default this is because in oracle each database is treated as a server with a TNS name)


5. Test a stored procedure
declare @lstatus varchar(240)
set @lstatus = ''
execute ( 'BEGIN JNC_RMS_API.CHECK_CONNECTIVITY( ? ); END;', @lstatus output) at ORALINK
print @lstatus

Please ensure that the strings being sent for output parameters are initialized with a default value ( set @lstatus = '' ) else the procedures don't execute.

AX Workflow Error

From Blog

1- Open cmd as administrator , Right click run as administrator
2- Go to folder C:\inetpub\AdminScripts>cscript adsutil.vbs get W3SVC/APPPOOLS/Enable32BitAppOnWin64
3- If this node is True just set it to False “C:\inetpub\AdminScripts>cscript adsutil.vbs set W3SVC/APPPOOLS/Enable32BitAppOnWin64 False”
4- Restart IIS iisreset and retry the installation.

Keyword Options for X++ select Statements Keyword

Description

firstfast
Fetches the first selected record faster than the remaining selected records.

firstonly
Returns only the first selected record.

forupdate
Selects records for updating.

nofetch
Specifies that the Dynamics AX runtime should not execute the statement immediately because the records are required only by some other operation.

forceplaceholders
Forces the Dynamics AX runtime to generate a query with placeholder field constraints. For example, the query generated for the preceding code example looks like this: select * from myTable where myField1=?. Database query plans are reused when this option is specified. This is the default option for select statements that do not join table records. This keyword cannot be used with the forceliterals keyword.

forceliterals
Forces the Dynamics AX runtime to generate a query with the specified field constraints. For example, the query generated for the preceding code example looks like this: select * from myTable where myField1='value'. Database query plans are not reused when this option is specified. This keyword cannot be used with the forceplaceholders keyword.

forceselectorder
Forces the Microsoft SQL Server query processor to access tables in the order in which they are specified in the query. (No effect on Oracle.)

forcenestedloop
Forces the SQL Server query processor to use a nested-loop algorithm for table join operations. Other join algorithms, such as hash-join and merge-join algorithms, are therefore not considered by the query processor.

reverse
Returns records in reverse of the select order.