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.
0
Answers
All NAV tables are visible and editable in SSMS. How did you create the View then?
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.
Just strange that it works fine in SQL. But then again, it's NAV. Wouldn't be any fun if it was easy.
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.
???
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.
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.
RIS Plus, LLC
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?
NAV/SQL Performance Optimization & Troubleshooting
STRYK System Improvement
The Blog - The Book - The Tool
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.