I DENTIFIERS , D ATA T YPES AND D ATA F ORMATS IN THE SQL S ERVER O PTION FOR N AVISION

Naming Identifiers

Identifiers for SQL Server tables and columns are based upon the table names and field names for the corresponding tables and fields of a Navision table definition. If you set a table’s DataPerCompany property to Yes, the SQL Server table name is prefixed by the company name. The two names are separated by the ($) symbol. For example, the SQL Server table name for the Customer table of the CRONUS International Ltd.

company is CRONUS International Ltd_$Customer. If the DataPerCompany property of a table is set to No, there is no prefix.

The primary key of a Navision table is created in a SQL Server table as a primary key constraint. The name of the primary key will be based on the table name with a suffix of $0, for example, CRONUS International Lt_$Customer$0. Any secondary keys in a Navision table that must be created and maintained in SQL Server – the

MaintainSQLIndex key property is set to Yes – will have SQL Server indexes created that are named after an internal key ID with a $ prefix. Examples of this are $1 and $4.

If the database maintains SQL views for language IDs, the system creates a SQL view by prefixing the SQL Server table name with the Windows language ID. For example, if you want to refer to the Customer table in the CRONUS International Ltd. company in German (Standard), the SQL view is DEU$CRONUS International Ltd_$Customer.

For more information about multilanguage functionality, see Chapter 18.

If the database maintains relationships, the system creates foreign key constraints using the SQL Server table name and TableRelation property information. The names of the constraints have the following format: <table name>$FK$T<referencing table ID>_F<referencing field ID>$T<referenced table ID>. Here is an example using the Customer table: CRONUS International Ltd_$Customer$FK$T18_F107$T308.

When you create a Navision table with keys that contain SumIndexFields^®, this causes additional tables to be created in SQL Server to support the SIFT™

functionality. These tables are named after the company, the table ID and an internal key ID. For example, the SIFT table name for SumIndexFields of the key (G/L AccountNo.,Posting Date) in the G/L Entry table in CRONUS International Ltd. is CRONUS International Ltd_$17$0.

. . .

If you create a Navision table with keys that contain SumIndexFields, you must not give the table the same name as its ID. SIFT tables whose names are the same as

. . .

their ID cannot be saved. If you try to do so, you will receive an error message.

3.3 Identifiers, Data Types and Data Formats in the SQL Server Option for Navision

Representation of Navision Data Types

Every available Navision data type is mapped to an appropriate SQL Server data type in the tables of the SQL Server Option for Navision. The following table shows which SQL Server data type is used for the corresponding Navision data type:

Each of the SQL Server data types is created as NOT NULL except the IMAGE type, which allows NULL.

Compatibility of Data Types

Some of the SQL Server data types listed previously are compatible with other Navision data types. The following table shows the extended compatibility of SQL

Navision

Data Format Considerations

When you are using the SQL Server Option for Navision, you must be aware of the effect the data formats will have on the way your data is compared and sorted.

Code Fields

In the SQL Server Option for Navision, code fields can be represented by several SQL Server data types.

Code fields have a property, SQL Data Type, that determines whether they contain integers, text strings or a mixture of both. You set this property in the following way:

1 Click Tools, Object Designer.

2 Click Table and select the appropriate table.

3 Click Design.

4 Select the field whose data type is defined as code and then click View, Properties.

The Properties window for that field appears:

NCHAR(n) Text(n)

3.3 Identifiers, Data Types and Data Formats in the SQL Server Option for Navision

You can set the SQL Data Type property to Varchar, Integer or Variant. Leaving the value as Undefined is the same as selecting Varchar, which is the default value.

When you create a table in the SQL Server Option for Navision, the code field data is stored in VARCHAR, INTEGER or SQL_VARIANT columns in the SQL Server table that correspond to the SQL Data Type property’s values Varchar, Integer or Variant.

When you set the value of the SQL Data Type property of a code field to Varchar:

· All the values in the field are compared and sorted as character data, including numeric values.

When you set the value of the SQL Data Type property of a code field to Integer:

· All the values in the field are compared and sorted as integers. No alphanumeric values can be stored in the field.

· If you enter negative values in the column outside Navision using external tools, they cannot be read into Navision.

· The value "0"(zero) is used to represent an empty string in Navision.

· Non-numeric code values or any numeric values beginning with "0"(zero) cannot be entered in the code field.

When you set the value of the SQL Data Type property of a code field to Variant:

· The values in the field are compared and sorted according to their base data type.

Numeric values are sorted after alphanumeric values.

· Data that is entered into the code field in Navision is stored as either the VARCHAR or INTEGER base data type, depending on the value that has been entered.

· Any value beginning with "0"(zero) can be entered in the code field and is stored as an INTEGER base data type.

Date and Time Fields

SQL Server stores information about both date and time in columns of the DATETIME and SMALLDATETIME types. For date fields, Navision uses only the date part and places a constant value for the time. For a normal date, this contains 00:00:00:000.

For a closing date, it contains 23:59:59:000 for a DATETIME and 23:59:00:000 for a SMALLDATETIME.

The Navision undefined date is represented by the earliest valid date in SQL Server:

01-01-1753 00:00:00:000 for a DATETIME, and 01-01-1900 00:00:00:000 for a SMALLDATETIME.

For time fields, only a SQL Server DATETIME type can be used. Navision uses only the time part and places a constant value for the date: 01-01-1754. The Navision undefined time is represented by the same value as an undefined date.

In order for Navision to interpret date and time values correctly, the formats mentioned above must be used when linking Navision table definitions to external tables or views. For more information about this, see page 66.

To reformat a DATETIME or SMALLDATETIME column that is to be used as a date field in Navision, an UPDATE statement can be applied to the table data. Here is an example of such an update statement:

UPDATE [My Table] SET [My Date] = CONVERT(CHAR(10), [My Date], 102)

For a closing date, a CONVERT style of 120 can be used to set the appropriate time part. To reformat a time field, a similar statement can be used:

UPDATE [My Table] SET [My Time] = CAST('1754-01-01 '+CONVERT(CHAR(8), [My Time], 108) AS DATETIME)

As an alternative to modifying the table data, you can create a view that applies the necessary conversion to the column and gives the column an alias. However, you cannot update views that are created in this way and it is more efficient to change the data than to apply conversions for every row.

. . .

The information in this section only applies to fields of the Date and Time data type

. . .

and does not apply to fields of the DateTime data type.

Accessing Navision Tables with External Tools

You can access data in Navision tables with external tools, such as Microsoft

Enterprise Manager. When you do this, the values in fields that contain the code, date and time data types and which have a specific format must be manipulated correctly for data modification or comparison. When you use external tools, no special processing of code field data is required to join fields in different tables provided that you use the same SQL data type value for each code field in a join or CAST the value to the appropriate data type.

3.3 Identifiers, Data Types and Data Formats in the SQL Server Option for Navision

Multilanguage Views In the New Database and Alter Database windows, you can select to maintain SQL views. If you enable this option, SQL Server will create and maintain a view for each language ID that is added to a table in Navision. The system creates a SQL view by prefixing the SQL Server table name with the Windows language ID for each CaptionML value.

This means that external tools can use a view of the object in the user’s language, for example Spanish, rather than the object name. The object name could be in an other language, for example English (United States).

The view is updated by every change in the CaptionML values of a table. For more information about multilanguage, see Multilanguage Functionality on page 369.