LinkedObject - SQL Views - Error when adding a new field

AngelaMills
AngelaMills Member Posts: 29
edited 2009-07-26 in NAV/Navision Classic Client
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

Comments

  • davmac1
    davmac1 Member Posts: 1,283
    Are you running multiple companies with data per company = no?
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • AngelaMills
    AngelaMills Member Posts: 29
    I changed it to "no" and now I get the following error: Table Data x does not exist.
  • nunomaia
    nunomaia Member Posts: 1,153
    Did you check if the user has permission to execute view ?
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • AngelaMills
    AngelaMills Member Posts: 29
    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
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • AngelaMills
    AngelaMills Member Posts: 29
    Is "Common to all companies" the same as "DataPerCompany"?
  • nunomaia
    nunomaia Member Posts: 1,153
    AngelaMills wrote:
    Is "Common to all companies" the same as "DataPerCompany"?

    Yes
    Nuno Maia

    Freelance Dynamics AX
    Blog : http://axnmaia.wordpress.com/
  • davmac1
    davmac1 Member Posts: 1,283
    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.
    David Machanick
    http://mibuso.com/blogs/davidmachanick/
  • AngelaMills
    AngelaMills Member Posts: 29
    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
    kine Member Posts: 12,562
    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... ;-)
    Kamil Sacek
    MVP - Dynamics NAV
    My BLOG
    NAVERTICA a.s.
  • AngelaMills
    AngelaMills Member Posts: 29
    Thank you. This has been very helpful.

Categories