mibuso.com

Microsoft Business Solutions online community
It is currently Sat Jul 26, 2014 7:05 am

All times are UTC + 1 hour [ DST ]




Post new topic Reply to topic  [ 13 posts ] 
Author Message
 Post subject: [Solved] Problem with linked table/view
PostPosted: Thu Mar 03, 2011 4:28 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Last edited by bbrown on Thu Mar 03, 2011 6:04 pm, edited 1 time in total.

Top
 Profile E-mail  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Thu Mar 03, 2011 5:06 pm 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
Did you add the field to a NAV table directly through SQL mgmt. studio?


Top
 Profile  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Thu Mar 03, 2011 5:15 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Thu Mar 03, 2011 5:23 pm 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
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?


Top
 Profile  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Thu Mar 03, 2011 5:26 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Thu Mar 03, 2011 5:40 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Thu Mar 03, 2011 6:04 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Fri Mar 04, 2011 10:08 am 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
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.


Top
 Profile  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Fri Mar 04, 2011 1:22 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Fri Mar 04, 2011 2:28 pm 
Offline

Joined: Mon Jul 28, 2008 2:37 pm
Posts: 674
Location: AT
Country: Austria (at)
Wow, didn't know that! Thanks for the info.


Top
 Profile  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Fri Mar 04, 2011 3:26 pm 
Offline
MVP Microsoft Dynamics NAV

Joined: Thu Jan 02, 2003 6:37 pm
Posts: 7989
Location: Howell, MI
Country: United States (us)
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.

_________________
Daniel Rimmelzwaan
MVP - Dynamics NAV


Top
 Profile  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Fri Mar 04, 2011 3:43 pm 
Offline
MVP Microsoft Dynamics NAV
NAV TechDays 2014 attendee

Joined: Thu Jul 31, 2003 12:00 pm
Posts: 640
Location: Nürnberg
Country: Germany (de)
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


Top
 Profile E-mail WWW  
 
 Post subject: Re: Problem with linked table/view
PostPosted: Fri Mar 04, 2011 4:39 pm 
Offline

Joined: Tue Jun 07, 2005 5:24 pm
Posts: 2815
Location: MA
Country: United States (us)
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.


Top
 Profile E-mail  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 13 posts ] 

All times are UTC + 1 hour [ DST ]


Who is online

Users browsing this forum: No registered users and 1 guest


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum


Search for:
Jump to: