103
7.4 L
INKEDO
BJECTSWith Navision, you can create a table definition for a SQL Server object (user table, system table or view) that already exists in the current database.
Defining Linked Object Table Properties
You use the table property LinkedObject to link to SQL Server objects by changing the value to Yes when creating or modifying a table description in the table designer.
When you change this value to Yes, the LinkedInTransaction property becomes available.
The LinkedInTransaction property must be set to No when the Navision table description refers to a view that depends on objects that are outside the current database or on a linked server.
The LinkedInTransaction property allows you to read and modify data from linked server data sources, such as Excel, Access or another SQL Server. This access is not under Navision transaction control. This means that if a Navision transaction is aborted, any changes that were made during this transaction to a linked object that is outside the current database or on a linked server will remain in effect.
For information about linked sever data sources, see "Access to Objects in Other Databases or on Linked Servers" on page 106.
. . .
Note
You cannot run tables with the LinkedInTransaction property set to No when the
. . .
database has been set to single user mode.
Creating a Navision Table Description
The following descriptions illustrate the different kinds of Navision table descriptions that you can create, depending on the LinkedObject and LinkedInTransaction table property values. You must be a member of the db_owner fixed database role to create a table description.
To create a non-linked table:
· Set the value of the LinkedObject property to No.
· When you save this table, a SQL Server table that is owned by the db_owner fixed database role is created with the name you have specified (including the company name, if necessary).
· If an object with this name already exists, an error message is displayed and the table is not saved.
To create a linked object that is under transaction control:
· Set the LinkedObject property to Yes.
· Set the LinkedInTransaction property to Yes.
Chapter 7. Customizing and Maintaining Tables
104
· The table is saved without checking its validity. Navision will check that the corresponding SQL object exists and that it is compatible with the Navision table description when the table is accessed.
To create a linked object that is not under transaction control:
· Set the LinkedObject property to Yes.
· Set the LinkedInTransaction property to No.
· The table is saved without checking its validity. Navision will check that the corresponding SQL object exists and that it is compatible with the Navision table description when the table is accessed.
Deleting a Navision Table Description:
When the LinkedObject property is set to No:
· The SQL Server object is deleted if it is a user table.
· The SQL Server object is not deleted if it is a system table or a view. It can only be a system table or a view if it has been changed to one of these object types with the aid of an external tool. The LinkedObject property must be set to Yes in order to be able to link to a system table or a view.
When the LinkedObject property is set to Yes:
· The SQL Server object is not deleted.
This means that if you create a Navision table with the LinkedObject property set to No and then change it to Yes, its corresponding SQL Server object is not deleted.
When you modify the LinkedInTransaction property of a Navision table:
· All access to the linked SQL Server object will be made under or outside transaction control, depending on the setting you choose.
When you access data in a linked object:
· If the LinkedInTransaction property is set to Yes, all access to the linked object will be performed under transaction control – within Navision transactions.
· If the LinkedInTransaction property is set to No, all access to the linked object will be performed outside transaction control – independent of Navision transactions.
Requirements for Linking Objects
When you are using a linked object, you should take the following into account:
· The name of the SQL Server object that includes any company prefix and ($) separator must match exactly with the name of the Navision table.
· As is the case when creating regular Navision tables, you must be a member of the db_owner fixed database role in the current database.
7.4 Linked Objects
105
· As is the case with regular Navision 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. For more information about accessing objects outside the current database, see page 106.
· Navision will automatically grant the required SQL permissions on the object so that you can access it in the same way that regular Navision tables are accessed. It will then be subject to permissions assigned in the Navision security system.
· The object being linked must have a SQL Server table definition that is compatible with the Navision table definition.
· A view that cannot be updated in SQL Server (for example one containing computed/converted columns or unions) will also be read-only if it is used as a linked object from Navision. With SQL Server 2000, you can write Instead-Of triggers to define the logic that allows such a view to be updated. This logic is not part of Navision.
Rules Determining Compatibility
There are a number of rules that you need to keep in mind when you use linked objects:
· All columns in the object must be type compatible with those named in the Navision table definition. It is not necessary to name all the columns in the Navision table definition. For more information about type compatibility, see page 75.
· SumIndexFields cannot be defined for any object type.
· 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 primary key, and these columns must have the same names.
· If the object is a view or system table, a primary key must be defined, and any secondary keys may also be defined if required. These keys will only be used in Navision. 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 will order the data as though a primary key was physically defined. Navision relies on this uniqueness in order to correctly identify and order records.
· If the object is a view, it can have only one column of the SQL Server timestamp type, but it does not need to have any unless BLOB fields are present in the Navision table definition. A timestamp column must exist in a user table.
· An IDENTITY column can be used in a user table or a view, and Navision will ignore this column when inserting records into the table. This allows the IDENTITY column to be used as intended. Similarly, a computed column in a user table is also ignored. For a view, a column defined on a computed table column cannot be used if insert operations are required.
· You cannot link to a SQL Server temporary table.
Chapter 7. Customizing and Maintaining Tables
106
· Multilanguage views are not created or maintained for linked objects. For more information about multilanguage views, see the section “Creating and Maintaining Databases” in the manual Installation & System Management: Microsoft Business Solutions–Navision SQL Server Option.
Once an object has been linked, Navision treats it like a regular table. However, depending on the object type, SQL Server may prevent certain operations from taking place. For example, a non-updateable view cannot be updated in Navision, and a SQL Server error message appears if you attempt to do this. The ability to redesign the object from within Navision is limited, and these restrictions are described in the next section.
Redesigning the Navision Linked Object Table Definition
A Navision linked object table definition can be redesigned in accordance with the following rules:
· It cannot be renamed by changing the table definition name or the company name.
· No fields in the table definition can be renamed.
· New fields can be added providing they exist in the view, and existing fields can be deleted. In either case, the definition of the view in SQL Server is not changed.
· The primary and secondary key definitions can be changed. Also, new keys can be added, and existing keys can be deleted.
· The Navision field data types can be modified provided that the new type remains compatible with the column type in the view.
· A linked user table can undergo any design changes that are applicable to a regular table that is created from within Navision.
· If the DataPerCompany property of the Navision table definition is changed, it will result in an attempt to link to a new object. This new object will be based on the new company name. The previously linked SQL Server object will no longer be linked by the table definition.
· The LinkedObject table property can only be changed from Yes to No for a user table.
Access to Objects in Other Databases or on Linked Servers
You can access objects outside the current database or server from Navision by linking to an appropriately defined view in the current database. You can create a view definition outside of Navision 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 or a Microsoft Excel spreadsheet.
7.4 Linked Objects
107