Dynamics 365 Dynamics NAV 2017 SQL SQL Server

Connect Dynamics NAV table with an External SQL Database

Let’s talk about Table type property of NAV tables. There’s a very interesting option in here, ExternalSQL. This option can help us sync a table in Nav with a  table in SQL granted they have a same structure.

Let’s see how we can set this up…

Pre-Requisites

  1. A copy of Nav 2017 ( might work on 16 as well)
  2. Development License
  3. Sql server ( Can be on-Premise or hosted , Just need the connection string and credentials)

 

Steps

  1. Identify the table you want to connect with a nav table. Here I am using a simple demotable with just 4 fields
  2. Create the table in Nav.
  3. Now go to table properties and Change the following properties as in Screenshot. The ExternalName should contain the name of the table in external database you want this table to sync with.

 

  1. You can also go to individual field property and set ExternalName property in case your nav field name differs from External sql one

 

  1. That’s all for the table. Now create a new codeunit in your range.
  2. Create a new function in it for SQL connection.
  3. Syntax

 

  1. IF HASTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName)
    
      THEN
    
        UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName);
    
    
    
    REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName,DatabaseConnectionString);
    
    SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL,DatabaseName);<span id="mce_marker" data-mce-type="bookmark" data-mce-fragment="1">​</span>

    You will need identify external database name here. You also need to define DatabaseConnectionString. It should be of the form

 

Server=<server>;Database=<database>;User ID=<userid>;Password=<password>;

 

  1. Next create a page for the nav table.

  1. Then Add code to run our connection codeunit in OnInit()

 

That’s all. Now if you create any record in Nav it gets updated to SQL table and vice versa.

6 comments

  1. Great article, Jaspreet!

    How about if you want to use the same table structure, say table RemoteCustomer to connect at different times to different NAV company Customer tables? Imagine this in multi-tenancy NAV.

    How can one programmatically change the ExternalName property, and say e.g. ExternalName=Company1$Customer or ExternalName=Company2$Customer or ExternalName=Company3$Customer

  2. How would you dynamically change property ExternalName using C/AL, to rely on the same structure at a time on different external connections, but access similar structured, but distinct, external tables? e.g. the Customer table in different companies and tenants?

    1. You mean ExternalName Property of the table? You would like to take customers from different companies in NAV and sync them to separate tables in the External DB?

      As far as I know , ExternalName cannot be set using C/AL.
      You can write logic to register different database connection depending upon the current company although I don’t think it helps with your requirement.

  3. Hi Jaspreet
    Just found this Blog
    I’m trying to connect to an external SQL via these functionality, but I get an error, saying that I haven’t got the permissions.

    When I read this discussion on MIBUSO then “https://forum.mibuso.com/discussion/67072/connecting-nav-2016-to-an-external-sql-database” it seems that the system uses the NAV Service User iD and not the one specified in the parameter.
    How did you solve that?
    Regards
    Mads Morre

    1. Hi Mads,
      I did pass username and password in my Database connection string and it was a externally hosted SQL server.
      So I can confirm to you that it is using the username and password specified in the connection string.

      You can try to get a free sql server from https://appharbor.com/addons/sqlserver to test this functionality. It was fairly straightforward.

Leave a Reply

Your email address will not be published. Required fields are marked *