Microsoft Dynamics NAV

Team Blog

  • Microsoft Dynamics NAV Team Blog

    Coffee Break - Windows PowerShell Remoting

    • 3 Comments

    In this previous blog post we looked briefly at Windows PowerShell and remoting. Back then, we used a PSSession object to connect to a Microsoft Dynamics NAV virtual machine in Microsoft Azure. This time we will look a little closer at just the PSSession object.

    Coffee Break 8 - Windows PowerShell Remoting (On-Premises)

    Taking a closer look at ways to run PowerShell on remote machines on your network.

    But first, if you think you need more study to start with this, then please check out this free Professional Education online course, offered by Microsoft and EdX:
    Windows PowerShell Fundamentals

    Customer Story:

    The customer's IT administrator want to remotely check Microsoft Dynamics NAV Server instances on their network. And they want to be able to script tasks and run those scripts from their laptop. So connecting to each (NAV) server with RDC is not an option. In fact now we are at it, why not remote from your mobile phone?

    Basic installation guide for Windows PowerShell Web Access

    How to do PowerShell on your phone

    Customer Story II (The Sequel):

    A problem of some kind has occurred on one of the installations and services need to get restarted/updated/tenants mounted. The customer's IT administrator is in a traffic jam. A long phone call with instructions on what to press or not is one option. But PowerShell web access from the mobile, and simple web-run remote management while still stuck in that traffic jam might be a better one. More about this to come soon!

    Challenge:

    Next time you need to restart a NAV Server, change a config value or do any other (NAV) admin task that you can do with PowerShell, try to do it without actually connecting (RDC) to the machine. Do it via a PowerShell prompt on your machine.

     

    PowerShell Remoting:

    Imagine that you are sitting with your laptop, and from there you need to access (NAV) services on various different machines on your domain.

    Option 1: -Computername
    Many cmdlets implement the -Computername parameter, for example:

    $RemoteServer = "MyServerName"
    Get-Service -ComputerName $RemoteServer # shows you Windows services on [MyServerName]

    This is the simplest way to run a cmdlet remotely. But there is no general rule that every cmdlet must implement the -ComputerName parameter or if they do, then how.

     

    Option 2: Invoke-Command
    Invoke-Command is a cmdlet that will send whatever command to the remote machine. Example:
      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{get-service}
    or run anything that you are used to run from a cmd prompt
      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{ipconfig}
    or

      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{dir C:\}
    or even (though a bit pointless)
      Invoke-Command -ComputerName $RemoteServer -ScriptBlock{calc}

    If you want to prompt for credentials on the remote machine:

      Invoke-Command {Get-Service} -computername $RemoteServer -Credential Get-Credential

    Invoke-Command creates a new session, so to run a NAV cmdlet we first need to import the NAV management module and then run our NAV cmdlet. Each command separated by semi-colon:
      Invoke-Command -ComputerName $RemoteServer `
       -ScriptBlock {Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll' `
       ; Get-NAVServerInstance | Format-Table -AutoSize}

     Note that in this example you do not need to have NAV on the machine you are running from. The only cmdlet that runs on your machine is Invoke-Command.

    You can also run a script remotely. If you first create a script like this:

      Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'
      Get-NAVServerInstance | Format-Table -AutoSize

    Save it as c:\Tech\GetNAVServers.ps1

    Then run it:

      Invoke-Command -ComputerName $RemoteServer -FilePath{C:\Tech\GetNAVServers.ps1}

    Notice that the script is saved locally on your machine, but it runs on the remote machine.

    We can add parameters to this script by adding -ArgumentList Arg1, Arg2, Argn. Let's make a small addition to the script by adding a Microsoft Dynamics NAV Server instance name as a parameter:

      Param($NAVInstanceName)
      Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'
      Get-NAVServerInstance $NAVInstanceName | Format-Table -AutoSize

    (if you want more than one parameter, just comma-separate them like this Param($NAVInstanceName, $Tenant, $UserName, ... etc)

    Try to just run the script (locally):

      CD C:\Tech\

      .\GetNAVServers.ps1 -NAVInstanceName DynamicsNAV80

    And remotely:

      Invoke-Command -ComputerName $RemoteServer -FilePath{C:\Tech\GetNAVServers.ps1} -ArgumentList Dynamicsnav80

     

    Option 3: PSSession
    As mentioned before, Invoke-Command will create a new session. But we can also create a new session separately, and then re-use this session. Why?

    •   It takes time to open a session, so we can potentially save some time by re-using one session.
    •   The session is persistent (keeps variable values), which could also be of use.

    Creating a remote session of course requires authentication. As long as we are remoting to a machine on our domain then Windows will take care of this. Creating a remote session to a machine in the cloud requires certificate. For this, see the previous blog (linked in the top of this one). But here we just want to open session(s) on our own network. But notice that once you have your PSSession object, then the rest of the syntax is exactly the same, whether the session is to a machine under your desk, or to a machine in an Azure data centre. Create a session to a machine ($RemoteServer) on your network could not be easier:

      $MySession = New-PSSession $RemoteServer

    Now you can (re) use this session with Invoke-Command. So in the examples above, just use -Session parameter instead of -ComputerName:
      Invoke-Command -Session $MySession -ScriptBlock{get-service}

    The next example will show what it means that the session is persistent:
      Invoke-Command -Session $MySession -ScriptBlock{$i++;$i}

    Run that one a few times, and see that $i increments every time.

    Or you can enter a remote manual session:

      Enter-pssession $MySession

    Notice that the first part of the prompt changes to show you that the prompt is now running on the remote machine. To leave this machine and return PowerShell to your local machine, run EXIT.

    Troubleshooting:

    On some (client) OS, PowerShell remoting is not enabled by default. PowerShell remoting runs under a service called WinRM. To test it:

    Test-connection -ComputerName $RemoteServer
    Test-wsman -ComputerName $RemoteServer

    And as also mentioned by Pallea in comments to the blog linked above (thanks!), if remoting is not enabled you can enable it in this way:

    Enable-PSRemoting

     

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 24 for Microsoft Dynamics NAV 2013 has been released

    • 6 Comments

    Cumulative Update 24 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013.

    The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions: 

    • AU - Australia
    • AT - Austria
    • BE - Belgium
    • CH - Switzerland
    • DE - Germany
    • DK - Denmark
    • ES - Spain
    • FI - Finland
    • FR - France
    • IS - Iceland
    • IT - Italy
    • NA - North America
    • NL - Netherlands
    • NO - Norway
    • NZ - New Zealand
    • SE - Sweden
    • UK - United Kingdom

    Where to find Cumulative Update 24

    You can download the cumulative update from KB 3039828 - Cumulative Update 24 for Microsoft Dynamics NAV 2013 (Build 40118).

    Additional Information

    For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2013

    For a list of all hotfixes included in cumulative updates for this version, see the following CustomerSource and PartnerSource pages:

    CustomerSource:

    PartnerSource:

  • Microsoft Dynamics NAV Team Blog

    A Couple of RDLC Performance Optimization Tips

    • 5 Comments

    We have received several requests related to optimization tips for the Microsoft Dynamics NAV RDLC report experience. So in this blog post we provide you with a couple of tips for how you can refactor a report in order to increase its performance in terms of dataset reduction - the example is based on the standard report 4 “Detailed Trail Balance” from Microsoft Dynamics NAV 2015 Cumulative Update 4 German localized DVD version (DEU).

    NOTE: This approach is valid if you are crystal clear on the intrinsic technical limitations when printing client-side as was widely discussed and dissected in this blog post from March 2014, and it is intended to provide a general guidance as-is in the form of a proof of concept.

    TIP #1 : Create a header for constant and static values

    This is an old trick (described also in books and other collateral since Microsoft Dynamics NAV 2009), and it consists of pushing static values such as e.g. COMPANYNAME or TABLECAPTION in a DataItem based on an “Integer” record (virtual table) that will be repeated only one time (MaxIteration DataItem property needs to be set to 1).

    The “Integer” DataItem must be the very first DataItem in the report so that while refactoring the report in Visual Studio, developers could always refer to this by using the =First( statement in VB.NET.

    The great advantage is to avoid costly redundancy for static values (e.g. COMPANYNAME) all along the dataset since only the first record in the column is populated. This will reduce the dataset dimension, in terms of data quality, and consequently obtain a faster load of the dataset client side and raising higher the out of memory exception bar (the application is able to process more rows). 

    In this example, standard Report 4 has been refactored and 8 columns has been moved as part of the Header “Integer” DataItem:

    Data Source Name
    COMPANYNAME   CompanyName 
    ExcludeBalanceOnly    ExcludeBalanceOnly
    PrintReversedEntries  PrintReversedEntries
    PrintClosingEntries   PrintClosingEntries
    PrintOnlyCorrections  PrintOnlyCorrections
    GLFilter    GLFilter
    STRSUBSTNO(Text000,GLDateFilter)  PeriodGLDtFilter
    GLAcc.TABLECAPTION + ': ' + GLFilter   GLAccTableCaption

    TIP #2 : Use labels

    Multianguage (ML) labels are pushed to Report Viewer at runtime outside the dataset. This happens when the report is run, such as when there is code such as this:

    CurrReport.LANGUAGE := Language.GetLanguageID("Language Code");

    inside the DataItems (typically OnAfterGetRecord triggers). This does not have any influence in the label items since they are already loaded by Report Viewer as constant value in the Parameters read-only collection.

    Luckily, this is not always the case and we could use ML labels as constant values instead of redundant and repeated values added as Columns in the DataItems. Since labels are not part of the dataset, this will be reduced in the number of columns improving performance in loading the dataset client-side and raising higher the out of memory exception bar (the application is able to process more rows). 

    In this example, standard Report 4 has been refactored and 9 columns has been deleted and values added as ML Labels:

    Name

    DetailTrialBalCaptionLbl

    PageCaptionLbl

    BalanceCaptionLbl

    PeriodCaptionLbl

    NetChangeCaptionLbl

    GLEntryDebitAmtCaptionLbl

    GLEntryCreditAmtCaptionLbl

    TIP #3 : Refactor decimals and use FORMAT(decVar) where and if possible

    This is tricky. Microsoft Dynamics NAV RDLC decimal values are pushed to the dataset together with their formatting. This means that the dataset will have a column that store the value and another one that store the value format. This is costly since it will always add an extra column for every decimal value in the dataset.

    In some cases, you can transform the decimal to its equivalent string by applying the C/AL Statement FORMAT(decVar,0,1) in the Microsoft Dynamics NAV development environment, and then use a ML label for all the decimal value format (or several ML labels if there are different decimal formatting inside the report). In Visual Studio, remember to transform back the string into a valid decimal value using the VB .NET =CDec( function.

    In this example, the standard report 4 has been refactored, and 5 columns containing the same formatting clause has been silently deleted (not created):

    Data Source Name
    FORMAT(StartBalance,0,1)   StartBalance 
    FORMAT("VAT Amount",0,1)   VATAmount_GLEntry
    FORMAT("Debit Amount",0,1)    DebitAmount_GLEntry
    FORMAT("Credit Amount",0,1)   CreditAmount_GLEntry
    FORMAT(GLBalance,0,1)  GLBalance 

    And a ML Label has been created and referenced in Visual Studio where needed:

    DecimalFormat              #,##0.00

     

    OUTCOME and STATISTICS
    Report PRE   37 Columns
    Report POST   22 Columns (8 Header columns)
    Gain   Dataset reduced by 41 % (in quality – header – by 62 %)

    We have attached our version of report no. 4 as a text file so you can more easily compare this to your own version.

    These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.

    Duilio Tacconi Microsoft Dynamics Italy
    Alessandra Pandini Microsoft Dynamics Italy

     

    Microsoft Customer Service and Support (CSS) EMEA

  • Microsoft Dynamics NAV Team Blog

    Cumulative Update 17 for Microsoft Dynamics NAV 2013 R2 has been released

    • 34 Comments

    Cumulative Update 17 includes all application and platform hotfixes and regulatory features that have been released for Microsoft Dynamics NAV 2013 R2.

     The cumulative update includes hotfixes that apply to all countries and hotfixes specific to the following local versions:

    •   AU - Australia
    •   AT - Austria
    •   BE - Belgium
    •   CH – Switzerland
    •   CZ – Czech Republic
    •   DE - Germany
    •   DK - Denmark
    •   ES - Spain
    •   FI  - Finland
    •   FR - France
    •   IS - Iceland
    •   IT - Italy
    •   NA - North America
    •   NL - Netherlands
    •   NO - Norway
    •   NZ - New Zealand
    •   RU – Russia
    •   SE - Sweden
    •   UK - United Kingdom

    Where to find Cumulative Update 17

    You can download the cumulative update from KB 3039826  – Cumulative Update 17 for Microsoft Dynamics NAV 2013 R2 (Build 40076). 

    Additional Information

    For more information about cumulative updates for this version, see Announcement of update rollups for Microsoft Dynamics NAV 2013 R2.

    For a list of all cumulative updates for this version, see Released Cumulative Updates for Microsoft Dynamics NAV 2013 R2

    For a list of all hotfixes included in cumulative updates for this version, see the following CustomerSource and PartnerSource pages:

    CustomerSource:

    PartnerSource

  • Microsoft Dynamics NAV Team Blog

    The Multi-File Download Cookbook

    • 0 Comments

    Today's pattern is not a pattern but a "cookbook" that shows how to use the FileManagement library to download multiple files as a .zip file through Microsoft Dynamics NAV in a way that works on all types of clients.

    Abstract

    The goal of this pattern is to enable the users to download multiple files as a zip file instead of downloading one by one.  On the Microsoft Dynamics NAV Web client, this is the preferred way of delivering multiple files since it is one of the web patterns and we cannot use the File Management codeunit to place files silently on the machine.

    Description

    When generating reports that consists of multiple, and usually an unknown number of files, the developer will have to handle the download which also depends on the client the user is on. The problem is that the Microsoft Dynamics NAV Windows client has access to the user’s file system, whereas the web client does not. Following web guidelines, and the fact that client-side .NET is not available in Web client, you can’t initiate multiple downloads which requires the developer respond to the type of client. In some browsers it is possible to download files one-by-one in the Web client by using a confirm dialog, however this is a hack and should not be used.

    To solve this problem, a generic download mechanism is used that is client dependent event when multiple files need to be downloaded. For the Web client, the files are compressed using ZIP, and for the Windows client the files are downloaded directly to the file system.

    The pattern is usable for all objects that output multiple files and is available in both Windows client and Web client.

    Usage

    The pattern consists of two steps: 1) Gathering the files and 2) downloading the file(s)

    For first step consists of a loop that goes through the files that needs to be downloaded. If it is on the Web client, the files are added to a ZIP archive server-side using a naming convention defined by the integration function GetSeriesFileName. This function takes a filename and number, and transforms it to unique names following a meaningful deterministic pattern e.g. prepend an integer before the file extension. The same function is used when the temporary files are created server side, so the files can be found deterministically later. This removes the need for storing filenames and consequently allows an arbitrary number of files. The second integration function: GetTotalNumberOfFiles, returns the total number of files generated during the data processing and makes the pattern able to handle an arbitrary number of files.

    The second step is the actually download of file(s). For the Web client this consists of closing the ZIP achieve and downloading via the standard download handler that works in the Web client. For the Win client, the files are saved directly to the client during the first step.

    Code 1: File loop shows an example implementation of this pattern. ServerFileName is generated at the beginning of the report/codeunit, and is the base for GetSeriesFilename. The file that is actually written to during data processing is stored in another variable which holds the output from GetSeriesFilename on the current file number. Note; the example code will only create a ZIP file if there in fact are multiple files to be downloaded. 

    Read more on the NAV Design Patterns Wiki....

    Best regards,

    Martin Dam at Microsoft Development Center Copenhagen

  • Microsoft Dynamics NAV Team Blog

    Coffee Break - Creating and Using Windows PowerShell Profiles

    • 1 Comments

    When you open a Windows PowerShell command prompt, often you type the same commands to begin with. This post shows how to create a profile that will run the commands you want every time you start a new PowerShell command prompt.

    Coffee Break 7 - Creating and using Windows PowerShell profiles

    User Story

    Developer wants to automate importing of  Microsoft Dynamics NAV modules along with other often used scripts into the Windows PowerShell ISE environment, for simplicity and reliability. For this, a PowerShell profile can be used. A PowerShell Profile is a start-up script that runs when PowerShell starts. Here we can import modules , add snap-ins, run functions, etc. Things that will help to initialize and build up the work environment.

    $profile

    A profile path is contained in the automatically created variable $profile. Executing $profile in PowerShell will show the path to your existing profile, or path to where one would be created. It will look like this when run from the ISE environment: C:\Users\[user]\Documents\WindowsPowerShell\Microsoft.PowerShellISE_profile.ps1.

    To check if you already have a profile created, run:

    Test-Path $profile

    If the profile doesn’t exist, return value will be False. To create the profile you can run this:

    New-Item -path $profile -type fileforce
    (notice using –force to overwrite existing profile).

    The above will simply create a blank profile file (script). One can modify the script directly from the file explorer, and or by using

    Notepad $profile

     

    #Here we can for example import modules used  for NAV management.

    Import-module "C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll"

    #And any other modules we might use frequently (for ex. Azure module - requires having installed this in advance)

    Import-module Azure

    #Set prompt to be descriptive

    function Prompt { "NAV71 $(Get-Location)>" }

    # We can set background to our preferences, change default path, etc….

    Set-location C:\MyWorkEnv\
    $host.UI.RawUI.BackgroundColor = “DarkRed”; $Host.UI.RawUI.ForegroundColor = “Gray

    For complete list of colors that can be set using host, check this link: ConsoleColor Enumeration

    Note: Some modules get loaded automatically when PowerShell starts, even without Import-Module. This happens when a module is added to system folder %PSModulePath% at installation time. All modules that are in %PSModulePath% are loaded automatically whenever any of their commands are used. 

    Different profiles

    There are 4 possible profiles for a host:

    • All Users, All Hosts,
    • All Users, Current Host,
    • Current User, All Hosts,
    • Current User, Current Host

    Where the bottommost one has the highest priority. You can store different profiles for different scopes. To view the path of each, run each of the following commands:

    • $Profile.AllUsersAllHosts
    • $Profile.AllUsersCurrentHost
    • $Profile.CurrentUserAllHosts
    • $Profile.CurrentUserCurrentHost

     

    Note also that a Windows PowerShell profile is  just another ps script and like any other ps script is not exempt from execution policy. The Execution Policy in PowerShell determines which scripts can be run and which cannot. By default, no scripts can be run, including Profiles (Restricted Execution Policy). This can be changed (to All Signed, Remote Signed, Unrestricted). Note that the change is saved in the registry and only needs to be changed once per computer.

    Handling multiple versions of Microsoft Dynamics NAV on the same machine

    Finally, you might want to work with several Dynamics NAV versions in parallel (on the same machine). Working with each version requires it’s respective Dynamics NAV management module version to be loaded. The PS modules are .net assemblies which will load it into the AppDomain of the PowerShell Host (the application). Remove module cmdlet will just remove the module from  the current session. For more details, see:
    http://msdn.microsoft.com/en-us/library/ms173101(v=vs.80).aspx

    In short – once we have imported a module (dll) it will remain in the current session. So to work with parallel NAV versions, a session per version is needed. Instead of one common profile, a startup-script dedicated to each session can be used to initialize each NAV version environment.

    So to create an ISE environment for each Dynamics NAV version, we can open ISE with a startup script to import Dynamics NAV modules for the relevant version of Microsoft Dynamics NAV. For example, create a script file called MyNAV2013Env.ps1 and save it locally. The file could for example look like following:

     

    import-module "C:\Program Files\Microsoft Dynamics NAV\71\Service\Microsoft.Dynamics.Nav.Management.dll"  
    Set-Location C:\tech\MyNav2013Env\

    #here we can (for ex.) add different coloring for different version, to avoid any confusion about what NAV version we’re working with
    $host.UI.RawUI.BackgroundColor = “DarkRed”; $Host.UI.RawUI.ForegroundColor = “Gray”

    #the below will clear the screen
    Clear-Host

    # Welcome message
    Write-Host "Welcome to NAV 2013R2 Powershell: " + $env:Username
    Write-Host "Dynamics NAV version 2013 R2 module imported"

     

    Save the file and create a shortcut to the Windows PowerShell ISE on the desktop. Modify shortcut properties to set
    Target: C:\Windows\System32\WindowsPowerShell\v1.0\powershell_ise.exe -File "C:\mydocs\ MyNAV2013Env.ps1"
    Set shortcut name to NAV2013R2_ISE. Suggestion: Tick "Run as Administrator" on the shortcut as well.

    When doubleclicking either of the shortcuts an ISE environment will open with the MyNAV2013Env file.
    Press F5 to execute the file and set up environment, then Ctrl+N (or File-New) to open a new script file to work with.

    Repeat the above for Microsoft Dynamics NAV 2015 (modify the file name, module path , colors, welcome message and target path accordingly). And in this way have one shortcut for each version of Microsoft Dynamics NAV on your machine.

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    Coffee Break | More piping with Dynamics NAV

    • 5 Comments

     Did you see our first coffee break about piping at Windows PowerShell and Piping? Let's dig a bit further.

    Coffee Break 6 - Return to piping

    This time we will use more piping and other ways to look at a PowerShell object and format it in different ways. For the example here we will use Get-NAVServerInstance and the results from that cmdlet. But everything in this post would apply in the same way on other cmdlets, like

    Get-NAVServerUser
    Get-NAVWebService
    Get-Process
    Get-Service

     

    Change how data is displayed

    Import the NAV management module so we can use the NAV cmdlets

    import-module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'

    Run the following commands and check how the result differs:
    Get-NAVServerInstance
    Get-NAVServerInstance | Format-Table
    Get-NAVServerInstance | Format-Table -AutoSize

     

    Select parameters:

    Select which columns to return

    Get-NAVServerInstance | Select-Object ServerInstance, State

    But... How do you know which columns are available? Simply pipe the cmdlet into Get-Member:

    Get-NavServerInstance | Get-Member

    This shows you a list of members, including these properties

    Default                                                                                                      
    DisplayName                                                                                                 
    ServerInstance                                                                                              
    ServiceAccount                                                                                            
    State                                                                                                    
    Version

    Formatting Output

    The most usual formats are list and table. Confusingly to a Dynamics NAV person, Format-List is like a card display, and Format-Table is just like a list. Run these to see the difference:
    Get-NAVServerInstance | Select-Object ServerInstance, State | Format-List
    Get-NAVServerInstance | Select-Object ServerInstance, State | Format-Table

    Some of the most useful other formats (to replace the last bit of the pipe above):

    Group-Object State
    Sort-Object State
    ConvertTo-Html
    Export-Csv -Path c:\x\servers.txt
    Out-gridview
    Clip

    Especially Clip is very useful - it sends the result directly to your clipboard so you can paste it into Notepad or somewhere else.

    Note that formatting pipes may destroy the object in order to display it, so always do the formatting as the last part of a pipe. Except if you want to follow it by an Out-cmdlet.

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

  • Microsoft Dynamics NAV Team Blog

    The AMC Bank Data Conversion Service for Microsoft Dynamics NAV 2015 now supports 35 additional banks

    • 0 Comments

    Based on requests from customers and partners, the AMC Bank Data Conversion Service for Microsoft Dynamics NAV 2015 has just released support for 35 new banks worldwide. To see the new banks being supported, please go to the service sign up page here where you can also find more information on the service.

    If your bank isn’t supported today, please place your vote here and influence which banks the AMC Bank Data Conversion Service should add support for next.

  • Microsoft Dynamics NAV Team Blog

    Application Design Pattern: SELECT DISTINCT with Queries

    • 2 Comments

    This pattern explains how to perform SELECT DISTINCT by using queries in Microsoft Dynamics NAV. It is brought to you by Bogdana Botez from the Dynamics NAV team here at Microsoft Development Center Copenhagen (MDCC).

    When you work with tables, sometimes you must perform a SELECT DISTINCT (also known as SELECT UNIQUE) from a table. As Dynamics NAV does not provide this out of the box, we present below a way to select unique records by using queries.

    Problem Statement

    Let's consider the VAT Entry table: 

    The goal is to select one line for each separate document that produced VAT Entries. In other words, we want records grouped by Type, Document Type and Document No.. However, if there are multiple lines with the same value of the triad Type, Document Type and Document No. in the VAT Entry table, we only want to see one of them.

    Solution

    Create a new query object VAT Entry Distinct Document No., with a single DataItem sourced from VAT Entry table. Add the three desired group-by fields Type, Document Type and Document No. as columns.

    To enable grouping, add one more column, with Method Type = Totals. This will automatically set the Group By checkbox to TRUE on the three precedent fields.

    Note that the Group By field is read-only and trying to set it by hand will clarify that:

    Running the query yields a single record per document. You can notice in the second line below for example, how the sales invoice number 103001 had 2 VAT Entries, but it shows up only once in the query:

    Example

    One thing is to be noted: there is a limitation to how much information you can take out from the records. For example, if we need to extract more information than just the one we already have in the columns, then the following apply: adding one more column of Method Type = None will indeed show more information, but it might affect the grouping. More details below.

    Read more on the Patterns Wiki site...

  • Microsoft Dynamics NAV Team Blog

    Coffee Break - Search in a Dynamics NAV object file using Windows PowerShell

    • 1 Comments

    This coffee break post illustrates how to search a text file for specific words or a phrase. You can do this with Windows PowerShell in any text files, but let's use some Dynamics NAV objects exported as text. Technically speaking we are reading a text file then piping it line for line through a search cmdlet, which pipes matching lines further to a log.txt file.

    Coffee Break 5 - Searching through a Dynamics NAV text file

    Customer story:

    The developer wants an automated way of locating all occurrences of a string (table name, field name, comment, ...) in a country-specific version of Dynamics NAV, in this example the Norwegian version. And we will log the output of this search to a log file in c:\NAVApp\Log. 

    Exporting objects from Dynamics NAV:

    Prerequisites:

    Crete a folder C:\MyNAVApp with a subfolder \Log so that the resulting full path is C:\MyNAVApp\Log.

    For this purpose we use the Microsoft Dynamics NAV Application Merge Utilities. Note that these install to the equivalent of C:\Program Files (x86)\Microsoft Dynamics NAV\80\RoleTailored Client. This time we don't need to import the Management module, only the application merge utilities:

    Import-Module "${env:ProgramFiles(x86)}\Microsoft Dynamics NAV\80\RoleTailored Client\Microsoft.Dynamics.Nav.Model.Tools.psd1"

    • Note 1: Make sure to import Microsoft.Dynamics.Nav.Model.Tools.psd1, and not NavModelTools.ps1.
    • Note 2: This will load the path for finsql.exe too, and use the finsql in the client folder.

    Set a few variables. Assuming that we work in folder C:\MyNAVApp\, and we will be searching for where "G/L Account" table reference is used. And we will log the output of this search to a log file in the c:\MyNAVApp\Log folder.

    $objectPath = 'C:\MyNAVApp'
    $sourcepath = Join-Path $ObjectPath '\MyObjects'

    $NAVobjects = Join-Path $ObjectPath 'NAVobjects.txt'
    $LogPath = Join-Path $ObjectPath '\log\whereused.txt'
    #Note, a search string can also be an array of strings
    $SearchString = '”G/L Account”'

     

    Export the objects you like, either all objects:
    Export-NAVApplicationObject  -DatabaseName "Demo Database NAV (8-0)" -DatabaseServer ".\NAVDEMO" -Path $NAVObjectFile

    Or filter (choose the filter you like):

    $FilterString = "Version List=*NAVNO*"
    #or

    $FilterString = "Modified=Yes"
    Export-NAVApplicationObject  -DatabaseName "Demo Database NAV (8-0)" -DatabaseServer ".\NAVDEMO" -Path $NAVObjects -Filter $FilterString 

    #Split into individual object files

    split-navapplicationobjectfile -Source $NAVobjects -Destination $sourcepath -PassThru -Force


    Now load the list of files in the folder. We're using the -File parameter with Get-ChildItem to limit the scope to files only (sub folders are not included).

    $myobjects = Get-ChildItem -Path $SourcePath -Filter *txt -File

    The next line shows a very simple way to read through all text files in the specified path (c:\MyNAVApp) and for each file searches for the search string (in our case "G/L Account) and for each hit pipe the source line to the log file along with the line number. For this we will use the Select-String cmdlet, that can work directly on Objects with File Info (objects returned by calling  Get-ChildItem cmdlet).

    $myobjects | Select-String $SearchString | Out-FileFilepath $LogPath

    Note that using the parameters and segments above implies that:

    • The script raises an error if the $ObjectPath does not exist.
    • Out-File will overwrite the existing file per pipeline
    • Out-File will append lines to the file, per pipeline object

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

    Bas Graaf, Senior Software Engineer Dynamics NAV

     

  • Microsoft Dynamics NAV Team Blog

    Upgrading from Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 to Microsoft Dynamics NAV 2015

    • 10 Comments

    Quite a few people have asked that we excavate the guidance for how to upgrade from Microsoft Dynamics NAV 2009 R2 directly to Microsoft Dynamics NAV 2015. We provided this guidance in the blog post for Cumulative Update 1, but as time progresses, this becomes harder to remember and to find. So here you go:

    Introduction

    This blog post describes the tasks required for upgrading a Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database to Microsoft Dynamics NAV 2015.

    Using the upgrade toolkit and conversions tools that are available, the tasks will lead you through the process of converting the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database to Microsoft Dynamics NAV 2013, converting the Microsoft Dynamics NAV 2013 database to Microsoft Dynamics NAV 2015, and finally, converting the old data with the old table and field structure to function together with the table and field structure of Microsoft Dynamics NAV 2015.

    In order to use the toolkit to upgrade data, you will also need the Microsoft Dynamics NAV 2013 development environment and Microsoft Dynamics NAV 2015.

    Data Upgrade Short Overview

    The following lists provides a brief description of the steps involved in the data upgrade. The steps are detailed in the task sections that follow.

    1. In Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1, compile all tables.
    2. Make a full SQL backup of the Microsoft Dynamics NAV 2009 R2 database.
    3. Import Microsoft Dynamics NAV 2015 license into the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database.
    4. Import Upgrade601800.[Country].1.fob (use Replace All action on the Import Worksheet).
    5. For each company in the database, run form 104001, Upgrade - Old Version, and choose the Transfer Data action.
    6. Make a full SQL backup of the Microsoft Dynamics NAV 2009 R2 database.
    7. Run form 104001, Upgrade - Old Version, and then choose the Delete Objects action.
    8. Uninstall Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1.
    9. Change the compatibility level of the database by using SQL Server Management. Studio.
    10. Download the latest Cumulative Update of Microsoft Dynamics NAV 2013 and install the Microsoft Dynamics NAV 2013 development environment.
    11. Open the old database with Microsoft Dynamics NAV 2013 development environment and convert the database.
    12. Uninstall Microsoft Dynamics NAV 2013.
    13. Make a full SQL backup of the database.
    14. Install Microsoft Dynamics NAV 2015 including the Development Environment, Microsoft Dynamics NAV Server, and Administration Tool components.
    15. Open the old database with Microsoft Dynamics NAV 2015 Development environment and convert the database.
    16. Make sure the system tables (which have IDs in 2000000004…2000000130 range) are compiled (On the Compile window, set the Synchronize Schema option to Later).
    17. Set database role membership for the service account that is used by the Microsoft Dynamics NAV Server instance to db_owner.
    18. Connect Microsoft Dynamics NAV Server instance to the old database.
    19. Start the development environment as administrator.
    20. Synchronize all tables from the Tools menu by selecting Sync. Schema for All Tables, then With Validation.
    21. Import all new Microsoft Dynamics NAV 2015 objects (use the Replace All action on the Import Worksheet, and then set the Synchronize Schema option to Later).
    22. Import Upgrade601800.[Country].fob.
    23. Compile the tables which are not already compiled (set Synchronize Schema option to Later).
    24. Synchronize all tables from the Tools menu by selecting Sync. Schema for All Tables, then With Validation.
    25. Make a full SQL backup of the database
    26. Run the data upgrade process from the Tools menu by selecting Data Upgrade, and then Start.
    27. Delete obsolete tables and upgrade toolkit objects.
    28. Import updated permissions sets and permissions.
    29. Set language of the database.
    30. Import or register the control add-ins.

    Task 1: Run Upgrade Step 1 in the Microsoft Dynamics NAV 2009 R2 or SP1 development environment (Classic client)

    1. Make sure that all table objects have compiled successfully.
      To compile all table objects, in Object Designer, select all objects of type Table, and then on the Tools menu, choose Compile. During compilation, Microsoft Dynamics NAV generates or regenerates the BLOB content in the Object Metadata table that is used in the later steps.
    2. Create a copy or a backup of the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database, and open it in the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 development environment.
    3. Add your Microsoft Dynamics NAV 2015 partner license to the database.
      You can do this by selecting the Save License in Database field in the Alter Database window. If the field was not already selected, a dialog box opens so you can specify the location of your partner license. If the field was already selected, upload the partner license in the License Information window.
    4. Open Object Designer, and then import Upgrade601800.[Country].1.fob from the \UpgradeToolKit\Local Objects folder. In the Import Worksheet window, choose Replace All, and then choose the OK button to import the objects.
    5. For each company in the database, do the following:
      1. Open the company, and make the relevant changes to data. For more information, see Task 3: Data/Object Changes Prior to Step 1 in the MSDN Library.
      2. In Object Designer, run form 104001, Upgrade - Old Version. Choose the Transfer Data button.

        Important: After you have completed this step for all companies, we strongly recommend that you create a full SQL backup the database by using SQL Server management tools.
    6. When you have transferred all data for all companies, in the Upgrade - Old Version window, choose the Delete Objects button.
      This action deletes all objects in the database that are not tables, but also obsolete tables that belong to functionality that is not available in Microsoft Dynamics NAV 2015.

    Task 2: Convert the Microsoft Dynamics NAV 2009 R2 or SP1 database to a Microsoft Dynamics NAV 2013 database

    1. Uninstall Microsoft Dynamics NAV 2009 R2.
    2. Download the latest available Cumulative Update for Microsoft Dynamics NAV 2013.
      The following is article lists the Cumulative Updates that have been released for Microsoft Dynamics NAV 2013: https://mbs2.microsoft.com/Knowledgebase/kbdisplay.aspx?scid=kb,en-us,2842257 
    3. Run the installation program (setup.exe) for Microsoft Dynamics NAV 2013, choose Choose an installation option, choose Custom, and then choose the Client option (with the Development Environment).
    4. Change the compatibility level of the database according to the following:
      For SQL Server 2008 R2 set the compatibility level of the database to 100.
      For SQL Server 2012, set the compatibility level of the database to 110.
      To do this, you use Microsoft SQL Server Management Studio as follows:
      1.  Start Microsoft SQL Server Management Studio and connect to the SQL Server instance that includes the Microsoft Dynamics NAV database.
      2. Right-click the Microsoft Dynamics NAV database, and then select Properties.
      3. In the Database Properties window, choose the Options page, and then set the Compatibility Level to the correct value.
    5. In the Microsoft Dynamics NAV 2013 development environment, open the Microsoft Dynamics NAV 2009 R2 or Microsoft Dynamics NAV 2009 SP1 database and agree to convert the database.
      During this step, Microsoft Dynamics NAV converts all text and code fields to Unicode format by changing their SQL Server data type. This conversion requires more disk space than usual, since both the database and the log file will grow in size considerably. It can also be a lengthy process.
      If your Microsoft Dynamics NAV 2009 R2 database is using SQL Server Collation, within the same step the collation will be changed to a suitable Windows collation. This is because Microsoft Dynamics NAV 2013 and higher versions only support Windows Collation.

      Important: After the database conversion has completed, we strongly recommend that you create a full SQL backup of the database.
    6. The technical upgrade of the database to Microsoft Dynamics NAV 2013 is complete. You can now uninstall Microsoft Dynamics NAV 2013.

    Task 3: Convert the Old Database to a Microsoft Dynamics NAV 2015 Format

    1. Install Microsoft Dynamics NAV 2015.
      During the installation of Microsoft Dynamics NAV 2015, select the Choose an installation option, then select the Custom option, where you choose to install the Client (with the Development Environment), Server, and Administration Tool components.
    2. To convert the old database to a Microsoft Dynamics NAV 2015 format, open the old database in the Microsoft Dynamics NAV 2015 development environment, and then follow the instructions.
    3. After the database conversion is completed, verify that all system tables are compiled.

    System tables have IDs in the range 2000000004 to 2000000130. If any tables are not compiled, then you must compile them. When you compile tables, on the Compile dialog box, set the Synchronize Schema option to Later.

    Task 4: Connect a Microsoft Dynamics NAV 2015 Server Instance to the Converted Database

    1. Using Microsoft SQL Server Management studio, add the service account that is used by the Microsoft Dynamics NAV Server instance (for example, NT AUTHORITY\NETWORK SERVICE) as a member of the db_owner role in the Microsoft Dynamics NAV database on SQL Server.
      For more information, Giving the account necessary database privileges in SQL Server.
    2. Using the Microsoft Dynamics NAV Server Administration tool, connect a Microsoft Dynamics NAV Server instance to the converted database.

    For more information, see How to: Connect a Microsoft Dynamics NAV Server Instance to a Database.

    Task 5: Run Schema Synchronization to initialize the database.

    You can run the schema synchronization from the Microsoft Dynamics NAV Development Environment or Microsoft Dynamics NAV 2015 Administration Shell.

    From the development environment:

    1. Open development environment as an administrator.
    2. On the Tools menu, choose Sync. Schema For All Tables, and choose With Validation, and then follow the schema synchronization instructions.

    If synchronization errors occur, run the schema synchronization from Microsoft Dynamics NAV 2015 Administration Shell as described in the following procedure. This enables you to get the complete list of tables that could not be synchronized.

    From the Microsoft Dynamics NAV 2015 Administration Shell:

    1. Open the Microsoft Dynamics NAV 2015 Administration Shell as an administrator.
    2. Run Sync-NavTenant cmdlet as follows:

    Sync-NavTenant –ServerInstance <ServerInstanceName> -Mode Sync

     Replace <ServerInstanceName> with the name of the Microsoft Dynamics NAV Server instance that is connected to the database. For more information, see How to: Run the Sync-NAVTenant Cmdlet to Synchronize the Tenant Database with the Application Database.

    Task 6: Import the Application Objects to the Converted Database

    1. In the development environment, import all the application objects that you want in the Microsoft Dynamics NAV 2015 database.
      When you import the FOB file that contains the Microsoft Dynamics NAV 2015 objects, on the Import Worksheet, choose Replace All, and then choose the OK button to import the objects.
      On the dialog box for selecting the schema synchronization, set the Synchronize Schema option to Later.
    2. After you have imported Microsoft Dynamics NAV 2015 objects, import the upgrade toolkit FOB file Upgrade601800.[Country].fob from \UpgradeToolKit\Local Objects folder.
    3. Compile all objects that have not been compiled yet. On the Compile dialog box, set the Synchronize Schema option to Later.

    Task 7: Run the Schema Synchronization

    Similar to task 5, run the schema synchronization either from the development environment (Tools – Sync. Schema for All Tables – With Validation…) or from Microsoft Dynamics NAV 2015 Administration Shell.

    This will synchronize the data schema changes of the newly imported tables to the SQL tables. It will also run the Upgrade Toolkit Step 1 logic if it is defined in the upgrade codeunit. This step will be performed for all companies in the database, so you don’t have to re-run it for other companies.

    Note: After the schema synchronization has successfully completed, we strongly recommend that you take a full SQL Server backup of the database. 

    Task 8: Run the Data Upgrade to perform Upgrade Toolkit Step 2

    A data upgrade runs the upgrade toolkit objects, such as upgrade codeunits and upgrade tables, to migrate business data from the old table structure to the new table structure. You can start the data upgrade from the Microsoft Dynamics NAV Development Environment or Microsoft Dynamics NAV 2015 Administration Shell.

    From the development environment:

    1. Open development environment as an administrator.
    2. On the Tools menu, choose Data Upgrade, and then choose Start and follow the instructions on the Start Data Upgrade window.

    If you want to see the result of running the Data Upgrade action, run the following cmdlet from Microsoft Dynamics NAV 2015 Administration Shell:

    Get-NAVDataUpgrade -ServerInstance <ServerInstanceName> -Detailed | Out-GridView

    In the output of this cmdlet, you can see which upgrade codeunits and functions were run, which companies have been upgraded, the time it took to run each upgrade function, and a detailed description of errors that occurred during the data upgrade.

    If errors occurred while running the data upgrade, you can resolve them directly in the database (for example by updating the upgrade codeunits) and then resume the data upgrade. To resume the data upgrade, on the Tools menu, choose Data Upgrade, and then Resume.

    Note: To optimize upgrade performance and effectively use the available computer resources, the data upgrade functions are executed in parallel and across all companies by default. While performing this step, if you discover that some functions are locking each other from executing, then you can configure the data upgrade to execute data upgrade functions in serial instead of parallel. To do this, on the Start Data Upgrade window, set the Execution Mode option to Serial. For more information see http://msdn.microsoft.com/en-us/library/dn762348(v=nav.80).aspx.

    From the Microsoft Dynamics NAV 2015 Administration Shell:

    Open the Microsoft Dynamics NAV 2015 Administration Shell as an administrator, and then run Start-NavDataUpgrade cmdlet as follows:

    Start-NavDataUpgrade -ServerInstance <ServerInstanceName> -Force

    Replace <ServerInstanceName> with the name of the Microsoft Dynamics NAV Server instance that is connected to the database.

    To view the progress of the data upgrade, you can run Get-NavDataUpgrade cmdlet as follows:

    Get-NAVDataUpgrade -ServerInstance <ServerInstanceName> -Progress

    Note: The data upgrade process runs CheckPreconditions and Upgrade functions in the upgrade codeunits. If any of the preconditions are not met or an upgrade function fails, you must correct the error and resume the data upgrade process.

    Task 9: Delete the Upgrade Objects and Obsolete Tables

    At this point, you have upgraded the database to Microsoft Dynamics NAV 2015. Now, you can delete the tables which are not needed into the new application, plus the upgrade codeunits and upgrade table objects that you imported in task 6.

    For application tables, you can see which the tables are marked for deletion in the Version List column in the Tables list of Object Designer. During the data upgrade that you performed in task 8, these tables were marked with the text Old Unused Table - marked for deletion. Upgrade objects are marked with the text Upgrade Toolkit Object - marked for deletion.

    When you delete tables, if you don’t need the data that is saved in the tables, then on the Delete dialog box, set the Synchronize Schema option to Force.

    Task 10: Import upgraded permission sets and permissions by using the Roles and Permissions XMLports

    You import the permission sets and permissions XML files according to the following procedure.

    To import the permission sets and permissions

    1. Delete all permission sets in the database except the SUPER permission set.
      In Object Designer, run page 9802 Permission Sets, and then delete the permission sets.
    2. Run XMLport 9171 Import/Export Permission Sets to import the permission sets XML file.
      In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permission sets XML file.
    3. Run XMLport 9172 Import/Export Permissions to import the permission XML file.
      In the request page for the XMLport, in the Direction field, choose Import, choose the OK button, and then specify the permissions XML file.

    Task 11: Set the Language of the Customer Database

    In the development environment, choose Tools, choose Language, and then select the language of the original customer database.

    Task 12: Add New Control Add-ins

    The database is now fully upgraded and is ready for use. However, you may want to add the new client control add-ins that are included in Microsoft Dynamics NAV 2015. These are not added by the upgrade process. The following client control add-ins are available from the Microsoft Dynamics NAV product media:

    • Microsoft.Dynamics.NAV.MicrosoftDynamicsOnlineConnectControl
    • Microsoft.Dynamics.Nav.Client.BusinessChart
    • Microsoft.Dynamics.Nav.Client.PageReady
    • Microsoft.Dynamics.Nav.Client.PingPong
    • Microsoft.Dynamics.Nav.Client.VideoPlayer
    • Interactive Timeline Visualization Add-in

    You can add control add-ins in the Control Add-ins window in the Microsoft Dynamics NAV Windows client. For more information, see How to: Register a Windows Client Control Add-in.

  • Microsoft Dynamics NAV Team Blog

    Using Windows Powershell cmdlets to modify companies and databases

    • 2 Comments

    Have you been following the coffee break series of blog posts recently? Are you ready to start using Windows PowerShell? If you do not want to get started on your own development database, how about using the Microsoft Dynamics NAV 2015 demonstration database? In this blogpost, we use Windows PowerShell to change the demonstration database into a multitenant environment. Not because we think you should make your own solution multitenant, but because it makes it easier to see what is really going on.

    If we look at the Demo Database NAV (8-0) database which is provided on the product DVD (W1), it has one company called CRONUS International Ltd. The first step towards multitenancy is to create additional companies. For this small task we will use the Windows PowerShell ISE. On your Windows machine, you will need to start the Windows PowerShell ISE in an elevated command prompt (Run as Administrator). After that you will need to set the execution policy. You can read more about this here. After that, we will show the cmdlets that are needed and the modules you need to import.

    Since we will only run scripts that change the demonstration database, we will run the following command:

    Set-ExecutionPolicy unrestricted

    After each command, you must press F5. Now choose File in the menu and then select New to open a new screen or simply press CTRL+N. Type in the following command:

    Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\NavAdminTool.ps1'

    Press F5, CTRL+N. Now we need to create some new companies.

    Copy-NAVCompany -DestinationCompanyName 'CRONUS Production Ltd.' –SourceCompanyName 'CRONUS International Ltd.' -ServerInstance DynamicsNAV80W1
    Copy-NAVCompany -DestinationCompanyName 'CRONUS Sales Ltd.' -SourceCompanyName 'CRONUS International Ltd.' -ServerInstance DynamicsNAV80W1
    Copy-NAVCompany -DestinationCompanyName 'CRONUS Marketing Ltd.' -SourceCompanyName 'CRONUS International Ltd.'-ServerInstance DynamicsNAV80W1
    Copy-NAVCompany -DestinationCompanyName 'CRONUS Staff Ltd.' -SourceCompanyName 'CRONUS International Ltd.' -ServerInstance DynamicsNAV80W1

    Press F5, CTRL+N. Now we will export the application data into a new database.

    Export-NAVApplication –DatabaseServer SQL\INSTANCE –DatabaseName 'Demo Database NAV (8-0)’ –DestinationDatabaseName 'Demo Database NAV (8-0) App' –Force

    NOTE: Change the value of the DatabaseServer parameter to specify your server name and SQL Server instance that is hosting the Demo Database NAV (8-0) database.

    Press F5, CTRL+N. Now we will prepare the service tier instance

    Set-NavServerInstance DynamicsNAV80W1 –Stop
    Set-NAVServerConfiguration -ServerInstance DynamicsNAV80W1 -Keyname MultiTenant  -KeyValue "True"
    Set-NAVServerConfiguration -ServerInstance DynamicsNAV80W1 -Keyname DatabaseName -KeyValue ""
    Set-NavServerInstance DynamicsNAV80W1 –Start

    Press F5, CTRL+N. Now we will mount the application and first tenant

    Mount-NAVApplication -DatabaseServer SQL\INSTANCE -DatabaseName ‘Demo Database NAV (8-0) App’ -ServerInstance DynamicsNAV80W1
    Mount-NAVTenant -ServerInstance DynamicsNAV80W1 -Id Default –DatabaseName ‘Demo Database NAV (8-0)’ -OverWriteTenantIDInDatabase –AllowAppDatabaseWrite

    NOTE: In a Microsoft Dynamics NAV application that is used in a multitenant deployment, some areas require you to set up web services. Since web services are created in the application database, you must create at least one tenant that has write access to the application database. This setting is determined by the Allow application database writes parameter when you mount a tenant against a Microsoft Dynamics NAV Server instance. For more information, see How to: Mount or Dismount a Tenant on a Microsoft Dynamics Server Instance.

    For example, you can create a dedicated administration tenant that you mount against the Microsoft Dynamics NAV Server instance when you create web services for an application. It is also needed allowing to importing FOB files. For more information see this earlier blog post.

    Press F5, CTRL+N. Now we will import the example Windows PowerShell scripts that you can find on the Microsoft Dynamics NAV 2015 product media:

    Import-Module 'D:\NAV.8.0.39663.W1.DVD\WindowsPowerShellScripts\NAVMultitenancySamples.psm1'

    NOTE: You can copy the WindowsPowerShellScripts folder from the product media to a safe location on your hard drive as long as you do copy all files in that folder
    NOTE: If you are running the PowerShell scripts on a computer that does not have SQL Server / SQL Server tools installed, the following tools are necessary: Microsoft® Windows PowerShell Extensions for Microsoft® SQL Server® 2012

    Press F5, CTRL+N and we will move the companies from the default tenant to newly created tenants.

    HowTo-MoveCompanyToTenant -ServerInstance DynamicsNAV80W1 –FromDatabase 'Demo Database NAV (8-0)' -OldTenantName Default -NewTenantName Production -CompanyName 'CRONUS Production Ltd.' -ToDatabase Production -DatabaseServer SQL\Instance

    HowTo-MoveCompanyToTenant -ServerInstance DynamicsNAV80W1 –FromDatabase 'Demo Database NAV (8-0)' -OldTenantName Default –NewTenantName Sales -CompanyName 'CRONUS Sales Ltd.' –ToDatabase Sales -DatabaseServer SQL\Instance

    HowTo-MoveCompanyToTenant -ServerInstance DynamicsNAV80W1 –FromDatabase 'Demo Database NAV (8-0)' -OldTenantName Default -NewTenantName Marketing -CompanyName 'CRONUS Marketing Ltd.' -ToDatabase Marketing -DatabaseServer SQL\Instance

    HowTo-MoveCompanyToTenant -ServerInstance DynamicsNAV80W1 –FromDatabase 'Demo Database NAV (8-0)' -OldTenantName Default –NewTenantName Staff -CompanyName 'CRONUS Staff Ltd.' –ToDatabase Staff –DatabaseServer SQL\Instance

    Press F5, CTRL+N and we will now remove the companies from the default tenant.

    Remove-NAVCompany -Tenant Default -CompanyName 'CRONUS Production Ltd.' -ServerInstance DynamicsNAV80W1
    Remove-NAVCompany -Tenant Default -CompanyName 'CRONUS Sales Ltd.' -ServerInstance DynamicsNAV80W1
    Remove-NAVCompany -Tenant Default -CompanyName 'CRONUS Marketing Ltd.' -ServerInstance DynamicsNAV80W1
    Remove-NAVCompany -Tenant Default -CompanyName 'CRONUS Staff Ltd.' -ServerInstance DynamicsNAV80W1

    To dismount and mount again, I refer you to my previous posting here. You can simply start the Windows client and if you do not change the config files, you will get a message stating that no tenants were specified. Press OK and change the url like this:

    name_servicetier:7046/DynamicsNAV80W1/default
    name_servicetier:7046/DynamicsNAV80W1/production
    name_servicetier:7046/DynamicsNAV80W1/sales
    name_servicetier:7046/DynamicsNAV80W1/marketing
    name_servicetier:7046/DynamicsNAV80W1/staff

    NOTE: If you set up multitenancy on a machine that is configured with other releases of Dynamics NAV side by side, it could be that the Administration console may generate an error stating in the PowerShell ISE tool that you can only run the Powershell command lets for a specific release. The root cause could be that you installed Dynamics NAV 2013 R2 after you installed Dynamics NAV 2015 or vice versa. You would need to verify the following key in the registry. It should match the release you are using. V7.1 is the build number for Dynamics NAV 2013 R2 and v8.0 is the build number for Dynamics NAV 2015.


    Windows Registry Editor Version 5.00
    [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\PowerShell\1\PowerShellSnapIns\Microsoft.Dynamics.Nav.Management]
    "ApplicationBase"="C:\\\\Program Files\\\\Microsoft Dynamics NAV\\\\71\\\\Service\\\\"
    "AssemblyName"="Microsoft.Dynamics.Nav.Management, Version=7.1.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"
    "Description"="Microsoft Dynamics Nav Management Snap-in"
    "ModuleName"="C:\\Program Files\\Microsoft Dynamics NAV\\71\\Service\\Microsoft.Dynamics.Nav.Management.dll"
    "PowerShellVersion"="2.0"
    "Vendor"="Microsoft Corporation"
    "Version"="7.1.0.0"

     

    Regards,

    Marco Mels
    CSS EMEA

    This posting is provided "AS IS" with no warranties, and confers no rights

  • Microsoft Dynamics NAV Team Blog

    Coffee Break | Windows PowerShell and Piping

    • 2 Comments

    The | (pipe) functionality in Windows PowerShell offers endless opportunities for formatting and manipulating results from one cmdlet to the next.

    Coffee Break 4: Piping in Windows PowerShell

     Each time we run a Windows PowerShell cmdlet, the resulting output is not text but an Object providing structured information of the result.

    Example:  Get-NAVServerInstance will return existing instances of NAVServerInstance Objects:

    with the following properties:

    •   ServerInstance
    •   DisplayName
    •   State
    •   ServiceAccount
    •   Version
    •   Default

    Windows PowerShell can pipe objects. Pipelines are a series of cmdlets (segments) separated by a Pipeline character ‘|’. Each Item is passed through all segments of the pipeline (left to right) before the next is processed.

    Piping example:

    First import your Dynamics NAV cmdlets (if you are not already in the Microsoft Dynamics NAV Administration Shell, that is):

    Import-Module 'C:\Program Files\Microsoft Dynamics NAV\80\Service\Microsoft.Dynamics.Nav.Management.dll'

    This imports the NAV.Management.dll for version 80 (=NAV 2015). Adjust the path to the version you are working with.

    Get-NAVServerInstance

    In case Get-NAVServerInstance returns more than one, then let's filter the one(s) that we want by piping the original result through a filter. Or said in other words: We pipe it to the Where-Object cmdlet:

    #Filter by version

    Get-NAVServerInstance | where-Object –Property Version -like "8.0*”

    One can also omit the –Property parameter here, and just run:

    Get-NAVServerInstance | where-Object Version -like "8.0*”

     

    Then we will take the result and pipe that to another cmdlet, let's pipe it to Sync-NAVTenant:

    Get-NAVServerInstance | where-Object –Property Version -like "8.0*” | Sync-NAVTenant

    Note also that the syntax of Where-Object cmdlet has changed across Windows PowerShell  versions, so for compatibility, and going forward , the following syntax should be used:

    Get-NAVServerInstance | where-Object  {$_.Version -like "8.0*”} | Sync-NAVTenant

    Where $_ is used to reference the Item returned by the previous pipeline segment (in the above example, it references the instance of NavServerInstance Object returned by Get-NAVServerInstance cmdlet). However running the above will still fail to sync Microsoft Dynamics NAV tenants for instances that are not running (State: stopped).  This will not abort the process, but we can also further filter on only those instances that are running:

    Get-NAVServerInstance | where-Object  {$_.Version -like "8.0*”} | where-Object {$_.State –eq 'Running'} | Sync-NAVTenant

    The two pipline segments filtering the output can of course be combined in one. We join the filtering conditions using –And operator. Note that Logical operators (-And / -Or) are valid only within script blocks.

    Get-NAVServerInstance | where-Object  {$_.Version -like "8.0*” -And $_.State –eq 'Running'} | Sync-NAVTenant

     

    See further content about Piping here: https://technet.microsoft.com/en-us/library/dd347655.aspx

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

  • Microsoft Dynamics NAV Team Blog

    Coffee break in the Cloud with Windows PowerShell

    • 1 Comments

    In this post we will run Windows PowerShell in the cloud. The ability to connect your Windows PowerShell with your Microsoft Azure instances can double the value of Microsoft Dynamics NAV and PowerShell, since you now have tools for both on-premises and cloud deployments. And having the choice of hosted deployments is also one of the first prerequisites for moving to Cloud-first as described in the Are you putting the cloud first? article.

    Coffee Break 3: PowerShell Remoting with Microsoft Dynamics NAV

    Prerequisites:

    Customer Story:

    • Cloud first
    • Running PowerShell remotely will be handy for customers who migrate or automate their system / part of their system or their development environment in / to the cloud.

    Connect

    Open your Windows PowerShell ISE. In order to connect to Azure you must have two things:

    1. Azure Publishsettings file which will have all the details of your subscription.
    2. Your Azure account credentials.

     

    #Connect to Azure

    import-module azure #see pre-requisites from above.

    #Check if we are logged on to our Azure subscriuption - expect this to fail for now:

    Get-AzureVM

    #Log in to your Azure subscription

    Add-AzureAccount

    Get-AzurePublishSettingsFile #After saving your file once, just skip this point and remember where you save it, you don't need to save it every time. But keep the file safe as it contains connection details.

    Import-AzurePublishSettingsFile "C:\Temp\Windows Azure MSDN - Visual Studio Ultimate-5-14-2013-credentials.publishsettings"

    #Check again - this time it should work:

    Get-AzureVM 

     

    You should now be able to run generic Azure commands, like Get-AzureVM and Start-AzureVM and more to access your Virtual Machines, and get some of the same information you get via the Azure management portal. Next, we will connect to a running machine and start running PowerShell remotely.

    For the next steps you need to have at least one Virtual Machine (VM) running in your Azure subscription. You can create a VM easily from your Azure management portal. You will be able to pick a ready-built image with Microsoft Dynamics NAV 2015 from the Azure marketplace under Microsoft -> Dynamics. You can also create your own VM, and you can still continue with the scripts below. 

    Note: When you create a new VM, you need to specify both Machine Name and Service Name. Some of the scripts below assume that these two names are the same. So just to avoid any confusion, keep Service Name and Machine Name the same. 

    To connect to a remote machine we can use the cmdlet New-PSSession. This works nice and easy on your domain. However if the remote machine is in the cloud, connecting becomes a little bit more challenging. Luckily we have helper-scripts, right on the NAV product DVD - for more information, see Deploying and Managing Microsoft Dynamics NAV on Microsoft Azure. In this example, the DVD is in D:\NAVDVD\, and we will import the NAVRemoteAdministration module from the Cloud subfolder.

    #Load NAV remote admin from the product DVD
    Import-Module "C:\NAVDVD\WindowsPowerShellScripts\Cloud\NAVRemoteAdministration\NAVRemoteAdministration.psm1"

    Before continuing lets just stop and check what the NAVRemoteAdministration module contains:

    get-command -module NAVRemoteAdministration

    What we will use first, is New-NAVAdminSession but also notice functions like Copy-FileToRemoteMachine, Get-NAVServerUserRemotely, New-NAVServerInstanceRemotely and Start-ServiceRemotely. We're sure you can imagine how useful these can be. But first we need to create a session object that we can use to connect: Make sure that the VM you refer to exists and is running, and that service and machine names are the same:


    $PsSession = New-NAVAdminSession `
      -RemoteMachineAddress MyTestDynnav2015.cloudapp.net `
      -AzureServiceName MyTestDynnav2015 `
      -VMAdminUserName VMAdmin `
      -VMAdminPassword 1MyPassword2

     

    The 4 parameters in this function all are from when you created your VM.

    You now have a session variable ($PSSession) that you can use to connect to your cloud VM. This is a persistent session but we will come back to session types in more details another time. You can start either an interactive session or just run scripts to this session.

    #Enter an interactive session
    Enter-PSSession $PsSession

    get-navserverinstance #will show you any NAV instances on this VM

    get-service

    dir

    and once you are done checking what you want, return to your own PC:

    EXIT

     

    Run a script remotely. It is a little bit of a problem to list something, since the result may just stay on the remote machine. So for example Get-NAVServerInstance would run, but not necessarily show you something. So instead we run a cmdlet to run an action - let's stop the Microsoft Dynamics NAV Server service remotely:

    Invoke-Command -Session $Pssession -ScriptBlock{Set-NavServerInstance NAV -stop}

    Or run a script from a file, this time adding some parameters:

    Invoke-Command -Session $psSession -FilePath .\MySCriptps1 -ArgumentList Arg1,Arg2

     

    And finally let's run one of the functions we looked at earlier, and again using the $PSSession variable to do the connection

     Copy-FileToRemoteMachine -Session $Pssession -SourceFile c:\Tmp\MyDB.bak -DestinationFile c:\NAV\DB.BAK

     

    As you can imagine, the topic of PowerShell Remoting is much bigger than what can fit in a coffee break, but hopefully this has given some good insights at least.

     

    Jasminka Thunes, Escalation Engineer Dynamics NAV EMEA

    Lars Lohndorf-Larsen, Escalation Engineer Dynamics NAV EMEA

  • Microsoft Dynamics NAV Team Blog

    Importing objects in a multi tenancy environment

    • 5 Comments

    The following posting is based on a support request where a partner wanted specific instructions what the process should be when trying to import a FOB file in a true multi tenant environment. If you simply import the FOB file, the
    following message box will first pop up to you:

    This box is new in Dynamics NAV 2015 and is not part of the scope of this blog posting. It is the other message box that needs more explanation:

    The first suggestion is to verify if the server is indeed running (of course it is) and the suggestion from support would be to always verify if the server is accessible via the mentioned port. If these are all true, then the second suggestion is to ensure all tenants are dismounted except one. This is because there should only be one tenant mounted that does have the option “Allow application database write to” enabled.

    Note that if you import objects into a multitenant deployment of Microsoft Dynamics NAV 2013 R2, you will see a different error message if you have more than one tenant mounted against your service instance:

    Tenant 'Default" could not be found.
    Check that:
    1. Tenant 'default' is mounted on the server, or
    2. Specify a different Tenant in Tools|Options.

    The meaning is the same as shown in the screenshot from Microsoft Dynamics NAV 2015: Before you import objects into a multitenant application, you must ensure that only one tenant is mounted against that Microsoft Dynamics NAV Server instance, and that this tenant is mounted with the database synchronization mode set to Force.

    The following configuration will generate the warning “Unable to process table changes because the request cannot be processed by the specified Dynamics NAV Server instance”:

    If you dismount all the tenants except the one that does have the option “Allow application database writes” enabled, the warning will not pop up and you will be able to continue the object import. The following configuration will therefore not generate the warning “Unable to process table changes because the request cannot be processed by the specified Dynamics NAV Server instance”:

    To dismount a tenant, you can utilize the Microsoft Dynamics NAV 2015 Administration Shell which may need to be start up with Run as Administrator:

    Dismount-NavTenant –ServerInstance DynamicsNAV80SSL –tenant marketing –force
    Dismount-NavTenant –ServerInstance DynamicsNAV80SSL –tenant production –force
    Dismount-NavTenant –ServerInstance DynamicsNAV80SSL –tenant sales –force
    Dismount-NavTenant –ServerInstance DynamicsNAV80SSL –tenant staff –force

    After you have done the necessary work to get the application upgraded with the latest rollup or whatever fob file you are trying to import, you need do mount the tenants again:

    Mount-NavTenant –ServerInstance DynamicsNAV80SSL –DatabaseInstance NAVDEMO –DatabaseName marketing –DatabaseServer SQL-02 –DefaultCompany ‘CONTOSO Marketing Ltd.’ –id marketing
    Mount-NavTenant –ServerInstance DynamicsNAV80SSL –DatabaseInstance NAVDEMO –DatabaseName production –DatabaseServer SQL-02 –DefaultCompany ‘CONTOSO Production Ltd.’ –id production
    Mount-NavTenant –ServerInstance DynamicsNAV80SSL –DatabaseInstance NAVDEMO –DatabaseName sales –DatabaseServer SQL-02 –DefaultCompany ‘CONTOSO Sales Ltd.’ –id sales
    Mount-NavTenant –ServerInstance DynamicsNAV80SSL –DatabaseInstance NAVDEMO –DatabaseName staff –DatabaseServer SQL-02 –DefaultCompany ‘CONTOSO Staff Ltd.’ –id staff

    Last but not least you need to synchronize the tenant with the application
    database:

    Sync-NavTenant –ServerInstance DynamicsNAV80SSL –tenant marketing 
    Sync-NavTenant –ServerInstance DynamicsNAV80SSL –tenant production
    Sync-NavTenant –ServerInstance DynamicsNAV80SSL –tenant staff 
    Sync-NavTenant –ServerInstance DynamicsNAV80SSL –tenant sales

    Hope this makes the process a bit more clear.

    Regards,

    Marco Mels
    CSS EMEA

    This posting is provided "AS IS" with no warranties, and confers no rights

    This blog post was updated on March 21, 2015, to include a note about the same scenario in Microsoft Dynamics NAV 2013 R2.

Page 2 of 50 (740 items) 12345»