TableType::ExternalSQL

One very cool feature in NAV2016 is the table type “ExternalSQL”. In older versions you had to use a view in SQL for each table and set the property LinkedObject. Now you don’t have to do that any more.

Let’s take a look at it:

table

Here you can see how you can use TableType, ExternalName and ExternalSchema instead of LinkedObject. In NAV this table is called “My Table” but in SQL it’s called “mytable”.

You can also set ExternalName for fields. Below I want to use the field name “No.” although it’s called “number” in the SQL table.

field

 

The connection to the server, database and the user credentials is controlled with C/AL.

Here’s a sample:

UNREGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

REGISTERTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName', 'Data Source='MyServerName';Initial Catalog='MyDatabaseName';User ID='MySQLUserID';Password='MyPassword');

SETDEFAULTTABLECONNECTION(TABLECONNECTIONTYPE::ExternalSQL, 'MyDatabaseName');

This code will first try to unregister any existing connection to the database I want to connect to. Then it will register using a connectionstring (which you of course shouldn’t hard code like this) and then it will set the connection as the default connection.

The connection needs to be established on each login so it would make sense to store server name, database name and credentials in a setup table and register the datasource on login.

According to the MSDN documentation any rollbacks will also take place in the external tables. I haven’t tested that myself though.

I’m on my first project using this feature so I’ll probably update this post later on when I’ve got some more time on this functionality.

8 Responses to “TableType::ExternalSQL”

  1. Michael 2016-06-06 at 13:37 #

    Hello,

    I think this a great Feature. But how do you deal with datatypes, that doesn’t exists in Dynamics NAV? Such as SQL-datatype “bit”

    Greetings

    • Lars Westman 2016-06-21 at 16:11 #

      Sorry for super late reply, but as far as I know you can only use datatapes supported in NAV. But you should be able to cast unsupported datataypes in a view in SQL and then use that view instead of a table.

  2. rj 2016-06-21 at 16:02 #

    Dear Lars,

    is it possible to create a page (list) based on ExternalSQL without creating a table (pulling ID) before?
    The requirement ist to create a page on XML (maybe XML->SQL->Page).

    Many Thanks

    • Lars Westman 2016-06-21 at 16:08 #

      Hi.

      You need a table declared as ExternalType=SQL. On that table you can then create a page.

  3. Moiz 2017-07-14 at 02:05 #

    Hi Lars

    The tutorial was amazing, Thanks for the help.
    I am trying to connect to the external database using the UserID for the RTC client, as the customer I am working for is a bit skeptical, and wants to handle the permissions on the Database not on NAV.

    • Lars Westman 2017-08-14 at 11:34 #

      Hi.

      Sorry for the late reply. Summer vacation here in Sweden….

      Since you specify the connection string you are free to connect to the external database as you wish. You can use a common login that you store in a setup table or use the current users login. That way you setup permissions as usual on the external databasse you are connecting to.

      /Lars

  4. Mads Morre 2017-12-06 at 13:02 #

    Hi Lars
    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

    • Lars Westman 2017-12-06 at 13:27 #

      Hmm. I didn’t have a problem with that so there was nothing to solve for me…

      The service account for the NAV server does not have a login on the SQL in my case. It’s only the user specified in the connection string who has. That user are mapped to the roles public, datareader and datawriter in the NAV database.

      In this case we are using the NAV build 9.0.45243.0

Leave a Reply