Share via

Altering Databases - Collation Tab

  • Article

Changes the collation that is used by the database.

To open this window, on the File menu, choose Database, choose Alter, and then choose the Collation tab.

Before you change the collation, you have to select the Single user option on the Options tab.

If you change the database collation, then the collation of objects in the database is changed except for tables that have the LinkedObject property set to Yes. You must manually re-create these objects. For example, you can script them in SQL Server Management Studio.

If you change the collation from a case-sensitive to a case-insensitive collation or from an accent-sensitive to an accent-insensitive collation, then duplicates can occur in the primary keys of the tables. Duplicates can be caused by the values of the character data stored in the primary keys. If duplicates occur, then you receive an error message and the database collation change is stopped. We recommend that you do not change these attributes of a collation.

Note

Changing the collation can be a lengthy process that depends on the size of the database and the number of companies in the database. The system tables and all user table indexes that contain character data must be rebuilt.

The Language drop-down list displays the friendly name of the language, not the full Windows collation name. For some languages, there are multiple collations that sort characters differently. For example, the Windows collation languages include multiple Scandinavian languages, some of which sort Aa after Z, Æ, Ø, and some of which sort Aa after A and before B. When you upgrade from Microsoft Dynamics NAV 2009 to Microsoft Dynamics NAV 2015, you upgrade the database to the Windows collations. If you used SQL collation in earlier versions of Microsoft Dynamics NAV, then after you upgrade, verify that the Windows collation sorts characters in the way that you expect.

If you set the Validate Collation check box, then collation languages that run with a different non-Unicode code page from your system non-Unicode code page are filtered out of the Language drop-down list. An example scenario of when you might want to choose a collation language that has a different code page from your system code page is if you want to prepare a Japanese database on a Danish computer.

Changing the Collation of a Microsoft Dynamics NAV 2015 or Microsoft Dynamics NAV 2013 R2 Database

You cannot change the collation directly in the current database. To change the collation, you must create a new database that uses the correct collation, and then export the data from the old database and import it to the new database. You can do this by using SQL Server Management Studio and the Microsoft Dynamics NAV Administration Shell as outlined in the following procedure.

To change the collation

  1. In SQL Server Management Studio, create a new database that uses the desired collation.

    Make sure that the service account of the Microsoft Dynamics NAV Server instance that will connect to the database has proper permission to the database. The service account must be a member of the db_owner role of the database. For more information see Provisioning the Microsoft Dynamics NAV Server Account.

  2. To export the data from the old database to the new database, open the Microsoft Dynamics NAV Administration Shell, and run the Export-NAVData cmdlet as shown in the following example.

    Export-NAVData -DatabaseServer DatabaseServerName -DatabaseName OldDatabaseName -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath c:\Files\MyNavDB.navdata

    For more information, see Export-NAVData cmdlet.

  3. To import the data from the old database to the new database, run the Import-NAVData cmdlet as shown in the following example.

    Import-NAVData -DatabaseServer DatabaseServerName -DatabaseName NewDatabaseName -IncludeApplication -IncludeApplicationData -IncludeGlobalData -AllCompanies -FilePath c:\Files\MyNavDB.navdata

    For more information, see Import-NAVData cmdlet

  4. Connect the new database to the Microsoft Dynamics NAV Server instance.

    For more information, see How to: Connect a Microsoft Dynamics NAV Server Instance to a Database.

See Also

Other Resources

Altering Databases on SQL Server
Altering Databases - General Tab
Altering Databases - Database Files Tab
Altering Databases - Transaction Log Files Tab
Altering Databases - Options Tab
Altering Databases - Integration Tab
Altering Databases - Advanced Tab