SQL Server Diagnostic Utility

Toolbar and menu

Field Description

File

List

Option	Description
Table verification details	Includes table verification details when generating the report.
Index verification details	Includes index verification details when generating the report.
Column verification details	Includes column verification details when generating the report.

Include

Valid user defined columns This includes user defined columns in the display. Uncheck this option to speed up the validation process.

Function

Generate Index Script This generates the script ADDKEY70.SQL in your working folder.

Test Bulk Insert command

Drop Foreign Keys This removes all existing standard SYSPRO Foreign Keys.

Rebuild Foreign Keys

This rebuilds all standard SYSPRO Foreign Keys in the current database.

Foreign keys enable the database to enforce data integrity by allowing the definition of logical links between tables (e.g. a sales order detail table could have a foreign key to the sales order header table to ensure that each line added has a corresponding header row).

SQL Server Enterprise Manager (and some other software applications) can take advantage of Foreign Keys defined in the database to understand the logical links between tables. In addition, diagrams (sometimes called Entity Relationship diagrams) can be generated automatically using these logical links.

SYSPRO ships with an addition to the data dictionary that defines the logical links between tables. The physical file defining the links is stored beneath the server ..\Base\dd folder as DDSFKY.DAT (and .IDX). When SYSPRO is installed on a SQL server-based system, this set of Foreign Keys is applied when the database is created. When migrating from prior versions, the Foreign Keys are automatically added. Standard SYSPRO Foreign Key definitions begin with Syspro_ and must not be changed.

You should also not add user defined Foreign Keys beginning with Syspro_.

The Foreign Keys defined have a WITH NOCHECK clause, which means that the Foreign Key is not used for data integrity purposes, as SYSPRO performs its own data integrity verification.

We strongly discourage you from adding your own Foreign Keys to a SYSPRO database (especially without the NOCHECK directive) as this could cause SYSPRO applications to fail unexpectedly.

SQL Server Configuration

Field Description

SQL Server Information

System Information

The System Version function shows the result of the SQL Server statement:

SELECT @@VERSION

Information about how long it takes to connect to SQL Server is displayed, along with the name of the ODBC driver being used. This may help support personnel diagnose specific problems.

The connection speed has a large effect when logging into SYSPRO, when using optimized SQL commands from within SYSPRO programs and when using e.net Solutions. The connection time should typically be less than 1/10th of a second (0.10 sec). Any connection speeds slower than this could lead to a significant degradation when using SYSPRO. If the connection speed appears slow, then it is recommended that you configure your ODBC data source on the server and check the time taken to test the connection. If this is also slow, then verify the protocols used to communicate with SQL Server to improve the speed.

Database Configuration

The SYSPRO Database Settings function detects anomalies or inconsistencies between the SYSPRO Company Control files (?ADMCTL.DAT files), the SysproAdmin company/database cross-reference table and the SYSPRO company databases.

This function shows a list of all SYSPRO Company Control files (?ADMCTL.DAT) and collates a list of all the company/database cross references in SysproAdmin. The list view shows this information merged by company.

Review the Status column of the Database Information pane for a description of the anomalies identified.

Verify Current Database

These options are designed to deal specifically with the database associated with the current company.

Option

Description

Verify Tables

Displays any missing standard SYSPRO tables and any additional user-defined tables.

Only tables which start with a capital letter are included in the selection.

Although adding user-defined tables does not generally cause a problem, missing standard SYSPRO tables is a serious problem. Contact your local SYSPRO support agent to correct this situation.

Verify Indexes

Checks that each standard SYSPRO table has a primary index. The diagnostic function does not currently validate the columns defined in the index, or alternate indexes.

We have discovered that a number of SYSPRO system administrators have attempted to use the SQL Server Data Transformation Services (DTS) wizard to copy one database to another and have used the default option to copy tables and their views. However, this does not copy indexes.

Verify Columns

Checks that each standard SYSPRO column is defined appropriately and shows user-defined columns.

Any user-defined columns are verified to ensure that they are compatible with SYSPRO.

This function also detects any data size discrepancies between the SYSPRO database and the SYSPRO data dictionary.

The columns Actual size and Expected size are displayed when you select to verify columns.


	This function may take several minutes to run as there are a large number of standard columns in a typical SYSPRO database. There is a menu choice to ignore user-defined columns - this will improve the speed of the validation.

Review the Exception Message column of the Database Information pane.

Database Information

The details displayed in this pane corresponds to your selection in the SQL Server Configuration pane.

Column	Description
ODBC driver name	This helps to identify whether the SQL Server generic driver or one of the 'SQL Server Native Client' drivers are in use.
Connection time	If this is over 0.5 second then this is too slow for normal SYSPRO performance. A message is displayed indicating that if you are using an ODBC DSN then you should view the Client Configuration information and consider using the TCP/IP network library.

Status

When selecting the SYSPRO Database Settings option, this column identifies any anomalies. In order of priority, the following status conditions may be displayed:

Status	Description
No Matching ADMCTL	Indicates that an entry in the `SysproAdmin` table has been found but that there is no matching Company Control File (`?ADMCTL.DAT`). This usually occurs when a test or temporary company has been added in SYSPRO and the Company Control file (`?ADMCTL.DAT`) has been subsequently deleted, but the `SysproAdmin` table has not been changed. If this is the case, then to tidy up you should delete the row in the `SysproAdmin` table and delete the remaining database for that company - ensure that this is the correct database before deleting it. If in doubt consider renaming it with a `.old` extension.
Cannot connect to database	Indicates that both the Company Control File (`?ADMCTL.DAT`) and `SysproAdmin` table match, but the database is inaccessible. It's possible that the database has been renamed or dropped. It's possible that the company was created as a test or temporary company that is no longer needed. If this is the case, then to tidy up you should delete the Company Control file (`?ADMCTL.DAT`) and other related files and then manually remove the corresponding rows from the `SysproAdmin` table.
Database does not match ADMCTL	Indicates that the database name in the Company Control file (`?ADMCTL.DAT`) does not match the equivalent row in the `SysproAdmin` table. If the `SysproAdmin` table database name is incorrect then manually change the `DatabaseName` column in the `SysproAdmin` table to show the correct database name. However, if the database name in the Company Control file is incorrect then load SYSPRO, change the company and edit the database name, save and exit, and try re-running this diagnostic option to ensure that it has been corrected.
Case insensitive database	SYSPRO is not designed to work on a case-insensitive database. If you see this status you must not use this database for SYSPRO.
Version incorrect	Indicates that the Company Control File (`?ADMCTL.DAT`) contains a version that does not correspond to the current system version. This may occur when you are in the process of migrating companies from one SYSPRO version to another and have not yet converted all the companies.
No matching SysproAdmin	Indicates that the Company Control File (`?ADMCTL.DAT`) exists for a SQL Server-based company, but no matching row in the company/cross-reference table `SysproAdmin` has been found. This can happen when an end-user attempts to copy an existing database to a new name and they attempt to use the newly-copied company. In this case, it will be necessary for an appropriate row to be manually added to the `SysproAdmin` table. In addition to the above statuses, any C-ISAM-based companies are also included in the list of companies. This may serve to highlight either test or temporary companies that are to be deleted, or companies that have not yet been migrated to SQL Server.

Exception Message

When selecting the Verify Columns option, the following exception messages may be displayed:

Message	Description
User column detected - ok	Indicates that a user-defined column has been found (it's not a standard SYSPRO column) and the column will not cause a problem for SYSPRO programs. If a user-defined column is not required then it is recommended that you drop it manually so as not to conflict with possible future SYSPRO columns.
User column detected - not null	Indicates that a user-defined column has been found (it's not a standard SYSPRO column) and that the column has been defined as `NOT NULL`. This is a serious problem when the column is defined in a SYSPRO table and needs to be corrected so that the column is `NULLABLE`.
Collation does not match default	This message indicates that the collation of the column does not match the database itself. This can lead to a serious problem when future columns are added to the database, as SYSPRO does not support mixed collation between columns.
Standard column missing	Indicates that a standard SYSPRO column is missing. This can indicate that the database has not yet been migrated to the current version of the software. This is a serious problem because when SYSPRO programs use the table they will expect this column to exist. Contact you local SYSPRO support agent to correct this situation.
Incorrect data size	Indicates that there is a data size discrepancy between the SYSPRO database and the SYSPRO data dictionary. This discrepancy is indicated in the Actual size and Expected size columns.

Notes and warnings

Restrictions and limits

When issuing SQL statements to query objects in the database (e.g. tables, indexes and columns) only the standard default schema (dbo) is searched.

User schemas (which could potentially contain objects with the same names as standard SYSPRO objects) are ignored.