Using Linked Objects

  • Article

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 Microsoft Dynamics NAV table.

  • You must be a member of the db_owner fixed database role in the current database.

  • The service account of Microsoft Dynamics NAV Server must have permission to access the SQL database table. If accessing objects in other databases or on linked servers, such as a Microsoft Access database or Excel spreadsheet, then the service account must also have permission to access to these sources.

  • As is the case with regular Microsoft Dynamics NAV 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, see Accessing Objects in Other Databases or on Linked Servers.

  • Microsoft Dynamics NAV will automatically grant the required SQL Server permissions on the object so that you can access it in the same way that regular Microsoft Dynamics NAV tables are accessed. It will then be subject to permissions assigned in the Microsoft Dynamics NAV security system.

  • The object being linked must have a SQL Server table definition that is compatible with the Microsoft Dynamics NAV 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 Microsoft Dynamics NAV. 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 Microsoft Dynamics NAV.

Rules for Using Linked Objects

Object modification Rule

Column type

All columns in the object must be type compatible with those named in the Microsoft Dynamics NAV table definition. It is not necessary to name all the columns in the Microsoft Dynamics NAV table definition. For more information about type compatibility, see Identifiers, Data Types, and Data Formats.

SumIndexField technology

SumIndexFields cannot be defined for any object type.

View or system tables

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 Microsoft Dynamics NAV. 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, Microsoft Dynamics NAV will order the data as though a primary key was physically defined. Microsoft Dynamics NAV relies on this uniqueness in order to correctly identify and order records.

View objects

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 Microsoft Dynamics NAV table definition. A timestamp column must exist in a user table.

User table or view

An IDENTITY column can be used in a user table or a view. Microsoft Dynamics NAV 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.

Temporary table

You cannot link to a SQL Server temporary table.

Multilanguage views

Multilanguage views are not created or maintained for linked objects.

Redesigning a Linked Object Table Definition

After an object has been linked, Microsoft Dynamics NAV 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 Microsoft Dynamics NAV, and a SQL Server error message appears if you attempt to do this. The ability to redesign the object from within Microsoft Dynamics NAV is limited; these limitations are as follows:

  • The object 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 if 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 Microsoft Dynamics NAV 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 Microsoft Dynamics NAV.

  • If the DataPerCompany property of the Microsoft Dynamics NAV 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.

See Also

Concepts

Creating Table Definitions from SQL Server Objects (Linked Objects)