Microsoft Dynamics NAV Development Blog

Denne blog indeholder vejledninger og codesnippets i C/AL, PowerShell, SQL, m.fl., som bliver anvendt i det daglige arbejde med at udvikle og vedligeholde Navision Stat.

Bloggen er på engelsk og henvender sig til NAV-udviklere.

The Navision Stat Development Team resides at the Agency for Modernisation, as part of the Ministry of Finance in Denmark. We support and develop Microsoft Dynamics NAV2013R2, and currently work towards a multitenant deployment architecture, testdriven development, and an upgrade to Microsoft Dynamics NAV2016. 

This blog contains walktroughs, instructions and codesnippets in C/AL, PowerShell, SQL, etc. which document the solutions to some of the many big and small challenges we meet in our ongoing effort to improve and optimize Microsoft Dynamics NAV. 

Blog content:


All content provided on this blog is for informational purposes only. The Navision Stat team makes no representations as to the accuracy or completeness of any information on this site or found by following any link on this site. The Navision Stat team will not be liable for any errors or omissions in this information nor for the availability of this information. 

Execute SQL query from NAV

NAV2016 - 4Apr2017: We recently needed to create an object to query the SQL database directly from NAV. The method used to query the SQL database from NAV had to be another method than ADO, as we support multiple hosting environments that by default do not contain the .NET framework ADODB.ConnectionClass. Instead, we wanted to use the namespace System.Data.SQLClient, which is the .NET framework Data Provider for SQL Server. 

We use the DotNet datatypes SqlConnection, SqlCommand, and SQLDataReader to connect, query, and retrieve data from a given SQL database to NAV: 

  • System.Data.SqlClient.SqlConnection
  • System.Data.SqlClient.SqlCommand
  • System.Data.SqlClient.SqlDataReader

The CodeUnit in this post is a small part of a bigger project concerning identifying white spaces in the database. But in the example given, we just query the Vendor table and retrieve 5 vendor names, so as to give a simple example of how to use the namespace System.Data.SQLClient.

The object shown in this post has been tested and works in both NAV2013R2 and NAV2016. 

Example

Below is an extract of the CodeUnit. Notice that you have to add the name of the server and database in use - it is hardcoded for simplicity (see our other post regarding how to retrieve the server and database name). 

ExecuteSQLcommand()

//DatabaseName and ServerName (hardcoded for simplicity)

g_DatabaseName:= 'MyDatabase'; //Update before running the codeunit

g_ServerName:=  'MyServer';    //Update before running the codeunit

//Retrieve and remove special characters from the company name

l_CompanyName:= CONVERTSTR(COMPANYNAME,'."\/''%][','________');

//Define the table to query and the query itself (hardcoded for simplicity)

l_TabelName:= '$Vendor';

l_SQLquery:= 'SELECT top 5 No_, Name ' +

              'FROM [' +  g_DatabaseName + '].[dbo].[' + l_CompanyName + l_TabelName + ']' + 

              'WHERE Name <> ''''';

//Create and open SQN connection, run the SQL query, and store the query result in SQLDataReader

GetSQLConnection(l_SQLConnection);

l_SQLConnection.Open;

l_SQLCommand:= l_SQLCommand.SqlCommand(l_SQLquery,l_SQLConnection);

l_SQLCommand.CommandTimeout(0);

l_SQLDataReader:= l_SQLCommand.ExecuteReader;

//Check if the query returned any values

IF l_SQLDataReader.HasRows THEN BEGIN

  MESSAGE(STRSUBSTNO('At least one record returned from the query'));

END ELSE BEGIN

  MESSAGE(STRSUBSTNO('No records returned from the query'));

END;

//Loop through records returned from the query and inform

WHILE l_SQLDataReader.Read DO BEGIN

     l_Counter := l_Counter + 1;

     MESSAGE(STRSUBSTNO('Record %1 of 5: \\ID: %2 \Name: %3',l_Counter, l_SQLDataReader.GetValue(0), l_SQLDataReader.GetValue(1)));

END;

//Always close connection again

l_SQLConnection.Close;


Retrieve name of NAV database, server, service etc. 

NAV2016 - 28March2017: In this post we show a method for retrieving the name of the NAV database and its location (regardless of the database being singletenancy or multitenancy, or whether the application and tenant database are placed on the same or separate servers). We also show how to retrieve the server computer name, the name of the service in use, etc. Basically all the deployment information needed. 

Before, in NAV2013, we retrieved much of the needed deployment info from the server config files, but the content of these files have been changed in NAV2016. Thus, we now use the following DotNet .dll files: 

  • Microsoft.Dynamics.Nav.Types
  • System.Collections.Generic.List
Download the CodeUnit 'GetDeploymentInfo' here: 

The CodeUnit contains the following 7 functions: 
  • GetSQLTenantDatabaseServerName
  • GetSQLTenantDatabaseName
  • GetSQLApplicationDatabaseServerName //only relevant if multitenancy
  • GetSQLApplicationDatabaseName //only relevant if multitenancy
  • GetServerComputerName
  • GetServiceName
  • IsDatabaseMultitenant

Example

Here is an extract of the CodeUnit: 

GetSQLTenantDatabaseServerName(): Text[250]

//Returns the server of the SQL tenant database (both for single- and multitenancy).

InstantiateObjectToRetrieveSQLTenantDatabaseInfo(NavTenantRuntimeSettings);

EXIT(FORMAT(NavTenantRuntimeSettings.DatabaseServer));


GetSQLTenantDatabaseName(): Text[250]

//Returns the name of the SQL tenant database (both for single- and multitenancy).

InstantiateObjectToRetrieveSQLTenantDatabaseInfo(NavTenantRuntimeSettings);

EXIT(FORMAT(NavTenantRuntimeSettings.DatabaseName));


InstantiateObjectToRetrieveSQLTenantDatabaseInfo(VARNavTenantRuntimeSettings : DotNet"Microsoft.Dynamics.Nav.Types.NavTenantRuntimeSettings")

//Instantiate object and get info from NavTenantRuntimeSettings based on TenantID

NavTenantManagementTasks:= NavTenantManagementTasks.NavTenantManagementTasks();

TenantList:= NavTenantManagementTasks.GetTenants(TENANTID);

NavTenantRuntimeSettings:= TenantList.Item(0);


If you run into an error message on runtime regarding a missing .ddl file, you will need to copy the .dll file from C:\Program Files (x86)\Microsoft Dynamics NAV\90\RoleTailored Client to the subfolder Add-ins. This is only necessary at the time of development, and not all users will need to do this. 


How to identify white spaces with SQL

NAV2013R2/NAV2016 - 13Feb2017: We have had difficulties with records where the ID (for example, Vendor No. or Customer No.) contains white spaces. The white spaces are normally added by a user by copy-pasting from Excel. 

The white spaces in the ID allows for several records to seemingly have the same ID, even though they are differentiated by a white space. The problem is that records with a whitespace in the ID can be impossible to open i NAV, and furthermore, we have encountered that "errors" in several reports have been caused by the report not being able to differentiate otherwise identical records, where one ID contains white spaces.

We have encountered the following white spaces: 

  • Line feed (char10)
  • Carriage return (char13)
  • Horizontal tab (char9) (much less widespread than the others)

We have mainly had issues with white spaces in the tables Vendor and Customer, and so, we have developed a query that identifies which records in these tables contain white spaces. The SQL query can be found here: SQL query to identify records with white spaces (sql)

For each company, the query returns the records with white spaces and shows the kind of white space: NAV White spaces results (jpg)

The Messages area informs of the number of records for each company:

 NAV White spaces messages (png)

The query can easily be altered to search other tables and other white spaces (by default, it searches for char(10) and char(13)).  


How to work with multiple NAV versions

NAV2013R2/NAV2016 - 18Nov2016: When running multiple versions of NAV - as we are now, because we are testing NAV2016 - Powershell will automatically choose to run a script of the last installed NAV version. Powershell will not specifically ask which NAV version it should run, nor can you define the version in the script. The same is true for when running an object from the Development Environment. 

If you wish to run another NAV version than the default version, the solution is to open the Microsoft Registry Editor (search "RegEdit"), which contains settings to the system registry. Select the following path:

HKEY_LOCAL_MACHINE / SOFTWARE / Classes / DYNAMICSNAV / Shell / Open / Command

Here you can change the path to the NAV version that Powershell automatically uses:

NAV multiple versions - Powershell (png)

So just a small tip, but we originally had trouble finding the solution by Googling, so maybe someone else can benefit from this. 


How to prevent NAV2013r2 from starting a new client session when running an object from C/SIDE

NAV2013R2 - 24Oct2016: With the default setup of NAV2013r2 a new client session is opened whenever you run an object from the C/Side development environment. This is quite time consuming and unnecessary, so here we share how to change this setup. 

Open cmd.exe and run the following command: 

C:\>Windows\Microsoft.NET\Framework\v4.0.30319\RegAsm.exe/register "C:\Program Files (x86)\Microsoft Dynamics NAV\71\RoleTailoredClient\Microsoft.Dynamics.Nav.Client.WinForms.dll" /tlb

Notice that you can't always just copy paste the command, as it may return an error. Enter the command manually, using Enter to find the folder/file needed in the path. 


From multitenancy to singletenancy

NAV2013R2 - 13Oct2016: We are currently working towards migrating all governmental agencies (approx 160 databases) to a multitenancy environment. As part of our preparations for the migration we wanted to make sure that we were able to roll-back and return to singletenancy in case something went wrong with a given database. 

This post is based on the guide from Microsoft on how to merge an application database with a tenant database which states that we can export the application tables from the application database to the tenant database by using the following Powershell functions: 

Remove-NAVApplication -DatabaseServer '[Name of our DBServer] -DatabaseInstance '[Name ofDBinstance]' -DatabaseName '[Name of tenant database' -Force

Export-NAVApplication -DatabaseServer '[Name of our DBServer] -DatabaseInstance '[Name ofDBinstance]' -DatabaseName '[Name of application database]' -DestinationDatabaseName '[Name of tenant database' -Force

But when running the Export-NAVApplication to export the application tables from the application database to the tenant database, we saw the following error: 

Export-NAVApplication : The '[Name of tenant database]' database that is specified in the ApplicationDatabaseName,contains NAV application tables. Please invoke 'Remove-NAVApplication-DatabaseName [Name of the tenant database]' to clean up application tables from the database. 

The problem was, of course, that the Remove-NAVApplication had already been run, and the tenant database thus should contain no application tables. But clearly, the tenant database still contained some unwanted data. 

It turned out that - in our case - it was due to the tenant database containing schemas that referred to non-standard users/roles. So we just had to remove these from the database, after which the Export-NAVApplication was run without any problems and thus achieving a successful roll-back to singletenancy:

NAV remove schema

So we deleted all schemas that were not the following: 

'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys', 'db_owner', 'db_accessadmin', 'db_securityadmin' ,'db_ddladmin', 'db_backupoperator', 'db_datareader', 'db_datawriter', 'db_denydatareader', 'db_denydatawriter'. 

As we wanted to automate the deletion of non-standard schemas, we created the following SQL-query for it:

--define database
USE [NameOfDatabase] --Change this!

go

--declare the cursor and the name of the column we want to  
--retrieve from the result set (the name of the schema)
DECLARE @SCHEMADATA AS CURSOR;
DECLARE @SCHEMA_NAME AS NVARCHAR(150);
--declare counter, so we can count the number of items in the result set
DECLARE @COUNTERCURSOR AS CURSOR;
DECLARE @COUNTER AS INT;
--declare the SQL-script we want to run (that will 
--delete non-default schemas)
DECLARE @runSQL VARCHAR(max);

SET @runSQL = '' + Char(10) + Char(10);
--Retrieve number of non-standard schemas
SET @COUNTERCURSOR = CURSOR
FOR SELECT Count(*)
    FROM   INFORMATION_SCHEMA.SCHEMATA
    WHERE  SCHEMA_NAME NOT IN ( 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys',
                                'db_owner', 'db_accessadmin', 'db_securityadmin'
                                ,'db_ddladmin', 'db_backupoperator', 'db_datareader',
                                'db_datawriter','db_denydatareader','db_denydatawriter' );

OPEN @COUNTERCURSOR;

FETCH FROM @COUNTERCURSOR INTO @COUNTER;

--Print number and status
PRINT 'Number of non-standard schemas: '
      + CONVERT(VARCHAR(10), @COUNTER);

IF @COUNTER > 0
  PRINT 'The following schemas will be deleted: ';
ELSE
  PRINT 'No schemas will be deleted';

--Retrieve non-standard schemas 
SET @SCHEMADATA = CURSOR
FOR SELECT SCHEMA_NAME
    FROM   INFORMATION_SCHEMA.SCHEMATA
    WHERE  SCHEMA_NAME NOT IN ( 'dbo', 'guest', 'INFORMATION_SCHEMA', 'sys',
                                'db_owner', 'db_accessadmin', 'db_securityadmin'
                                ,'db_ddladmin''db_backupoperator', 'db_datareader',
                                'db_datawriter''db_denydatareader',
                                'db_denydatawriter' );

--Loop through result set (the non-default schemas)
OPEN @SCHEMADATA;

FETCH next FROM @SCHEMADATA INTO @SCHEMA_NAME;

WHILE @@FETCH_STATUS = 0
  BEGIN
      --print name of schema that will be deleted
      PRINT @SCHEMA_NAME;

      --@RunsSQL will now contain the DROP SCHEMA but is not executed
      SET @runSQL = @runSQL + 'DROP SCHEMA [' + @SCHEMA_NAME + ']'
                    + Char(10);

      FETCH next FROM @SCHEMADATA INTO @SCHEMA_NAME;
  END

CLOSE @SCHEMADATA;

DEALLOCATE @SCHEMADATA;

CLOSE @COUNTERCURSOR;

DEALLOCATE @COUNTERCURSOR;

--Print the SQL that will drop the schemas
PRINT @runSQL;
--Execute the drop of the schemas
--EXEC (@runSQL)

Note that the final EXEC(@runSQL) is commented out, so the above query will not delete any schemas when it is run, and will only show you those schemas that it would have deleted. 


Delete NAV users with SQL

NAV2013R2 - 07Oct2016: When having to access a backup of a NAV database that has been in operation, and which has been restored for testing purposes, the content of the tables User, User Property, User Personalization and Access Control should be deleted. This can be done directly in the SQL Management Studio with the following SQL-snippet:
              
DECLARE @db varchar(60)
SET @db = 'NameOfDatabase'
DECLARE @SQL VARCHAR(MAX)
SET @SQL =
'TRUNCATE TABLE [' + @db + '].[dbo].[User]
TRUNCATE TABLE [' + @db + '].[dbo].[User Property]
TRUNCATE TABLE [' + @db + '].[dbo].[User Personalization]
TRUNCATE TABLE [' + @db + '].[dbo].[Access Control]'
EXEC(@SQL)

SMTP - Files are created but are not sent

NAV2013R2 - 07Oct2016: This post shows how to change the limit of the size of message sent to a SMTP service. The consequence of limit too small is that the file created by NAV is not sent to the recipient.

You can use the standard SMTP codeunit 400 (SMTP Mail) and its function CreateMessage() in Nav2013r2 to send emails from the system - for example a report.

In page 409 SMTP Mail Setup you define which the recipient server and the server port.

If you experience that the report is created but the email is not sent, the reason can be that the recipient server has not been setup to handle that size of emails.

Open the Internet Informations Services (IIS) application. Right-click on the SMTP attribute > Properties > Message. Here you can change the limit size.

Change limit of message size  FMTP (png)

Afterwards, you will need to restart the SMTP service. Reach it through the Server Manager. Find the service Simple Mail Transfer Protocol, right-click and restart the service.


Convert data in all NAV companies

NAV2013R2 - 07Oct2016: When upgrading Navision it is often necessary to run a series of data conversion code units, so that a given table in each of the companies in the database are updated. Tou will need to use the CHANGECOMPANY function in order to loop though all companies in the database and update a given field in a given table.

Create a new CodeUnit with the following variables (change the subtype of the local variable depending on the table you want to modify):
  • Global Value g_Company (Datatype: Record, SubType: Company)
  • Function ConvertData (Parameter: p_CompanyName, Datatype: Text, and Local variable: l_AliasSetup, DataType: Record, Subtype: Alias Setup)
Loop through the companies in the OnRun() function and call the Convert function, where the companies are changed and the table fields changed:
NAV2013R2 Loop through companies (png)

For copy-paste:

OnRun()

  //Loop through all companies, and call the Convert-function. Pass along the company name
  IF g_Company.FINDFIRST THEN REPEAT
    Convert_IN02324(g_Company.Name);
  UNTIL g_Company.NEXT = 0;

MESSAGE('Data converting finalized');

Convert_AliasSetup(p_CompanyName : Text[30])

//We change the company, and chose the table AliasSetup
l_AliasSetup.CHANGECOMPANY(p_CompanyName);
//If the table is not empty, then the changes can be made to the table for this company
IF l_AliasSetup.FIND('-') THEN BEGIN
//If a certain setup, then change the content of certain fields.
    IF (l_AliasSetup."Use Alias with Jobs" = l_AliasSetup."Use Alias with Jobs"::No) THEN BEGIN
        l_AliasSetup."Create Desc. Job Alias" := l_AliasSetup."Create Desc. Job Alias"::"Manual";
        l_AliasSetup.MODIFY;
    END;
END;


Find name of NAV server (service) address

NAV2013R2 - 06Oct2016: This post shows how you can find the name of the service address used (Microsoft Dynamics NAV Server Administration) through the function GETURL instead of the table Active Session.  

The table Active Sessions normally contains all sessions and the name of the NAV service the users are accessing the NAV database with. And so, normally you would retrieve the name of the service through the Active Session table. But we have encountered that sessions are not always logged in the Active Session table. The cause for this is still unknown, but assumed to be a bug, as the problem always relates to services with the Credential Type NavUserPassword. It "only" takes a restart of the service to achieve that the Active Sessionstable once again contains all sessions. 

As we could not count on the Active Session always containing the currently active session, we use GETURL, CONVERTSTR and SELECTSTR to obtain it:

LOCAL PROCEDURE GetServerInstanceFromUrl@1000000014();

VAR
  l_String@1000000002 : Text;
  l_ServerInstance@1000000000 : Text;
BEGIN
  l_String := GETURL(CLIENTTYPE::Windows);
  l_String := CONVERTSTR(l_String,'/:',',,');
  l_ServerInstance := SELECTSTR(6,l_String);
  MESSAGE('This is the name of the service: %1', l_ServerInstance);
END;