How To Connect An External Database To Business Central

In this post I would like to share the way you can connect an external SQL Server database to Business Central. This way you can check historic data from an older versions of Nav, retrieve data from other databases and work with them as if you had a Business Central table.

Create the table to connect

[UPDATE] The method described below used to work for On-Prem environments only, not for cloud/Saas. But, at this moment it’s not recommended, and an alternative solution should be considered.

For example, use HTTP requests to connect to external APIs.

Or learn how to expose Business Central APIs for other services to connect to it.

The old post continues here.

First of all, we need to create a table in Business Central. As an example, we will connect to an older version of Nav and get the customers from the database.

The table type will be ‘ExternalSQL’ and the external name will be the same that it´s in SQL Server (Company Name + Table Name).

Add the fields you wish to retrieve from the Sql table.

This step is quite delicate, you need to have the same data types between SQL Server and Business central fields. Otherwise, the system will display an error and the connection won´t be stablished. (varchar = text or code, unique identifier = guid, and so on).

The names of the fields can differ from the ones of Sql but if you do so, then, the field property ‘ExternalName’ must be filled.

An example of a table would look like this.

Note that if there is no primary key, BC will assing it to the first field.

Create the connection

First, you need to identify the Sql server, instance (if local server) and database name you will connect to:

Then, if you will login with Windows authentication or username and password. With this information create the connection string:

//Windows Authentication:
'Data Source=<DatabaseServer>\<DatabaseServerInstance>;Initial Catalog=<DatabaseName>;Integrated Security=SSPI;'

//Sql Authentication:
'Data Source=<DatabaseServer>\<DatabaseServerInstance>;Initial Catalog=<DatabaseName>;User ID=<username>;Password=<password>'

Finally, build the connection. The structure of the connection will always be like the following, only changing the name of your database and the connection string.

Create the page

To verify that the connection is succesful we will create a page with the information of the external table. On the ‘OnInit’ trigger you need to place the connection code.

Example:

page 50100 "CustomerExtSQL"
{

    ApplicationArea = All;
    Caption = 'Customer Ext. SQL';
    PageType = List;
    SourceTable = CustomerExtSQLGEN;
    UsageCategory = Lists;

    layout
    {
        area(content)
        {
            repeater(General)
            {
                field(No_; Rec.No_)
                {
                    ToolTip = 'Specifies the value of the  No_ field';
                    ApplicationArea = All;
                }
                field(Name; Rec.Name)
                {
                    ToolTip = 'Specifies the value of the Name field';
                    ApplicationArea = All;
                }
            }
        }
    }

    trigger OnInit()
    var
        DatabaseName: Text;
    begin
        DatabaseName := 'Demo Database NAV (8-0)';

        if HasTableConnection(TableConnectionType::ExternalSQL, DatabaseName) then
            UnregisterTableConnection(TableConnectionType::ExternalSQL, DatabaseName);

        RegisterTableConnection(TableConnectionType::ExternalSQL, DatabaseName, 'Data Source=IT-686\NAVDEMO2;Initial Catalog=Demo Database NAV (8-0);Integrated Security=SSPI;');

        SetDefaultTableConnection(TableConnectionType::ExternalSQL, DatabaseName);
    end;

}

Run the page in the web client, the connection will be stablished and you will see the data from Sql:

Note that the connection is bidirectional. If you modify the data from Business Central the external database will be modified as well.

That´s all hope you find it useful.

Check this other article on how to boost performance in Business Central.