Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics NAV forum
Unanswered

Error trying to access table from registered ExternalSQL database

Posted on by Microsoft Employee

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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,532 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,501 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans