mmdb.ini file for MS SQL databases

MS SQL databases settings for Aurea CRM.

Below are some of the mmdb.ini file for MS SQL database:

[MM SQL]

MULTIDBS=ON

If this entry is set to "ON", the parameters are first read from this section and applied. Otherwise, the parameters are read from the [MM ODBC MSS] section.

[MM ODBC MSS]

LOGINID=MM

This entry contains the name of the database user.

PASSWORD=MM

This entry contains the password of the database user. You can use MM_PWD_FILE or PWD_TO_ENCRYPT to encrypt this password (see below).

DATASOURCE=MMDB

This entry contains the name of the data source on the server.

DATABASE=UPDATE7

Assign the name of the MS SQL database used to add Aurea CRM tables to this parameter. This setting overwrites the default database in the ODBC definition. If the database entered in this parameter is not present on the MS SQL sever, the default database is used.

CONNECTION=Driver=SQL Server ;Server =mmdb;Database=UPDATE7;UID=mm;PWD=MM;

Use this parameter to specify the database connection as an ODBC connection string (maximum length: 1024 characters).

Use CONNECTION instead of

LOGINID=mm

PASSWORD=MM

DATASOURCE=MMDB

DATABASE=UPDATE7

Any installed ODBC driver can be specified as the driver, e.g.

Driver=SQL Server Native Client 10.0 for MS SQL Server 2008.

Use PWD_TO_ENCRYPT to encrypt the password (see below).

MM_PWD_FILE=OFF

If this entry is set to "OFF", Aurea CRM uses the password specified using the PASSWORD parameter. If the entry is set to "ON", a temporary password file is created in the .. \system\sys directory.

PWD_TO_ENCRYPT=<plain pwd>

Use this entry to store the database password in encrypted form: enter the password in plain text. The next time Aurea CRM is started, the password is encrypted and written to the PWD _ENCRYPTED entry. The PWD_TO_ENCRYPT entry is deleted automatically.

Each time Aurea CRM is started, the system checks whether the file mmdb.ini contains an encrypted password. If this is the case, the password is used.

You need to use Aurea CRM win to encrypt passwords using PWD_TO_ENCRYPT for other Aurea CRM products.

TABLE_PREFIX=MMDB

This entry specifies the prefix for the Aurea CRM tables. There is no default value; you therefore need to assign a value to this parameter.

SCHEMA=<schema name> (e. g.: SCHEMA=dbo)

As of MS SQ L2005, this parameter is set automatically.

Use this entry to specify the name of the database schema. If the Aurea CRM login ID does not correspond to the MS SQL default schema assigned to the database user, you need to enter SCHEMA=<ms sql default schema> in order to use all database functions.

If you use a reserved name (e.g. UPDATE) as a schema name, you need to place the entry in square brackets for all functions to work correctly.

TIMEZONE=110

This parameter specifies the time zone used by the database. The value is determine using the system settings and may not be edited.

SEGMENT_DAT=MMDAT

If the database has been split (e.g. over several hard disks), the "Filegroup" for Aurea CRM's database tables is defined in this entry.

If the database has not been split, this entry should be set to "NONE".

SEGMENT_IDX=MMIDX

If the database has been split (e.g. over several hard disks), the "Filegroup" for Aurea CRM's indices is defined in this entry.

If the database has not been split, this entry should be set to "NONE".

SEGMENT_SELDAT=MMSELDAT

If the database has been split (e.g. over several hard disks), the "Filegroup" for Aurea CRM's selection tables is defined in this entry.

If the database has not been split, this entry should be set to "NONE".

SEGMENT_SELIDX=MMSELIDX

If the database has been split (e.g. over several hard disks), the "Filegroup" for Aurea CRM's selection indices is defined in this entry.

If the database has not been split, this entry should be set to "NONE".

CASE_SENSITIVE=ON

If the database was installed on the server as case-insensitive, then this parameter should be used, as it can increase performance (set to "ON").

NDEX_MANDNR=ON

This entry causes each generated index to include the tenant number (only applicable to info areas that include the <xx>-TenNo field). As a result, access time is improved for large tables that include non-uniform tenant distribution.

The default value is "OFF"

MM_PACKET_SIZE=4096

This entry defines the network packet size (in bytes) for ODBC access.

JOIN_ID=OFF

In SQL queries including conditions applied to "fiid" and "kpid" but sorted according to an mmKey field, the optimiser accesses the mmKey field using an index. This can impact negatively on performance. By setting JOIN_ID=OFF, the condition also accesses the mmKey fields. The optimized SQL query would look like the following:

SELECT ... FROM table WHERE keyxxx LIKE '...' ORDER BY keyxxx

SELECT_LEFTOUTERJOIN=OFF

Disables the use of "left outer join" when reading records using a transfer fields format. The default setting is ON.

SELECT_INNERJOIN=OFF

Disables the use of "inner join" when reading records using a transfer fields format. The default setting is ON.

COLLATION = German_phonebook_CI_AS, Czech_CI_AS ,…

This entry is used to define the possible sorts applied to the MS SQL database during the current session. This parameter should only be assigned a value if multiple languages are used to sort, see Multi-language Sort.

CACHE_READ_CURSOR=OFF, CACHE_WRITE_CURSOR=OFF, CACHE_CURSOR=OFF

Use these entries to turn cursor sharing on or off. The default value for all entries is "ON".

The CACHE_CURSOR entry turns both modes (read and write) either on or off.

ERRORnn=<Error Message>

Use this setting to translate SQL error messages (e.g. "Cursor was not closed"), if you are using an operating system in another language.

Examples:

Error20=Cursor was not closed.

Error20_1=Der Cursor wurde nicht geschlossen.

Error20_2=...

...

Error21=Connection is busy with results for another hstmt.

Error21_1=Die Verbindung ist mit Ergebnissen von einem anderen hstmt belegt.

Error21_2=...

...

NOLOCK_HINT=ON

Use this entry to generate Select statements using the hint NOLOCK.

Example: SELECT columnA FROM tableA WITH (NOLOCK)

This allows queries to be run on tables that are being updated, which improves the system's performance.

SELECT_INNER_JOIN=ON

This entry determines that the INNER JOIN parameter is used when creating a selection from a base selection. This improves performance. The default value is OFF.

SQL_UPDATE_SELECTIONSTATISTICS=n

If the number of records found in a selection is equal to or greater than the number entered here, the selection statistics are updated.

AGGREGATE_CURRENCY=ON

This entry ensures that sorting by sum total fields works correctly when using multiple currencies. The default setting is OFF.

NO_CURRENCY=ON

This entry ensures that sorting by sum total fields works correctly if you are not using multiple currencies. The default setting is OFF.

COLLATE=OFF

If different sort orders (collations) are defined in the Configuration info area (see MSSQL) and database, the sort order defined in the Configuration info area is used when evaluating conditions applied to string fields. Set COLLATE=OFF to use the database's sort order.

ORDER_CATALOG_FUNCTION=OFF

By default, when sorting records by catalog fields (e.g. "select Firma,Land,Ort from FI order by Land") Aurea CRM generates a more or less complex SQL statement without accessing the Catalog info area.

Set ORDER_CATALOG_FUNCTION=ON to use the SQL function "prefix"_ CatText( ) instead. However, this sorting method is much slower because the function accesses the Catalog info area.

[STATIC CURSOR]

Use this section to disable static database cursors. This can solve problems with performance when reading large volumes of data (e.g. when exporting).

To disable static cursors for individual info areas, specify the info area using the info area ID, e.g.:

MA=off

To disable static cursors for all tables:

AllTables=off

Multi-language Sort

Aurea CRM supports unicode character sets and therefore the input of data using different codepages. Using multi-language sorts, each user can determine their own language-specific sort settings.

Types of Sort

  • Data is sorted by field
    • by selecting Data > Sort from the menu
    • by clicking on the column header in a list
    • in the dialog box used to select catalog values

    The sort order for text fields is based on the Windows regional settings (Start > Settings > Control Panel > Regional and Language Options).

  • Data is sorted by index
    • by selecting Data > Sort from the menu
    • when sorting in selections

    The sort order used for indices is defined in the index itself. The index is constructed using the sort order used by the MS SQL database. This allows all users to access the same sort independent of their regional settings.

In order to allow users of an MS SQL database to use a language-specific sort order for indices, define the following sort order:

  1. Enter all the sort orders you require additionally in the mmdb.ini file. These options need to be supported by MS SQL server (see MS SQL documentation on "Collations"):

    Example: Aurea CRM installation using the "SQL_Latin1_General_CP1_CI_AS" sort order on a central database with users in three countries (Austria, the Czech Republic and Russia).

    [MM ODBC MSS]

    COLLATION= German_phonebook_CI_AS, Czech_CI_AS, Cyrillic_General_CI_AS

    Note: Each new index impacts negatively on the performance of the database. Consider whether using a "Multilingual Linguistic Sort" would also meet your requirements (see Oracle documentation). If you do need to add language-specific sorts, only add those that you definitely require.
  2. When starting one of Aurea CRM's modules, a message is displayed informing you that the necessary indices need to be added ("ADD").
  3. Switch to the SQL tool and click on the Rebuild NLS_SORT-Indexes to add the indices for the selected info area, see SQL Tool. Click on Rebuild ALL NLS_SORT-Indexes to add indices for all info areas.
    Note: Aurea recommends always using the Rebuild ALL NLS_SORT Indexes option.

    Only those indices are affected that access text fields.

    Note: To remove an index that is no longer required, delete the corresponding value from the MM_NLS_SORT parameter. When starting one of Aurea CRM's modules, a message is displayed informing you that the indices need to be deleted ("REMOVE"). Click on either the Rebuild NLS_SORT-Indexes or Rebuild ALL NLS_SORT-Indexes buttons in the SQL tool.
  4. Assign a sort order to users and rights groups in the Configuration info area in the Rights module:

    Field

    Contents

    Category

    MSSQL

    Option

    COLLATION

    Text

    <Sort order>, e. g. ENGLISH

    Rep/Group

    Enter the desired rep or group
    Note: If different sort orders (collations) are defined in the Configuration info area (see MSSQL) and database, the sort order defined in the Configuration info area is used when evaluating conditions applied to string fields. Enter COLLATE=OFF in the mmdb.ini file (see [MM ODBC MSS]) to use the sort order defined in the database.