Problem with linked table/view

bbrownbbrown Posts: 3,019Member
edited 2011-03-04 in SQL General
I have a linked table that's been working fine for a few years. I've now added a field (to the view and table) and am getting several "the multi-part identifier XXX could not be bound" errors when I run the table in NAV. But the related view runs fine in SQL.

Thoughts?
There are no bugs - only undocumented features.

Answers

  • rhpntrhpnt Posts: 688Member
    Did you add the field to a NAV table directly through SQL mgmt. studio?
  • bbrownbbrown Posts: 3,019Member
    How could I do that? There is no NAV table visible via SQL. It's a linked table.
    There are no bugs - only undocumented features.
  • rhpntrhpnt Posts: 688Member
    bbrown wrote:
    How could I do that? There is no NAV table visible via SQL. It's a linked table.

    All NAV tables are visible and editable in SSMS. How did you create the View then?
  • bbrownbbrown Posts: 3,019Member
    It's a view, as in a view to data in another system, not a table.

    I changed the view by doing a DROP and CREATE in SQL.

    Then I opened the table object in NAV Object Designer, added the new field, and save/compiled. View runs fine in SQL but give error in NAV. And not just on the field I added.
    There are no bugs - only undocumented features.
  • bbrownbbrown Posts: 3,019Member
    It's a problem with my views. I found a section that if I pull it out things work fine. The views are combining data between NAV and an external database. I've got to step back and get my head around the issue.

    Just strange that it works fine in SQL. But then again, it's NAV. Wouldn't be any fun if it was easy.
    There are no bugs - only undocumented features.
  • bbrownbbrown Posts: 3,019Member
    Problem solved.

    The view is joining data between NAV and an external database. I had not accounted for the possibility of NULL values in the external database for the new field.

    Thanks for your input.
    There are no bugs - only undocumented features.
  • rhpntrhpnt Posts: 688Member
    bbrown wrote:
    It's a view, as in a view to data in another system, not a table.

    ???

    Is it something I don't know about NAV or is someone standing on my connection cable? Seriously, I'd really like to know.
  • bbrownbbrown Posts: 3,019Member
    rhpnt wrote:
    bbrown wrote:
    It's a view, as in a view to data in another system, not a table.

    ???

    Is it something I don't know about NAV or is someone standing on my connection cable? Seriously, I'd really like to know.

    A Linked Table is a NAV table object that is connected to a SQL view rather than an actual table. This is a feature of the SQL version of NAV. Information details can be found in the Application Developers Guide. There are also several post on this board. Have a look and feel free to ask questions.
    There are no bugs - only undocumented features.
  • rhpntrhpnt Posts: 688Member
    Wow, didn't know that! Thanks for the info.
  • DenSterDenSter Posts: 8,047Member
    bbrown wrote:
    A Linked Table is a NAV table object that is connected to a SQL view rather than an actual table.
    Actually it can be a view or a table.

    In a nutshell: you create the SQL Server object (view/table) first, making sure data types are compatible with NAV data types (for instance the NAV data type Text is NOT compatible with the SQL Server data type Text). Then you create the table object in NAV, with the same table name and field names (beware of multi-company table names), set it to be a linked table, and NAV will look for a view/table on SQL Server to link to.
  • strykstryk Posts: 645Member
    bbrown wrote:
    I have a linked table that's been working fine for a few years. I've now added a field (to the view and table) and am getting several "the multi-part identifier XXX could not be bound" errors when I run the table in NAV. But the related view runs fine in SQL.

    Thoughts?

    If I don't err, this error occurs e.g. if you join two tables which both have a field of the same name, for example the field "No_" from "Sales Header" and "Sales Line". In such a case you have to specify the full qualified name; e.g. like:

    SELECT sh."No_", sl."No_", ... FROM "Sales Header" sh JOIN "Sales Line" sl ON ...

    Could this be the problem?
    Jörg A. Stryk (MVP - Dynamics NAV)
    NAV/SQL Performance Optimization & Troubleshooting
    STRYK System Improvement
    The Blog - The Book - The Tool
  • bbrownbbrown Posts: 3,019Member
    stryk wrote:
    If I don't err, this error occurs e.g. if you join two tables which both have a field of the same name, for example the field "No_" from "Sales Header" and "Sales Line". In such a case you have to specify the full qualified name; e.g. like:

    SELECT sh."No_", sl."No_", ... FROM "Sales Header" sh JOIN "Sales Line" sl ON ...

    Could this be the problem?

    That was my first thought. This is a somewhat complex view. It's actually a view or a view of a view. It's retreiving, joining, and summarizing data from about 20 or so tables between NAV and an external system. One of my first analysis steps was to sit down and review my SQL statement to see if I had missed something like this.

    I then started working back to see which statement actually caused the error. Once I have that I then started removing joins till I found the one causing the issue.
    There are no bugs - only undocumented features.
Sign In or Register to comment.