Thursday, October 26, 2017

SQL scripts repo

Just sharing a collection of SQL commands which we can use for various information retrieving w.r.t databases and tables in an SQL instance. Will try to keep this post updated with the commands I encounter. 

Go here... https://github.com/dax516/SQL/    

  • One example is as below is a command to check the number of tables in a particular database
    USE [YOUR DATABASE NAME]
    SELECT COUNT(*) from information_schema.tables
    WHERE table_type = 'base table' 
Happy coding! 

Monday, October 16, 2017

Rebuild index for all tables in a SQL Express DB

I have this weird situation in one of my ongoing projects, where there are several hundreds of SQL Express instances hosting Dynamics AX Channel Databases which are spread across few high performance virtual servers. Over a time we noticed that Server performance has reduced and a result of a good meeting with all the parties involved - we identified that SQL index rebuild was never performed even with loads of transactions and master data has been moving via the Channel databases. 

The obvious reason being - the SQL instances are Express and it doesn't support SQL Agent - to simply configure a Rebuild job out of the box. The below script can be helpful in order: 



  1. Get the current status of Index update
  2. Update indexes for all objects in all databases in a particular SQL instance. 

Hope this helps. Happy coding!

Wednesday, September 20, 2017

Switching user in visual studio 2015

Today when I logged into one of my VMs and started to use another Account with my Visual Studio 2015 - I have faced this issue - "Your account XXXX@YYYY.com cannot be connected to Visual Studio as you have already logged in with AAAA@BBBB.com. If you want to change the user - use Switch user. And if you want to continue, enter the credentials again" 

The error message might not be exactly the same, but you get the point. 


The solution I have applied is to clear of the register for the current User setting. You can do that by: 



  1. Open run > regedit
  2. Go to HKEY_CURRENT_USER > Software > Microsoft > VSCommon > 14.0 > ClientServices > VisualStudio > IdeUser
  3. Delete the existing ID
  4. Restart Visual Studio 
  5. Login with your desired credentials 
  6. and OLA... it should work. 
Happy coding!

Saturday, March 18, 2017

Pack() and Unpack() methods with RunBaseBatch

The pack() and unpack() methods can be override-ed when you extend your class with RunBaseBatch. The pack() and unpack() in Dynamics AX define serialization

Would be ease to understand with an example - Most of the interactive objects in Dynamics AX automatically store the last saved user parameters/values and when you reopen the object, you could already see the values preloaded from where you left. 
This happens because of the pack() and unpack() methods. 

For example, perhaps you remember running a report with a specific parameters and the next time you open the same report, the parameter values are preloaded as per your first selection. 

Have a look into the AOT\Classes\Tutorial_RunBaseBatch, for an example of RunBaseBatch with pack() and unpack() methods.

The confusing part could be the macros if you are a beginner. So let's go through an example without using any macros and then we can improvise it later to understand better. 

Simple example - without macros:


class AJSimpleRunBase extends RunBaseBatch
{
    date    simStartDate, simEndDate;
}

public container pack()

{
    //values stored needs to be returned to user
    
    //Simple
    return [simStartDate, simEndDate];
{

public boolean unpack(container packedValues)

{
    //Saves the user provided packedValues to the variable defined
    
    //Simple
    [simStartDate, simEndDate] = packedValues;
    return true;
}

Next level - adding additional parameter:

Say you need to add an additional parameter (isPosted) to the class. We can do so by adding a simple declaration in the classDeclaration() method and the pack() method. However, for the unpack() method, the previous stored values will become obsolete. So we need to introduce versioning and ensure that unpack() method understands which version of values it has to update. 

 class AJSimpleRunBase extends RunBaseBatch

{
    date    simStartDate, simEndDate;
    boolean isPosted;
}

public container pack()

{
    //values stored needs to be returned to user
    
    //Next level - versioning as new parameter added

    //return [1, simStartDate, simEndDate, isPosted]; //hardcoded version
}

public boolean unpack(container packedValues)

{
    //Saves the user provided packedValues to the variable defined
    
    //Next level - versioning as new parameter added
    boolean ret = true; 
    int version; 
    ;
    if (conPeek(packedValues,1) ==1) //hardcoded version
    {
        [version, simStartDate, simEndDate, isPosted] = packedValues;
    }
    else
    {
        ret=false;
    }
    return ret;
}


Using Macros - to ensure no-rework for versioning. 
If you want to add one more parameter to the class, you would have to repeat the same steps as shown above and then change the hard coded values. This is where, #macros come to the rescue. With #macros we would have to change only one spot when we change what we want to pack(). 

class AJSimpleRunBase extends RunBaseBatch

{
    date    simStartDate, simEndDate;
    boolean isPosted;
    
    //Macros, what they do is fill in exactly what the macro says at compile time. 
    //So whenever you write #CurrentVersion, the compiler fills in '1'.  
    //Whenever you write #CurrentList, the compiler fills in 'startDate, endDate, isPosted'
    #define.CurrentVersion(1)
    
    #localMacro.CurrentList
    simStartDate, 
    simEndDate, 
    isPosted
    #endMacro
}    

public container pack()

{
    //values stored needs to be returned to user
    
    //With Macros
    return [#CurrentVersion, #CurrentList];

}

public boolean unpack(container packedValues)

{
    //Saves the user provided packedValues to the variable defined
    
    //With Macros
    boolean ret = true;
    int version;
    ;
    if (conPeek(packedValues,1) == #CurrentVersion)
    {
        [version, #CurrentList] = packedValues;
    }
    else
    {
        ret=false;
    }
    return ret;

}

Hope this helps in better understanding of the pack() and unpack() methods and also some insights into #macros. 


Happy coding!

Wednesday, March 8, 2017

How to kill tasks on your machine

Sometimes only way to get out a stuck screen is to kill an on going process, if it has been executing for long, say, for example the "Application object server" is in "Starting" state for a pretty long time and you can not take any action from within the Services window. 

Always good to rely on the Windows Task manager (Right click on taskbar > Task manager) and under Services tab you can find the details of all the services available in the system. You can explore more around Processes and Details tab for further actions. 



And if Task manager is not able to help you in your situation. You can always rely on the good old command taskkill using the pid of the process to identify the correct service/process. 

Open command prompt (Run as Administrator)
taskkill /f /pid 4224


Happy coding!