Friday, 13 July 2018

How to Deploy Async Client thru PowerShell

If you want to install multiple Async Client Service in one Store Server, you must go thru PowerShell with below steps:

1.   Copy Async Client Folder Name and rename it, edit of the ss-settings-Updated.xml (If already Install One Async Service thru setup) file for each new instance of Async client that you are going to deploy.

2.   Edit your ss-settings-Updated.xml file in a text editor, such as Notepad. Change the parameter in the XML File and rename the file as service name. ex.(ss-settings-009)

3.   Copy edited file(ss-settings-009) into both folder default (async client Folder and your new copied async folder).

4.   Run PowerShell Command from async client Folder (Default)

---------------------------------------------Async Client Setting Xml---------------------------------------------------------------------
  <Setting key="InstallDatabase" value="true" />
  <Setting key="InstallSynchClient" value="true" />
  <Setting key="ChannelMessageDatabaseServer" value="ATKSTR" /> --------ASYNC SERVER ----
  <!-- Note: Format for a SQL instance name is either the server name or the full instance name. I.e. valid names are: "localhost", "localhost\instance2", "server1", "server1\instance2"-->
  <Setting key="ChannelMessageDatabaseServerNamedInstanceName" value="ATKSTR" />---ASYNC SERVER---
  <Setting key="ChannelMessageDatabaseName" value="ASYNCCLIENTDB" />--ASYNC SERVER MSGDB--
  <Setting key="ChannelDatabaseServer" value="ADC" />---------CHANNEL DATABASE SERVER--------
  <!-- Note: Format for a SQL instance name is either the server name or the full instance name. I.e. valid names are: "localhost", "localhost\instance2", "server1", "server1\instance2"-->
  <Setting key="ChannelDatabaseServerNamedInstanceName" value="ADC" />--CHANNEL DATABASE SERVER-
  <Setting key="ChannelDatabaseName" value="PRODRCT009" />----CHANNEL DATABASE-----
  <Setting key="ServiceName" value="CommerceDataExchangeAsyncClientService_PRODRCT009" /> --SERVICE NAME----
  <Setting key="ServiceDisplayName" value="Microsoft Dynamics AX for Retail Commerce Data Exchange Async Client:PRODRCT009" /> ------------SERVICE NAME-------------
  <Setting key="SynchServiceHeadOfficeURL" value="" /> ---ASYNC SERVER URL---
  <Setting key="ServiceUser" value="RCT\AXPRODAOS" /> --------ADMIN PASSWORD FOR SERVICE-------
  <Setting key="ServiceBinarySourceFolder" value="C:\Program Files (x86)\Microsoft Dynamics AX\60\CDX\Async Client-009\Package\" /> ---COPIED ASYNC CLIENT FOLDER FOR NEW SERVICE-------
  <Setting key="ServiceInstallFolder" value="C:\Program Files (x86)\Microsoft Dynamics AX\60\CDX\Async Client-009\Package\" />---COPIED ASYNC CLIENT FOLDER FOR NEW SERVICE-------
  <Setting key="ServiceExecutionFileName" value="AsyncClientService.exe" />
  <Setting key="DataStoreName" value="Rtextiles" /> -----CHANNEL DATABASE ID FROM AX--
  <Setting key="DataStoreUserName" value="rtextiles" /> -----CHANNEL DATABASE USER NAME FROM AX-
  <Setting key="UploadServiceName" value="UploadService.svc" />
  <Setting key="DownloadServiceName" value="DownloadService.svc" />
  <Setting key="StreamServiceName" value="StreamingService.svc" />
  <Setting key="CertificateValidationOff" value="false" />
  <Setting key="ChannelMessageDatabaseServerOverride" value="[ChannelMessageDatabaseServer]" />
  <Setting key="GenerateSQLiteDatabase" value="False" />
  <Setting key="SQLiteConverterConfigurationFile" value="SQLiteConverter.Config\RetailChannelDatabase\ChannelDbCreateSchemaAndCopyData.config" />

5.   Open the folder where the Windows PowerShell scripts are installed by default, Go to the at C:\Program Files (x86) \Microsoft Dynamics AX\60\CDX\ Async Server\Tools – Go to the File Click on Open Windows PowerShell --- Open Windows PowerShell as administrator.

6.   Run below command into the PowerShell window one by one

      I.     Set-ExecutionPolicy -Scope Process -ExecutionPolicy RemoteSigned

     II.     $Cred = @((New-Object System.Management.Automation.PSCredential('rct\axprodaos',(ConvertTo-SecureString 'password' -AsPlainText -Force))));

    III.     $Cred += @((New-Object System.Management.Automation.PSCredential('Rtextiles',(ConvertTo-SecureString 'rtextiles' -AsPlainText -Force))));

   IV.      $Cred += @((New-Object System.Management.Automation.PSCredential('rct\axprodaos',(ConvertTo-SecureString 'password' -AsPlainText -Force))));

     V.    .\DeployAsyncClient.ps1 -SettingsXmlFilePath “C:\Program Files (x86)\Microsoft Dynamics AX\60\CDX\Async Client\Tools\sc-settings-009.xml” -TopologyXmlFilePath “C:\Program Files (x86)\Microsoft Dynamics AX\60\CDX\Async Client\Tools\sc-topology.xml” -Credentials $Cred –Verbose $true

Friday, 6 July 2018

Command Line Querys DOS

1. Delete Microsoft .auc files from current user and Temp files
    Del /S "%LOCALAPPDATA%\*.auc " /q
    Del /s "%LOCALAPPDATA%\temp\*.*" /q  

Thursday, 5 July 2018

P- Jobs not Sync with ax without any error

P-Job's are not updating retails transaction's(with latest updated field values),If transactions are not pulling to store HQ we can do this to force the Channel Database into thinking that the records haven’t been uploaded to Async Server. If that table (crt.TABLEREPLICATIONLOG) was cleared out it would “reset” the channel database and it would re-upload all records into a new RPF file.  This should be OK since any duplicate transactions would be ignored by AX.

Follow this steps:

1. Stop the AsyncClient in the StoreDB machine
2. Make a backup of the Channel Database
3. Run Query into Channel Database
4. Delete  from  crt.TABLEREPLICATIONLOG
5. Start the Async Client
6. Run the P-job

Monday, 2 July 2018

How to check SQL / Windows Versions and Expire Date of Installed Copy

How to Check SQL Versions and Expire Date of Installed SQL Copy

If you want to know about your SQL Version which copy installed into your system then Run below Query into SQL Server
SELECT @@SERVERNAME [Server Name],CONNECTIONPROPERTY('local_net_address') AS [Server IP Address], SERVERPROPERTY('EDITION') 'Version', create_date AS 'SQL Server Installed Date',Expiry_date AS 'SQL Server Expiry Date', DATEDIFF(dd,create_date, GETDATE()) [No of Days Used],ABS(DATEDIFF(dd,expiry_date,GETDATE())) [No Of Days Left], GETDATE() [As On Date] From (SELECT sp.create_date,DATEADD(dd, 180, sp.create_date) AS Expiry_date From sys.server_principals sp  Where = 'NT AUTHORITY\SYSTEM') as exp_date_tbs

How to Check Windows Versions and Expire Date of Installed Windows Copy
If you want to know about your Windows Version which copy installed into your system then Run below command into RUN prompt (Windows + R)

View Activation, License, and Expiration Date Information

To display very basic license and activation information about the current system, run the following command. This command tells you the edition of Windows, part of the product key so you can identify it, and whether the system is activated.
slmgr.vbs /dli
To display more detailed license information–including the activation ID, installation ID, and other details–run the following command:
slmgr.vbs /dlv

View the License Expiration Date

To display the expiration date of the current license, run the following command. This is only useful for Windows system activated from an organization’s KMS server, as retail licenses and multiple activation keys result in a perpetual license that won’t expire. If you haven’t provided a product key at all, it’ll give you an error message.
slmgr.vbs /xpr

Uninstall the Product Key

You can remove the product key from your current Windows system with Slmgr. After you run the below command and restart your computer, the Windows system won’t have a product key and will be in an unactivated, unlicensed state.
If you installed Windows from a retail license and would like to use that license on another computer, this allows you to remove the license. It could also be useful if you’re giving that computer away to someone else. However, most Windows licenses are tied to the computer they came with–unless you purchased a boxed copy.
To remove uninstall the current product key, run the following command and then restart your computer:
slmgr.vbs /upk
Windows also stores the product key in the registry, as it’s sometimes necessary for the key to be in the registry when setting up the computer. If you’ve uninstalled the product key, you should run the below command to ensure it’s removed from the registry as well. This will ensure people who use the computer in the future can’t grab the product key.
Running this command alone won’t uninstall your product key. It’ll remove it from the registry so programs can’t access it from there, but your Windows system will remain licensed unless you run the above command to actually uninstall the product key. This option is really designed to prevent the key from being stolen by malware, if malware running on the current system gains access to the registry.
slmgr.vbs /cpky

Set or Change the Product Key

You can use slmgr.vbs to enter a new product key. If the Windows system already has a product key, using the below command will silently replace the old product key with the one you provide.
Run the following command to replace the product key, replacing #####-#####-#####-#####-##### with the product key. The command will check the product key you enter to ensure it’s valid before using it. Microsoft advises you restart the computer after running this command.
You can also change your product key from the Activation screen in the Settings app, but this command lets you do it from the command line.
slmgr.vbs /ipk #####-#####-#####-#####-#####

Activate Windows Online

To force Windows to attempt an online activation, run the following command. If you’re using a retail edition of Windows, this will force Windows to attempt online activation with Microsoft’s servers. If the system is set up to use a KMS activation server, it will instead attempt activation with the KMS server on the local network. This command can be useful if Windows didn’t activate due to a connection or server problem and you want to force it to retry.
slmgr.vbs /ato

Activate Windows Offline

Slmgr also allows you to perform an offline activation. To get an installation ID for offline activation, run the following command:
slmgr.vbs /dti
You’ll now need to get a a confirmation ID you can use to activate the system over the phone. Call the Microsoft Product Activation Center, provide the installation ID you received above, and you’ll be given an activation ID if everything checks out. This allows you to activate Windows systems without Internet connections.
To enter the confirmation ID you’ve received for offline activation, run the following command. Replace “ACTIVATIONID” with the activation ID you’ve received.
slmgr.vbs /atp ACTIVATIONID
Once you’re done, you can use the slmgr.vbs /dli or slmgr.vbs /dlv commands to confirm you’re activated.
This can generally be done from the Activation screen in the Settings app if your PC isn’t activated–you don’t have to use the command if you’d rather use the graphical interface.

Extend the Activation Timer

Some Windows systems provide a limited time where you can use them as free trials before entering a product key. For example, Windows 7 offers a 30-day trial period before it begins complaining at you. To extend this trial period and reset it back to 30 days remaining, you can use the following command.As Microsoft’s documentation puts it, this command “resets the activation timers.”
This command can only be used several times, so you can’t indefinitely extend the trial. The number of time it can be used depends on the “rearm count,” which you can view using the slmgr.vbs /dlv command. It seems different on different versions of Windows–it was three times on Windows 7, and it seems to be five times on Windows Server 2008 R2.
This no longer seems to work on Windows 10, which is very lenient if you don’t provide it a product key anyway. This option still works on older versions of Windows and may continue to work on other editions of Windows, such as Windows Server, in the future.
slmgr.vbs /rearm

Slmgr.vbs Can Perform Actions on Remote Computers, Too

Slmgr normally performs the actions you specify on the current computer. However, you can also remotely administer computers on your network if you have access to them. For example, the first command below applies to the current computer, while the second one will be run on a remote computer. You’ll just need the computer’s name, username, and password.
slmgr.vbs /option
slmgr.vbs computername username password /option

Thursday, 7 June 2018

Customization in DLL

Peripharal-->Printer DLL to Customised POS Receipt Width and Barcode Alignment

private void printDoc_BeginPrint(object sender, PrintEventArgs e)
            //Code to add Alignment Left Printing
            var printDocument = sender as PrintDocument;

            if (printDocument != null)
                printDocument.DefaultPageSettings.Margins.Left = 0;
                printDocument.DefaultPageSettings.Margins.Right = 0;
                printDocument.DefaultPageSettings.Margins.Top = 1;

            TextFontName = defaultFontName;
            TextFontSize = defaultFontSize;
            TextFontCharWidth = defaultFontCharWidth;
private void printDoc_PrintPage(object sender, PrintPageEventArgs e)
                e.HasMorePages = false;
                using (Font textFontRegular = new Font(TextFontName, TextFontSize, FontStyle.Bold))//FontStyle.Regular
                    float xCaretPos = 0, yCaretPos = 0;
                    float dpiXRatio = e.Graphics.DpiX / 96f; // 96dpi = 100%
                    float dpiYRatio = e.Graphics.DpiY / 96f; // 96dpi = 100%
                    float contentWidth = printText.Max(str => str.Replace(NORMAL_TEXT_MARKER, string.Empty).Replace(BOLD_TEXT_MARKER, string.Empty).Replace(DOUBLESIZE_TEXT_MARKER, string.Empty).Replace(DOUBLESIZE_BOLD_TEXT_MARKER, string.Empty).Length) * dpiXRatio; // Line with max length = content width

                    while (this.printTextLine < printText.Length)
                        string printingLine;
                        var heightStep = IsStringContainAnyOfMarkers(printText[this.printTextLine], DOUBLESIZE_TEXT_MARKER, DOUBLESIZE_BOLD_TEXT_MARKER) ? 2 * defaultLineHeight : defaultLineHeight;

                        if (yCaretPos + heightStep >= e.MarginBounds.Height)
                        {   // No more room - advance to next page
                            e.HasMorePages = true;

                        if (IsStringContainAnyOfMarkers(printText[this.printTextLine], BOLD_TEXT_MARKER, DOUBLESIZE_TEXT_MARKER, DOUBLESIZE_BOLD_TEXT_MARKER))
                            // Text line printing with bold or double size Text in it.
                            xCaretPos = 0;

                            printingLine = printText[this.printTextLine];
                            while (printingLine.Length > 0)
                                if (IsStringContainAnyOfMarkers(printingLine, BOLD_TEXT_MARKER, DOUBLESIZE_TEXT_MARKER, DOUBLESIZE_BOLD_TEXT_MARKER))
                                    string firstTextMarker = GetFirstTextMarker(printingLine, NORMAL_TEXT_MARKER, BOLD_TEXT_MARKER,
                                        DOUBLESIZE_TEXT_MARKER, DOUBLESIZE_BOLD_TEXT_MARKER);

                                    using (var textFontForPrint = CreateFontForMarker(firstTextMarker, TextFontName, TextFontSize))
                                        int firstMarkerIndex = printingLine.IndexOf(firstTextMarker);
                                        printingLine = printingLine.Remove(firstMarkerIndex, textMarkerWidth);
                                        string textToPrint = printingLine.Substring(0, printingLine.IndexOf(NORMAL_TEXT_MARKER));

                                        string textBeforeFirstMarker = textToPrint.Substring(0, firstMarkerIndex);
                                        e.Graphics.DrawString(textBeforeFirstMarker, textFontRegular, Brushes.Black,
                                            xCaretPos + e.MarginBounds.Left, yCaretPos + e.MarginBounds.Top);
                                        xCaretPos += textBeforeFirstMarker.Length * TextFontCharWidth;

                                            textFontForPrint, Brushes.Black, xCaretPos + e.MarginBounds.Left, yCaretPos + e.MarginBounds.Top);
                                        xCaretPos += textToPrint.Substring(firstMarkerIndex).Length * TextFontCharWidth;

                                        if (new[] { DOUBLESIZE_TEXT_MARKER, DOUBLESIZE_BOLD_TEXT_MARKER, BOLD_TEXT_MARKER }.Contains(firstTextMarker))
                                            printingLine = printingLine.Insert(printingLine.IndexOf(NORMAL_TEXT_MARKER) == -1 ?
                                                0 : printingLine.IndexOf(NORMAL_TEXT_MARKER) + NORMAL_TEXT_MARKER.Length,
                                                new string(' ', textToPrint.Substring(firstMarkerIndex).Length));

                                        printingLine = printingLine.Substring(printingLine.IndexOf(NORMAL_TEXT_MARKER) == -1 ?
                                            0 : printingLine.IndexOf(NORMAL_TEXT_MARKER) + NORMAL_TEXT_MARKER.Length);
                                    printingLine = printingLine.Replace(NORMAL_TEXT_MARKER, string.Empty);
                                    e.Graphics.DrawString(printingLine, textFontRegular, Brushes.Black, xCaretPos + e.MarginBounds.Left, yCaretPos + e.MarginBounds.Top);
                                    printingLine = string.Empty;
                            // Text line printing with no bold Text in it.

                            printingLine = printText[this.printTextLine].Replace(NORMAL_TEXT_MARKER, string.Empty);

                            Match barCodeMarkerMatch = Regex.Match(printingLine, barCodeRegEx, RegexOptions.Compiled | RegexOptions.IgnoreCase);

                            if (barCodeMarkerMatch.Success)
                                // Get the receiptId
                                printingLine = barCodeMarkerMatch.Groups[1].ToString();

                                using (Image barcodeImage = barCode.Create(printingLine, e.Graphics.DpiX, e.Graphics.DpiY))
                                    if (barcodeImage != null)
                                        float barcodeHeight = (barcodeImage.Height / dpiYRatio);

                                        if (yCaretPos + barcodeHeight >= e.MarginBounds.Height)
                                        {   // No more room - advance to next page
                                            e.HasMorePages = true;
                                        // Code Added by Vijay
                                        // Render barcode in the center of receipt.
                                        //commented to give 80 margin on left
                                        //e.Graphics.DrawImage(barcodeImage, ((contentWidth - (barcodeImage.Width / dpiXRatio)) / 2) + e.MarginBounds.Left, yCaretPos + e.MarginBounds.Top);
                                        e.Graphics.DrawImage(barcodeImage, ((contentWidth - (barcodeImage.Width / dpiXRatio)) / 2) + 80, yCaretPos + e.MarginBounds.Top);
                                        yCaretPos += barcodeHeight;
                                e.Graphics.DrawString(printingLine, textFontRegular, Brushes.Black, e.MarginBounds.Left, yCaretPos + e.MarginBounds.Top);
                        yCaretPos = yCaretPos + heightStep;

                        printTextLine += 1;

                    } // of while()
                } // of using()
            } // of try
            catch (Exception ex)
                NetTracer.Warning("Peripheral [Printer] - Exception in print page");

                ApplicationExceptionHandler.HandleException(this.ToString(), ex);
EOD-->Batch Printing DLL to customised Z Report and X Report Width

private static void AppendReportHeaderLine(this StringBuilder stringBuilder, int titleResourceId, string value, bool firstPart)

            // int partWidth = (paperWidth / 2);
            int partWidth = (paperWidth / 3);//Code add by Vijay
            //  int titleWidth = (int)(partWidth * 0.5);
            int titleWidth = (int)(partWidth * 0.2);//Code add by Vijay
            // int valueWidth = (int)(partWidth * 0.4);
            int valueWidth = (int)(partWidth * 0.2);//Code add by Vijay

            string title = ApplicationLocalizer.Language.Translate(titleResourceId);
            string line = string.Format(lineFormat, title.PadRight(titleWidth), value.PadLeft(valueWidth));

            if (firstPart)
private static void AppendReportLine(this StringBuilder stringBuilder, string title, object value)
            //stringBuilder.AppendLine(string.Format(lineFormat, title, value.ToString().PadLeft(paperWidth - title.Length - 2)));
            stringBuilder.AppendLine(string.Format(lineFormat, title, value.ToString().PadLeft(paperWidth - title.Length - 15)));//Code add for Width and length

Thursday, 24 May 2018

Error Retail Statement Posting InventTransTMPOriginKeyValueTransfer

Error in Retail Statement Posting

Error Messages

Cannot create a record in Transfer of the InventTrans origin foreign key value (InventTransTMPOriginKeyValueTransfer). Inventory transactions originator: 1001/409666, 1001/409666. The record already exists.

Microsoft.Dynamics.Ax.Xpp.ErrorException: Exception of type Microsoft.Dynamics.Ax.Xpp.ErrorException' was thrown. at Dynamics.Ax.Application.RetailStatementPostSalesOrders.Run() in 129
at Dynamics.Ax.Application.BatchRun.runJobStaticCode(Int64 batchId) in BatchRun.runJobStaticCode.xpp:line 54
at Dynamics.Ax.Application.BatchRun.runJobStatic(Int64 batchId) in BatchRun.runJobStatic.xpp:line 13
at BatchRun::runJobStatic(Object[] )
at Microsoft.Dynamics.Ax.Xpp.ReflectionCallHelper.MakeStaticCall(Type type, String MethodName, Object[] parameters) at BatchIL.taskThreadEntry(Object threadArg)


If you are getting error in Retail Statement posting and you are using Indian GST tax, then you are getting this error due to change HSN code into item master and push data into store database during the mid of the transaction, (Ex. Item A having HSN Code '123456' and you changed HSN Code '78910' on same day before shift close) then you will face this problem at the time of statement posting.

Identify your (Item,HSN Code and Transaction Time) from AX database RetailTransactionSaleTrans Table and break Statement into the part then post.

SQL Query

Create Function [Dbo].[Vijaysharma]  (@Timeinsec Varchar(Max))  
Returns Varchar(Max)  
Declare @Op Varchar(Max)  
Set @Op = ''  
Select @Op=Right('0' + Cast(@Timeinsec / 3600 As Varchar),2) + ':' +  
Right('0' + Cast((@Timeinsec / 60) % 60 As Varchar),2) + ':' +  
Right('0' + Cast(@Timeinsec % 60 As Varchar),2)  
Return @Op  
----------------------------Find Duplicate Records-------------------------------------------------------
Select Itemid, Concat(Hsncode_In,Itemid) As Hsnitem Into #Vijay From Retailtransactionsalestrans Where Transdate='2018-06-06'
Select Distinct Hsnitem,Itemid Into ##Vijay From #Vijay 
Select Itemid, Count(1) As Duplicate Into ###Vijay From  ##Vijay Group By Itemid Having  Count(1) > 1;
Select Rt.Hsncode_In,Rt.Itemid,Rt.Transdate,Rt.Transtime Into ####Vijay From  Retailtransactionsalestrans As Rt  Join  ###Vijay As Vi On Vi.Itemid=Rt.Itemid Where Rt.Transdate='2018-06-06' Order By Itemid
Drop Table #Vijay ,##Vijay ,###Vijay
Select distinct ITEMID from ####Vijay order by Itemid
Select * from ####Vijay where itemid='481221A001001'order by TRANSTIME
Select [Dbo].[Vijaysharma]('76235') As Transtime 
------------Delete Temp Tables------------------------------------------------------------------------------
Drop Table ####Vijay
Drop Function Vijaysharma

Excel Sheet

Select Hsncode_in,Itemid from RetailtransactionSalesTrans where Transdate='2018-05-22'
Copy these records from the SQL Tables and paste into the excel sheet, Insert one more column and Put formula =concate(B1,C2)

Go to the Menu Data tab and Remove Duplicate from the concate column, after that select ITEMID column, go to the menu Home Tab click on Conditional Formatting--Highlight Cells Rules--Click on Duplicate Values, Select Duplicate with color. you will find duplicate values. You can check transaction time of that particular item and can calculate(Split) your statement accordingly.

Friday, 18 May 2018

Error in Sync Job 1130 and 1040 Product Not Sync

Error in Sync Product N-1040 and N-1130 Product Availability
("Process Target Request Header Failed Due to An Invalid Operation Exception")

When running the sync job from Distribution Schedule, we came across this error - "System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.".

Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException: ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString: Data Source=CDB;Initial Catalog=RetailBostanciTestStore;Integrated Security=True;Connect Timeout=60;Application Name="Commerce Data Exchange Async Client" ---> System.InvalidOperationException: The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. ---> System.InvalidOperationException: String or binary data would be truncated.

  1. Normally this error comes due to mismatch both databases (AX and Channel DB), We need to Identify which table and which column has changed.
  2. Database Version Mismatch like any CU update or Retail Channel Utility  or Channel database not updated.
I found two tables which is having issue


Run Query Into SQL on Channel Database 

Alter Table ECORESPRODUCTTRANSLATION  Alter Column Name Nvarchar (90) Not Null
Alter Table INVENTDIM Alter Column WMSLOCATIONID Nvarchar (15) Not Null