RECID Maxed out

We have been struggling with this issue of the system sequences table is getting maxed out on the RECID for some tables. While the table that gets maxed out are random and without a pattern it mostly happens for table no 65482.

We also identified that this happens mostly when some user logs into the system. Which led to a conclusion that there is something at the user login stage that is causing the problem. We took some help from microsoft support desk and generated the trace file for the login steps.

 To generate the startup trace file follow the following steps : -

1. Login to the application server where the AOS is installed. 
2. Start the performance monitor  (start -> run -> perfmon ) and initiate the trace log
3. Go to User Defined and new 
4. Create the new trace using the AOSTRACE.XML template 
5. Log in with a client and stop it

1.  Each trace would be created and saved in a folder with an .etl extension
2. Copy the trace file created and open the same in Microsoft Dynamics AX Trace Parser. (can be found on the product CD).

When the trace file was analyzed it was found that the system was trying to run the initialization checklist on startup. This was evident from the fact that there were records in the following tables with running status.

1. Syssetuplog 
2. Syssetuppartitionlog  

These records were deleted using the following scripts : -

Delete from Syssetuplog

Where description like ‘Running’

Delete from Syssetuppartitionlog

Where description like ‘Running’

Once the above records were deleted we emptied all the compilation log and libraries and did a fresh compile of the application. 

The following table was truncated in the model database to get rid of any previous logs

• SYSXPPASSEMBLY

       The following folders were truncated in the AX server folders to delete the CIL libraries

•  C:\Program Files\Microsoft Dynamics AX\60\Server\XXX\bin\XppIL

It is assumed that that the initialized checklist got initiated when the second AOS was installed on our server. The process followed to compile and load an environment with 2 AOS servers was as follows

1. Stop All AOS
2. Start one AOS and wait until it is Running
3. Run AXBUILD on that AOS C:\Program Files\Microsoft Dynamics AX\60\Server\XXX\bin\AXBUILD.exe xppcompileall
4. When Finished with compilation, Restart this AOS
5. Log into a client and run a FULL CIL
6. When finished, stop the client, Stop the AOS
7. Start the second AOS and wait until it is up running
8. Start the First AOS
9. Log in users

We also noticed an event viewer log suggesting a missing stored procedure in AX.
Run AXUTIL SCHEMA 
Run AXUTIL OPTIMIZE to rebuild the model database


Another possible reason that was potentially considered for the max out was that maybe due to internal procedures using the SystemSequences Table it is possible the locks are being escalated and the table is getting locked out. When AX is trying to read the locked table it is possible generating a higher no to avoid a duplicate.

ALTER TABLE SYSTEMSEQUENCES

SET (LOCK_ESCALATION = DISABLE);

SSRS Report Caption

Had this strange issue when the dialog of the report was not updated for the caption. The dialog picks the value for the first time from the MenuItem, however this only happens at the time of first run and there after the details are saved in usage data.

The only means to get rid of this information is to clear the usage data. Use the below filters
RecordType = Class
ElementName = SrsReportRunController

Select the data relevant to the report to be refreshed and press CTRL + F9 to delete the records.

Permissions to Dimension Control

We had this issue in an implementation, where the payroll manager was not able to view the dimensions tab on the employment form. This tab was required to be filled in by the payroll manager to cost the right dimensions for the payroll. As the control is painted using a class and there is no MenuItem to grant permissions to, this particular case has to be resolved using explicit permissions.

Below is how the permissions were granted.

1. Open the security Role using AOT, the one which requires the permissions to this tab. 
2. In the Role node expand the permissions node and then browse to forms node
3. Right click and add the form in question. You can also drag the form in this node if it is open in a separate window. 
4. Once the form appears right click on the form and add new control. You can also drag the control called TabFinancialDimension onto the form, if the form is already open in a separate window.
5. Once the tab has been added, select the tab and look up the property sheet and update the effective access property to a relevant value. 

The above case where explicit permissions are required to be granted to a specific control on a form occurs when the control property for need permission has been set to Manual. In all other cases once a permission to the form has been granted the same permissions are cascaded to all the controls on the form.

AX 2012 EP Error to display approvals page

One fine day we encountered a strange error on the Enterprise Portal. This error was flashed when the Approvals page was being displayed.

Field Group Details does not exist.

This error was quite specific that the system expects a field group called Details in some table but we were not sure where. Finally after a lot of hit and trial we found that the issue is with the way the EP approval page display is structured. The system expects that for any table participating in the workflow there should be a details field group designed.

We started getting the above error once a new workflow was enabled for the user and the workflow involved a table (where the workflow status is maintained) which was missing a group called details. We added this group to the table and the page was up again.

RAM DISK

Recently i moved from a HDD to a SDD the results are fantastic. I now boot my windows in less than 20 seconds. This led me to investigate further on SDD drives and understand what else can i do to have a good performance with my laptop (and obsessed i am with performance).

The first thing i understood is that to have a good life for the SSD we should try and limit the no of reads and write. Once of the potentials areas where this can be limited in the temp work folder for windows and internet explorer.

The solution is to create a RAM disk and move the temp contents to this disk. Obviously this would also mean that we will have to find a solution to persist the contents of the RAM Disk which otherwise looses its contents on a restart.

I landed on an application call imdisk which is a freeware and does the job pretty well. Along with imdisk we need a utility called rawcopy to store the contents of this disk on a shutdown. The right solution would be to automate the two events, for this we would need two batch files in windows called shutdown and startup respectively. The details of these files are as follows:-

contents of the shutdown batch file
C:\RAMDISK\rawcopy.exe -mld \\.\R: "C:\RAMDISK\RDrive.img"

contents of the startup batch file
imdisk -a -t vm -f C:\ramdisk\rdrive.img -m R:

These files can be scheduled either using the windows task scheduler. Alternatively an entry can be made in the group policy (gpedit.msc) under
Local Computer Policy -> Computer Configuration -> Windows Settings -> Scripts

Double click on Startup or Shutdown applets on the right pane and add the batch files therein.

XDS MyConstruct

My construct is a pattern used to implement record level security when the business logic to implement the restriction is not straight forward.

Eg. We had a requirement at a customer where the HR wanted the records to be restricted as per the position hierarchy. So if a manager logs in he should be able to see managers reporting to him and any subordinates below these managers.

The answer to the above problem is to implement the My construct pattern for security. My Construct uses temporary tables of type TempDB which are populated using a table method called XDS().  This method is available for developers to write X++ logic to populate the temporary table. After the temporary table is populated, subsequent policy queries can use this temporary table.

In MyConstructs, we have the ability to refresh the data either PerSession or PerInvocation.

Below is the code for an overridden XDS method of a table

public RefreshFrequency xds()
{
    SPYMYSubordinate                mySubordinate;
    List                            workerList = new List(Types::Int64);
    ListEnumerator                  workerListEnumerator;
    HcmEmployment                   hcmEmployment;

    List subordinate(HcmWorkerRecId _worker,List _workerList)
    {
        HcmPositionHierarchy            positionHierarchy;
        HcmPositionWorkerAssignment     positionWorkerAssignment;

        _workerList.addEnd(_worker);
        while select positionWorkerAssignment join positionHierarchy
            where positionWorkerAssignment.Position == positionHierarchy.Position &&
                  positionHierarchy.ParentPosition == HcmWorker::getPrimaryPosition(_worker) &&
                  positionHierarchy.PositionHierarchyType == HcmPositionHierarchyType::lineHierarchyType()
            join hcmEmployment
            where hcmEmployment.Worker == positionWorkerAssignment.Worker
        {
            _workerlist = subordinate(positionWorkerAssignment.Worker,_workerlist);
        }
        return _workerlist;
    }

    workerList = subordinate(currentWorker(),workerList);
    workerListEnumerator = workerList.getEnumerator();
    while(workerListEnumerator.moveNext())
    {
        mySubordinate.Worker = workerListEnumerator.current();
        mySubordinate.insert();
    }

    return RefreshFrequency::PerSession;
}

Refresh SSRS Report

There is this common problem encountered while working with SSRS reports and AX 2012. The report changes are not reflected on the SSRS Reports servers

I found a sequence of steps which if followed ensures that the Report is updated. These steps are as follows:

1. Rebuild all on Visual Studio Project
2. Add the project to AOT
3. Right click the report on AOT and Restore
4. Deploy element from AOT 

Hope for the best :-) 

whoam i ? get the SSID

get extended who am i information in the system

wmic useraccount get name,sid

AX 2012 EP Timeout

Step 1:
Increase the timeout settings for EP in AX 2012. Looks like the solution is to increase the timeout setting for the ajax asynchronous calls, this should be done on each page where the timeout needs to be increased luckliy there is a central place as well where it can be done as shown below

1) Open the master page from \Web\Web Files\Static Files\defaultaxV4 in the AOT
2) Search for the OnLoad() method to replace in something in below.

protected override void OnLoad(EventArgs e)
{
base.OnLoad(e);
AxScriptManager scripts = AxScriptManager.GetCurrent(this.Page);

if (scripts != null)
{
scripts.AsyncPostBackTimeout = 900; // 600 seconds
}

//Call Header.DataBind to generate dynamics styles in header
Page.Header.DataBind();
}


Step 2:
Sharepoint has an service known as the request management service. This service manages all incoming requests and evalutes which is best possible machine to machines in the farm to handle the request.

The basic process to manage the request service is as follows

1. Get a reference to the SPWebApplication
2. Get a reference to the request management settings for the web application
3. Set the parameter
4. Update the setting

start sharepoint 2013 management shell. It can be found in the program files using the search option in windows

$waUrl = "http://ax-app:290"
$wa = Get-SPWebApplication $waUrl
$rmSettings = $wa | Get-SPRequestManagementSettings
$req=$wa.RequestManagementSettings
$timeout2= New-TimeSpan -minutes 5
$req.Requesttimeout=$timeout2
$req.update()



Step 3:
 Increase the timeout setting for the application pool associated with bcproxy in the IIS console.

AX 2102 No Sequences

https://community.dynamics.com/ax/b/goshoom/archive/2013/10/16/year-in-number-sequence-ax2012.aspx

AX 2012 Worker Contact Information

The address book framework in AX2012 comprises of quite a complicated schema. We might often be required to upload the contact information of the employees during an implementation as the bulk might be big enough to rule out data entry.

I had this requirement to upload the contact information of employees and ended up traversing the schema for the same here it is


select LEA.*
from HCMWorker W
inner join DirPartyTable DP
    on DP.RecID = W.Person
inner join DirPartyLocation DPL
    on DPL.Party= DP.RecID
inner join LogisticsElectronicAddress LEA
    on LEA.Location = DPL.Location
where W.PersonnelNumber = 'T00062'

Database Reindex

Below is the query to reindex all the tables in a database

DECLARE @TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'base table'

OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Reindexing: ' + @TableName
        DBCC DBREINDEX(@TableName,' ',90)
        FETCH NEXT FROM TableCursor INTO @TableName
    END
CLOSE TableCursor
DEALLOCATE TableCursor

sp_change_users_login

Encountered a strange issue where i was not able to drop a user and was not able to link this orphan user to an id.


Finally i realized that the SID of the user in the database was not matching with the SID of the user in the windows domain. This was realized when i used the alter user command which gave a more meaningful message as compared to sp_change_users_login

ALTER USER [SDCIT\Ali]     WITH name=[SDCIT\Ali]

the option then was to delete the existing user in the database. However before i could delete the user i had to transfer all its owned objects to dbo.

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('SDCIT\Ali')

-- now use the names you find from the above query below in place of the SchemaName below
ALTER AUTHORIZATION ON SCHEMA::[SDC\Ali] TO dbo

once the user was dropped i was able to create a new user and link it to the windows domain. 

MS SQL - Split a String

This is the first time that i used the apply operator. As the name suggests the apply is an operator hence it works at each row level rather than at a data set level and this comes truly handy to solve the problem on hand.

As defined in TechNet : The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.

I wanted to update a worker information and the data provided by the customer has the employee name in a single string using space as separator.

Below is the SQL statement for same.

select TCE.EmpCode   
    , substring( Emp.[Employee Name] , 1, P1.pos ) FirstName
    , case when p2.pos = 0 then '' else substring( Emp.[Employee Name] , P1.pos + 1, P2.pos - P1.pos) end MiddleName
    , case when p2.pos = 0 then substring( Emp.[Employee Name] , P1.pos + 1, len( Emp.[Employee Name] ) - P1.pos)
        else substring( Emp.[Employee Name] , P2.pos + 1, len([Employee Name]) - P2.pos) end LastName  
from TCE
left join EMP
on Emp.[Employee Number] = TCE.EmpCode
cross apply (select (charindex(' ', EMP.[Employee Name] ) ) ) as P1(pos)
cross apply (select (charindex(' ', EMP.[Employee Name] , P1.Pos + 1) ) ) as P2(pos)

AX 2012 Time Profiles

1. Profile :A Time profile is one of the key setups that is required for the time and attendance functionality. A time profile dictates the different types of attendance transactions to expect for a day.

2. Profile Type: A profile type is used to identify how a time interval is classified as either Standard time, overtime, break, and flex time on a given day. A profile type can be configured as one of the 8 profile specification types given below :

1. Clock in : Time when the worker is expected to clock in
2. Clock out: Time when the worker is expected to clock out.
3. Standard time : Time to be considered as work hours during the calculation
4. Overtime : Time to be considered as overtime during the calculation
5. Break : Time to be considered as unpaid break during the calculation
6. Paid Break : Breaks may be paid by the legal entity (Paid break). Otherwise the break time will be deducted from the work time.
7. Flex + : indicates that the flex balance is increased if the worker is at work. Typically, the Flex+ periods are put before clock-in and after clock-out. .
8. Flex -  indicates that hours are deducted from the flex balance if the worker is not at work. Typically, the Flex- periods are put between clock-in and clock-out times.

3. Profile Groups: When there is a need to group a set to time profiles together and set rules for selection of a certain profiles based on date and time constraints then profile groups can be used. 

4. Profile Relation: The profile relation on a profile group defines the profile to be activated for the worker based on their clock in time. In the screen below we can see that from 28 June to 27 July a Ramadan profile is activated which would reduce the work timings during the holy month of Ramadan.

A more visible use of the profile being decided based on the clock in time is the use of the shifts in a company. Consider a scenario when an employee could clock into any of the 3 shift that cover the 24 hours in a day one way to apply the relevant profile would be by using profile relations to decided what time profile to be applied to the worker which in turn would decide his break and overtimes.

4. Profile Calendar: Profile calendar is used to a specific work time profile to one or more workers on a specific date regardless of the clock in time.

In the screen below july 27 has been set as half day for the entire company. This form can also be used to setup holidays.

4.1 Work Planner: In the profile calendar form above there are links available to populate entries into it while compose and copy interval are simple. Work planner is a graphical tool used to create profile calendar entries graphically.

To use the form first set a selection in the list of profiles available (marked 1 in the screen below) then click on the desired cell in the grid (marked 2 in the screen below)

The data changes from this screen would be committed to the profile calendar form however records might not be visible due to the active filters on the form so make sure you open the form from the menu Human Resouce -> Area Page -> Setup -> Time and Attendance-> Time Profiles -> Profile Calendar.

Import a Department Hierarchy

Department hierarchy is one of the key elements of implementing the AX 2012 HR solution.

In this example I have used the DIXF framework for data import. The first thing that i realized is that once a hierarchy is imported it appears as a draft version which has to be published. So don't get worried when you import a hierarchy and you don't see the changes visually on the hierarchy.

The table that stores the import from the DIXF is OMREVISIONEDIT. This can be confirmed looking at the definition of the Organization Hierarchy entity in the DIXF module.

The first issue i encountered is that my legal entity was not getting attached to the department as per the csv file created. As a result i explored the transformation class in DIXF called "DMFOMHierarchyEntityClass" and looked at the function establishing the link called "generateOmParentLink" it didn't take me time to realize that the function was expecting the dataareaid instead of the name in case the organization unit was of type "legal entity"

Once updated success was on its way.

Replace with a part from Find Sting

Editplus probably is the best ascii based text editor that i have experienced so far and the most compelling reason is the find and replace capabilities that this editor allows.

I recently had this task to create an excel sheet full or formulas and just when i started writing these formulas i started wondering if i could manage a shortcut :-)

Here are the details

1. Firstly i wrote the formula on the first row using combination of absolute and relative referencing such that it could be copied to all the rows below while still referring to the right cells.
2. I realized i made a small mistake in the formulas and editing all the formulas again was a daunting task and so the exploration began.
3. I used the Excel feature to display all the formulas in the rows instead of the values using "Ctrl + ~" character combination
4. I copied all the formulas in EditPlus
5. I landed up with a string as below (only a part is pasted the actual string was quite long)

=IF($H4>$AE$1,$V4/(($H4-$G4)/30),0)

=IF($H4>$AF$1,$W4/(($H4-$G4)/30),0)

I wanted to add a AND clause in the formula and a simple find replace would not work as a part of the replace string was variable and had to reproduced from what was being replaced (highlighted in red above). So i used the tagged expression feature of editplus to copy this into the replaced string.

A tagged expression is denoted using round brackets "( )" and can be referred in the replace string using \1. So i used the below expressions

Note: a backslash is used before the $ symbol as it is a special character and the backslash is used to tell editplus to ignore the special meaning $ symbol has to it.

Find: \$H4>(\$....)

Replace : AND($H4>\1,$G4<=\1)

and got the result

=IF(AND($H4>$AE$1,$G4<=$AE$1),$V4/(($H4-$G4)/30),0) =IF(AND($H4>$AF$1,$G4<=$AF$1),$W4/(($H4-$G4)/30),0)

Access Datasheet Change font, or font style, size, and color

An access form is different from a form created in any other environment that i have seen in the sense that the form is does not have a fixed design and the same form design could open in a datasheet (grid) or a Single Form mode while the cosmetic changes on the form are retained in the single form mode during the grid mode the appearance parameters reset to system defaults

The visual parameters like Font, Style, Size and color for a form in a datasheet mode can be changed at the run time by clicking the Home Ribbon and changing the font on the toolbar. 

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