Dynamics Community Forum Thread Details

Error trying to access table from registered ExternalSQL database

Hi,

I've been able to connect to my external SQL database, which is on a completely different server. I've followed the documentation and searched a lot online for the same problem but haven't found anything, that's why I'm posting this question.

Documentation: https://docs.microsoft.com/en-us/dynamics-nav/external-tables

I've made a seperate table in the database to test this, which only contains one column named "Number", which is an INTEGER.

I've also made a companion table like so:

table 50125 Dynamics365TestTable //Already the name of the ExternalSQL table
{
    TableType = ExternalSQL;
    ExternalSchema = 'dbo';

    fields
    {
        field(1; Number; Integer) //Already the name of the ExternalSQL column
        {

        }
    }

    keys
    {
        key(PK; Number)
        {
        }
    }
}

After this I made a list page to display the data:

page 50121 "Data List"
{
    PageType = List;

    SourceTable = Dynamics365TestTable;

    layout
    {
        area(content)
        {
            repeater(Group)
            {
                field(Number; Number)
                {
                    ApplicationArea = All;
                }
            }
        }
    }

    trigger OnInit(); //Replaced actual IP and password with <FillInHere>
    begin
        if HasTableConnection(TableConnectionType::ExternalSQL, 'MDBC') then
            UnregisterTableConnection(TableConnectionType::ExternalSQL, 'MDBC');
        RegisterTableConnection(TableConnectionType::ExternalSQL, 'MDBC', 'data source=<DatabaseIP>;initial catalog=Pod_Demo;user id=sa;password=<password>;');
        SetDefaultTableConnection(TableConnectionType::ExternalSQL, 'MDBC', false);
    end;
}
TI am able to connect to the database, since it first gave me an error saying the database isn't registered, because I used a different IP. I fixed this, and now it gives me this error:


Invalid Object name
'Pod_Demo.dbo.Dynamics365TestTable$b2807498-6b2e-4989-b893-d3ac50327502'. 
Statement(s) could not be prepared.

The funky numbers behind the ($) operator of course being the application ID.

In SSMS doing: "SELECT * FROM Pod_Demo.dbo.Dynamics365TestTable" would give me the data.

Does anybody have any idea how to fix this or at least could point me in some direction?

Thanks in advance.

Quick Links

Leaderboard