Get SQL Server Name from code in Dynamics NAV 2013

19 Jun

Sometimes it is better to use SQL code instead of C/AL code to perform bulky operations. The reason is obvious: performance. Using SQL code is much faster. The upgrade procedure for Dynamics NAV 2013 uses SQL code to perform the upgrade on the Dimensions.

When using SQL code, you need to find the SQL Server Name. Currently, there is no way to find the SQL Server Name in NAV 2013. There are some alternative ways, as is explained here: http://www.dynamics.is/?p=1427

If you have gone through the standard upgrade procedure for Dynamics NAV 2013 you probably noticed Codeunit 104050 Upgrade – SQL Mgt. One of the functions in this Codeunit is to find the current SQL Server Name and Database Name. The Database Name is read correctly from the “SessionEvent” table (there is a alternative way, check the code in procedure GetDatabaseIndicatorText in Table 79 Company Information). However, the SQL Server Name is incorrect.

How do you mean, incorrect? Well, the SQL Server Name is read from the “Server Computer Name” field in the “ActiveSession” virtual table. But this is not the SQL Server Name it is the Dynamics NAV Server name!

In case the NAV Server and SQL Server resides on the same computer, this is no problem. But every developer will agree that you cannot trust that your code will always run on such environment. Although the method on Gunnar’s blog is a good method, I want to show an alternative method (with less code).

This method uses the .Net assemblies of the Dynamics NAV Server. In this case the assembly Microsoft.Dynamics.Nav.Ncl.dll.

How is it done?

Attached is a download of a simple Visual Studio project with the needed code (literally just one line of code to get the server name!). Compile it and copy the dll to the Add-ins folder of the Dynamics NAV server and the client (only for development machines).

using Microsoft.Dynamics.Nav.Runtime;

public static class MyNavDatabase
{
    public static string ServerName
    {
        get { return NavCurrentThread.Connection.Database.ServerName; }
    }
    public static string DatabaseName
    {
        get { return NavCurrentThread.Connection.Database.DatabaseName; }
    }
}

In NAV this assembly can be used as follows:

SNAGHTML3bf2a0cb

In this code snippet the GetServerName1 finds the SQL Server Name the same as the upgrade toolkit does. And GetServerName2 uses the .Net sample project.

image

In the message you can see the difference:

image

If a named instance is used, then the instance name will be included as well, in the format server\instance.

Download the Visual Studio project here: NavDatabaseSample

5 thoughts on “Get SQL Server Name from code in Dynamics NAV 2013

  1. Hi,

    The method you point to on my blog is obsolete for NAV 2013. I wrote a new blog entry with code that works for NAV 2013. The problem with the older code and the upgrade toolkit from Microsoft is that it does not check for the database instance name.

    Best regards,
    Gunnar Þór
    http://www.dynamics.is

  2. Hi Gunnar,

    Thanks for the update, I have updated it in the post. I already realized that I mentioned the code for NAV 2009 since the filepath structure is different. But that was after I posted it. Anyway, NAV 2013 requires much more code, doesn’t it?

    If an SQL server instance is used, than the ServerName method includes the instance in the format server\instance.

    Thanks,
    Arend-Jan

  3. You can also check Codeunit 9500 – Debugger Management

    ServerComputerName := Config.GetStringSetting(‘Server’);

    Where Config is a dotnet variable with SubType Microsoft.Dynamics.Nav.Types.ConfigSettings.’Microsoft.Dynamics.Nav.Types, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35′

  4. Hi Mohana,

    Unfortunately, the Config doesn’t give the SQL Server name, but the Dynamics NAV Server name. And the ServerInstance as used in Codeunit 9500 returns not the instance that I’m running on but a different one (don’t know why).

    And if you try to read the Config settings like DatabaseServer and DatabaseInstance, then you will get empty strings. So the Config variable seems to not exactly represent the config file.

    Arend-Jan

  5. Pingback: Get SQL Server Name from code in Dynamics NAV 2013 | Pardaan.com

Leave a Reply

Your email address will not be published.