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:
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.
//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
//Define the table to query and the query itself (hardcoded for simplicity)
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
//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'));
//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)));
//Always close connection again
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:
Download the CodeUnit 'GetDeploymentInfo' here:
The CodeUnit contains the following 7 functions:
- GetSQLApplicationDatabaseServerName //only relevant if multitenancy
- GetSQLApplicationDatabaseName //only relevant if multitenancy
Here is an extract of the CodeUnit:
//Returns the server of the SQL tenant database (both for single- and multitenancy).
//Returns the name of the SQL tenant database (both for single- and multitenancy).
InstantiateObjectToRetrieveSQLTenantDatabaseInfo(VARNavTenantRuntimeSettings : DotNet"Microsoft.Dynamics.Nav.Types.NavTenantRuntimeSettings")
//Instantiate object and get info from NavTenantRuntimeSettings based on TenantID
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: (jpg)
The Messages area informs of the number of records for each company:
The query can easily be altered to search other tables and other white spaces (by default, it searches for char(10) and char(13)).