LinkedObject - SQL Views - Error when adding a new field

AngelaMills

I created a view in MS SQL 2008 and then created a NAV table with the LinkedObject set to yes (has all the same field names and table name as the SQL view). Everything worked great. However, when I added a new field to the SQL view and then added the same field to the NAV table, I get the following error..
"The x table does not exist as the required object name of x in this database". Is it possible to add a new field or do I have to start all over? Thanks for the help. Angela

davmac1

Are you running multiple companies with data per company = no?

AngelaMills

I changed it to "no" and now I get the following error: Table Data x does not exist.

nunomaia

Did you check if the user has permission to execute view ?

AngelaMills

I am the user and I have administrator rights for both NAV and MS SQL. I was able to execute the view from SQL. Do you have any other thoughts or ideas? thanks for the help.

David_Singleton

Generally unless there is a specific reason otherwise, its best to set "common to all companies" to YES.

Adding a new field the way you mention is definitely possible, but it just needs one small mistake for it not to work. Me I have a couple of ways of doing this. If the SQL table already exists, you can create the script, and use a text editor to build the Navision fields that way. Or if its all from scratch you can just create the table in Navision, then create the SQL script, delete the Navision table, and then re-import it as a linked table. Also you can create the table in Navision, copy it to excel and then create the SQL script from there. I do it these ways, because it just seems every time I have an issue like yours its actually just some silly typo.

AngelaMills

Is "Common to all companies" the same as "DataPerCompany"?

nunomaia

AngelaMills wrote:

Is "Common to all companies" the same as "DataPerCompany"?

Yes

davmac1

The reason I asked the question is, if you have multiple companies with data percompany = yes, you need multiple views and if you add a field to one, you have to add it to all the views.

AngelaMills

Thank you for the update. That makes sense. I only created one SQL view for the company I wanted a NAV table. In the demo database, there are three NAV companies. Do I need to create 3 SQL views for each company even though I only want the view for one company? thanks

kine

AngelaMills wrote:

Thank you for the update. That makes sense. I only created one SQL view for the company I wanted a NAV table. In the demo database, there are three NAV companies. Do I need to create 3 SQL views for each company even though I only want the view for one company? thanks

Yes, you need to create the view for all companies. It must be handled like any other table in NAV... ;-)

AngelaMills

Thank you. This has been very helpful.