Navision Stat udviklingsblog

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.

Introduction

The Navision Stat Development Team resides at the Agency for Public Finance and Management, as part of the Ministry of Finance in Denmark. We support and develop Microsoft Dynamics NAV2016CU12 supporting multitenancy and releasing updates monthly to all governmental organisations.

This blog contains walkthroughs, 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

• Execute SQL query from NAV
• How to retrieve name of NAV database, server, service etc.
• How to identify white spaces with SQL
• How to work with multiple NAV versions
• Prevent NAV 2013r2 from starting a new client session when running an object
• From multitenancy to singletenancy
• Delete NAV users with SQL
• SMTP - Files are created but are not sent
• Convert data in all NAV companies
• Find name of NAV server (service) using GETURL

---

 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.

• Download the .fob file of the object (fob)
• Download the .txt file of the object (txt)

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;}