I found this FAQ in standard Navision document, but I can’t make it work. “Is it possible to have access to Microsoft SQL Server tables and views that are used for other applications? Yes. You need to create a table description in C/SIDE which points to the table or view in SQL Server 2000.” Can anybody help me?
See below: 7.2 Linking SQL Objects #8729; Views and Navision Attain #8729; Requirements for Linking Objects #8729; Rules for Compatibility #8729; Redesigning Table Definitions #8729; Other Databases or Linked Servers Views and Navision Attain What is a view? As defined in SQL Server Books Online: “A view is a virtual table whose content are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.” With Navision Attain, you can create a table definition for a SQL Server object (user table, system table or view) that already exists in the current database. You can create a table definition in the same way that you create a regular Navision Attain table, and save it. If the name you give the table matches exactly with an existing object in the database, a message will appear, prompting you to either continue to save the table definition and link to the object, or to cancel the save. NOTE: Views cannot be created from within Navision Attain. Example - View In this example, a View is created in the CRONUS USA, Inc. database in the Enterprise Manager. The view includes two columns from the [CRONUS USA, Inc.$Item] table: No. and Description. An alias was created for the No. column as Number. Back in the Navision Attain Client, a table is created to match the views definition and saved with the same name. This creates a warning that gives you the option to link this table to the existing view or cancel the save. Because duplicate names are not allowed in the SQL Server database, you must link this table or rename it before you save it. Requirements for Linking Objects When you link to existing SQL Server objects, the following points should be noted: #8729; The name of the SQL Server object, including any company prefix and ($) separator, must match the exact name of the Navision Attain table. #8729; If the name of the table has the DataPerCompany property set to Yes, then it will include the company prefix. This prefix makes it necessary to ensure that this is the full name of the SQL Server object if the names are to match. #8729; As is the case when creating regular Navision Attain tables, you must be a member of db_owner fixed database role in the current database. #8729; As is the case with regular Navision Attain tables, the object must exist in the current database and be owned by a user in the database who is a member of the db_owner fixed database role. A SQL Server view can be used to access objects outside the current database (including those residing on separate servers) or owned by other users. #8729; Navision Attain will automatically grant the required permissions on the objects so that you can access it in the same way that regular Navision Attain tables are accessed. It will then be subject to permissions assigned in the Navision Attain security system. #8729; The object being linked must have a SQL Server table definition that is compatible with the Navision Attain table definition. Rules Determining Compatibility: Linked Object The object being linked must have a SQL Server table definition that is compatible with the Navision Attain table definition. This compatibility is determined by the following rules: #8729; All columns in the object must be type compatible with those named in the Navision Attain table definition. It is not necessary to name all the columns in the Navision Attain table definition. #8729; An identity column can be used in a user table or a view, and Navision Attain will ignore this column when inserting records into the table. This allows the identity column to be used as intended. #8729; Once an object has been linked, Navision Attain treats it like a regular table. However, depending on the object type, SQL Server may prevent certain operations from taking place. For example, a nonupdatable view cannot be updated in Navision Attain, and a SQL Server error message appears if you attempt to do this. Rules Determining Compatibility: Table Object #8729; If the object is a user table, it must have a primary key constraint that contains the same number of columns as those listed in the Navision Attain primary key, and these columns must have the same names. Secondary keys cannot be defined during the linking procedure, but they can be added later when you redesign the table. #8729; SumIndexFields cannot be defined for any object type. You can, however, redesign the table and add SumIndexFields for a user table object. #8729; A computed column in a user table is ignored. #8729; You cannot link to a temporary table Rules Determining Compatibility: View Object #8729; If the object is a view or system table, a primary key must be defined, and any secondary key may also be defined if required. These keys will only be used in Navision Attain. They will have no effect on a view, its underlying objects in SQL Server or on a system table. It is important that the data in the columns named in the primary key is unique. This will not be enforced as a physical constraint by the view or system table in SQL Server. However, Navision Attain will order the data as though a primary key was physically defined. Navision Attain relies on this uniqueness in order to correctly identify and order records. #8729; If the object is a view, it must have no more than one column of the SQL Server timestamp type, but it does not need to have any. A timestamp column must exist in a user table. #8729; For a view, a column defined on a computed table column cannot be used if insert operations are required. Redesigning the Navision Attain Table Definition of a Linked Object A Navision Attain table definition for a linked object may be redesigned in accordance with the following three independent rules: 1. A linked system table cannot be redesigned once it has been created. If the table definition is deleted, the linked system table is not affected and will remain in the database. 2. A linked view may undergo the following design changes: #8729; It can be renamed by changing the table definition name or the company name. #8729; New fields can be added, providing they exist in the view. Existing fields can be deleted. The definition of the view in SQL Server is not changed. #8729; The primary and secondary key definitions can be changed. Also, new keys can be added and existing keys can be deleted. #8729; The Navision Attain field data types can be modified provided the new type remains compatible with the column type in the view. #8729; If the table definition is deleted, the view is not affected and will remain in the database. 3. A linked user table may undergo any design changes that are applicable to a regular table created from within Navision Attain. If the table definition is deleted, the SQL Server table is also deleted from the database. For linked views and user tables, if the DataPerCompany property of the Navision Attain table definition is changed, it will result in an attempt to create a new object (or possibly link to a new object) based on the new table name or the company name/table name, whichever is appropriate. The previously linked object will no longer be linked by the table definition. Access to Other databases or Linked Servers You can access objects outside the current database or server from Navision Attain by linking to an appropriately defined view in the current database. You can create a view definition outside of Navision Attain that accesses data on SQL Server Linked Servers, which can access heterogeneous data sources. This could, for example, involve performing a join of an Oracle table, a Microsoft Access table and a Microsoft Excel spreadsheet. Prior to Navision Attain, you were not allowed to insert records into linked servers. To use such a view from a linked server within Navision Attain, you must be a member of the db_owner fixed database role in the current database. If the view contains a timestamp column, then this column must originate from a table within the same database in order for the timestamp column to be used for optimistic concurrency purposes in Navision Attain. Note: Data stored on linked servers cannot be updated from Navision Attain
Please check out this thread. http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=6571 PS this forum has a search feature, which you may find extremely useful. If you search SQL VIEW, you will find your answers.
Moved to SQL Forum.
Also the link above is now located at: http://dynamicsuser.net/forums/thread/46015.aspx