Access 2010 Data Macros and LocalVars

Data macros is a new and exiciting additon to the MS Access 2010 offerrings. However i had a tough time getting it to work. Belowe are some of the issues identified working with Data Macros

1. The Lookup A Record Data Block only works if a record is found and does not work if a record is not found. So you cannot compose a contruct which triggers if the lookup fails of yields zero records.
2. The way lookup works is that if the record is found, then code inside of the code block runs (code indented inside of the lookup). If record is not found, then all of that indented (nested) code does not run.
3. The work around is using LocalVar. So one would have to declare a local variable above the lookup construct and then in the lookup set the local variable to a value to identify that a record was found.
4. The [localVars]![VariableName] also have a silly problem when trying to use it. There is actuall no need to prefix any variables in code with Localvars infact if you do the code fails so one has to ensure that the prefix is not used while inside a data macro.

 

Maximize Remote Desktop

Shortcut to maximize the remote desktop window

Alt + Ctrl + Pause/ Break

Financial Ratios

 Financial ratios are important pointers to the performance of a company. Looking at a combination of these ration a lot of conclusion can be drawn about the company's performance and operations. The important financial ratios are as follows

Important Terms:

1. Shareholders Equity: At any given point the equity is the total assets - total liabilities.  
2. Operating Income: it is the income or profit generated by the operations and is calculated as total sales - COGS - operating expenses - depreciation. It is also known as EBIT earnings before interest and taxes.

Debit/Equity: This ratio is calculated as
                 Total Liabilities
             __________________
             Shareholders Equity
 a debt to equity ratio of 5 would mean that for every 1 dollar of equity there is a 5 dollar liability. Any company with a high Debt to equity would have low investor interest.



Equity Ratio: This ratio is similar to the above debt to equity ratio, however the focus here is on assets instead of the liabilities. It is used in central europe and is calculated as follows:
                     Total Equity
                  ______________
                      Total Assets
This ratio helps to detemine how much the shareholders would receive in an event of a company wide liquidation. This ratio is expressed as a percentage of the total assets. Thus an equity ratio of 45% for a company with total assets of 500 $ million would mean that in an event of liquidation all the shareholders put togeather would receive $225 million (45% * $500).



Return on Equity : This ratio is expressed as a percentage and is calculated as
                           Net Income
                 __________________
                  Shareholder's Equity
This ratio is a measure of how efficient a company is at generating profits. A company with higher ROE took lesser investment and generated higher profits and hence would be of interest to investors.


Operating Profit Margin: This ratio is expressed as a percentage and is calculated as
                      Operating Income
                    _______________
                         Total Revenue
This ratio is a measure of how much the company makes on each dollar of sales before interest and taxes. This ratio displays how efficient a companys operations are at making profits. An operating profit margin of 12% would mean that it the operations make $0.12 (before interest and taxes) for every dollar of sales.

Costing Methods

Standard Cost

When to use standard costing to value the inventory depends on two issues. 

Firstly, if you in the business of make to stock manufacturing, making the same things in the same way over and over. Make to stock environments make sense for standards since most things are made over and over again, and are supposed to use the same effort, and have the same input costs. If they don't, management should know about it promptly to get things back on track. That's the role of standard cost variance reporting.

Secondly, do you have the resources on staff that can manage standard costs in a timely manner, before any transactions happen? In early days with the missing sofistication of the business systems Standard costing was an easy substitute for the vast amount of data accumulation required to aggregate actual cost information. Also in business scenarios where there is a possibility of negative stock there might be challenges to cost these negative transactions where standard cost works well.

Average

The average costing method is the preferred method of costing for distribution and other industries where the product cost fluctuates rapidly. The fluctation in the reported profits (monthly/ yearly) are reduced when using this method as the increase in the cost would be lesser when compared to the increase in the average cost.


FIFO

It is very common to use the FIFO method if one trades in foodstuffs and other goods that have a limited shelf life, because the oldest goods need to be sold before they pass their sell-by date.


LIFO

In the LIFO method the last inventory cost is consumed first so in most cases this increases the COGS and hence reduces the margins that are reporting in the P&L, because the cost of goods generally goes up over time; which also reduces the income tax liabilities of the company this method however is used in US but due to the above mentioned flaw is disallowed in non-US countries.

JQuery

This week i have started a new initiative to develop a mobile application on top of Dynamics AX however i want to have this application portable across the different client OS available on the mobiles namely Android, Windows, Apple etc.

The platform adopted for the same is HTML5 and JQuery while HTML5 is the latest version of HTML with some very good enhancements JQuery is a javascript framework which makes it easy to work with javascript.

To begin working on javascript there is a library of javascripts functions that we need to refer on our page. We have different versions of this library the popular ones are from google and mircosoft.  JQuery is activated using the dollar symbol followed by round brackets "$()". Below is the first JQuery page i built for printing "Hello World" :

<!DOCTYPE html>

<head>
<title>Hello World</title>
<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js"></script>

<body>

<div id="divTest1"></div>

<script type="text/javascript">
$("#divTest1").text("Hello, world!");
</script>

</body>

</html>

Dynamics AX Query framework

This is a very important diagram which details the hierarchy of the query classes that are available in Dynamics AX to access data.

Eg
public void myQuery2()

{
Query q;
QueryRun qr;
QueryBuildDataSource qbd;
QueryBuildRange qbr;
;

q = new Query();
qbd = q.addDataSource(TableNum(CustTable));
qbr = qbd.addRange(FieldNum(CustTable, AccountNum));
qbr.value('4005');
qbd.addSortField(FieldNum(CustTable, Name));
qr = new QueryRun(q);
qr.prompt();
pause;
}

Debugging Navision Employee Portal

After a long time today i had a chance to work on the Navision portal the need was to debug a problem as we know the navision really is a 2-tier application and the NAS is only the Navision client running in stealth mode as a service i remember having debugged this application server in the past but was lucky enough to recapitulate the same here is how it is done.

1. Stop the NAS server from the management console
2. Create a new codeunit in the navision client
3. In the run method of the new codeunit instatiate and execute the StartNAS method of the 6810 codeunit with a parameter 'NEP-1' or whatever you have used in your instance of NAS; it should display a message stating that the service has started.
4. That's it the debug mode is now enabled within the navision client now just enable the debugger and start debugging.

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.