The new Database Check Tool for Microsoft Dynamics NAV checks for inconsistencies between the Microsoft Dynamics NAV Object Metadata Snapshot table and SQL Server (CheckSnapshot) or checks and optionally correct invalid whitespace characters in code data types (FindInvalidCodeFields). You can use the tool for Microsoft Dynamics NAV 2013 R2 and later versions (such as, Microsoft Dynamics NAV 2015 and Microsoft Dynamics NAV 2016).
This tool replaces the earlier version released as hotfix
KB2963997 .
NOTE Microsoft recommends you to take a backup of the target database before running the database checker tool.
CheckSnapshot
When Microsoft Dynamics NAV applies changes to the table design in SQL Server; these changes are calculated based on the difference between the Object Metadata table, which is part of the Microsoft Dynamics NAV application database, and the Object Metadata Snapshot table, which is in the Microsoft Dynamics NAV tenant database. If the Object Metadata Snapshot table does not contain an accurate view of the actual table design in SQL Server, then Microsoft Dynamics NAV Server might not be able to apply changes to the design of that table and Microsoft Dynamics NAV cannot access the database.
If the tool reports a problem, there are different ways to resolve the issue. One option to resolve the issue is to manually align the table(s) in SQL Server with Microsoft Dynamics NAV metadata. For example, you can create the missing table, index, or field. The other option is to align Microsoft Dynamics NAV metadata with SQL Server. The way to do this is to make the changes in the Microsoft Dynamics NAV Development Environment so that the table fits the layout in SQL Server. Then run the following statement to update the snapshot:
declare @objectId int = 3
update [Object Metadata Snapshot] set Metadata = (select Metadata from [Object Metadata]
where [Object Type]=1 and [Object ID]=@objectId) where [Object Type] = 1 and [Object
ID]=@objectId
Syntax to check the database
NavCheckDatabase CheckSnapshot [-Server] <string> [-Database] <string> [-AppDatabase] <string>
Parameters description:
Server: The name of the database server on which you want to run the command. To specify the local computer, specify ".".
Database: The name of the database that you want to check, such as DynamicsNAVDB.
AppDatabase: The name of the application database, such as MyAppDB.
If appdatabasename is not specified, then it is assumed that the application and the tenant database are the same (Legacy mode), for example:
NavCheckDatabase CheckSnapshot . DynamicsNAVDB
FindInvalidCodeFields
The Database Check Tool has also the possibility of finding and correcting non-valid whitespace (as defined by the .NET Framework) on code fields of Microsoft Dynamics NAV databases. The tool traverses and reads all data and either counts, shows, or fixes existing code fields with whitespace in them.
If the tool reports a problem, you can also find which records contain whitespaces by setting the Extended parameter; when using it, it will also show the trimmed values of the records that contain the whitespaces.
If you want the tool to modify this data, set the –Modify parameter. If you set the Modify parameter, the tool will read all code data, find the records that contain whitespace and remove the whitespace at the beginning and end of the code values. Microsoft Dynamics NAV Server also uses these trimmed values when working with code fields.
Syntax
NavCheckDatabase FindInvalidCodeFields [-Server] <string> [-Database] <string> [AppDatabase] <string> [-Extended] [-Modify]
Parameter Description:
Server: The name of the database server on which you want to run the command. To specify the local computer, specify ".".
Database: The name of the database that you want to check, such as DynamicsNAVDB.
AppDatabase: The name of the application database, such as MyAppDB.
Extended: Specifies if the result must return the trimmed records that have non-valid whitespaces.
Modify: Specifies if the tool must trim the values of code fields with whitespaces, and update them in the SQL Server database.
It is not recommended to use the appdatabasename when using FindInvalidCodeFields, for example:
NavCheckDatabase FindInvalidCodeField . DynamicsNAVDB