Problem with linked table/view

bbrown

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?

rhpnt

Did you add the field to a NAV table directly through SQL mgmt. studio?

bbrown

How could I do that? There is no NAV table visible via SQL. It's a linked table.

rhpnt

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?

bbrown

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.

bbrown

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.

bbrown

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.

rhpnt

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.

bbrown

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.

rhpnt

Wow, didn't know that! Thanks for the info.

DenSter

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.

stryk

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?

bbrown

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.