Problem

When updating the database (after an update, or by manually initializing Add-ons) following error message shows:

Execute CSPickITCustomSolutions.sql Failed: (-2147221503: [Microsoft][ODBC SQL Server Driver][SQL Server] CREATE INDEX failed because the following SET options have incorrect settings: 'ANSI_NULLS'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
SQL State: 37000
DB error: 1934

Cause

This error can show with any SDK based solution, also when WMS or ICL are installed, when Filtered Indexes have been activated and are used in the database.

Solution

The initialization script for the SDK solution has to be changed by the developer party. The command SET ANSI_NULLS OFF just before starting "CREATE PROC dbo.CSCreateIndex(...." has to be changed to SET ANSI_NULLS ON. After this change, the database can be updated again by forcing a database initialization. This Microsoft document lists the possible effects of changing this setting.


A workaround is to change the SQL script yourself when the error occurs:

• determine the SQL script name which is mentioned in the error message (in the sample message above, this is csPickitCustomSolutions.sql)
• this file is located in the Globe program folder, subfolder 'SQL', default folder would be c:\program files\exact software\SQL
• open the file using Notepad and search for the text: CREATE PROC dbo.CSCreateIndex
• two lines above the found text, change the text SET ANSI_NULLS OFF to: SET ANSI_NULLS ON
• save the file
• force a database update, please read Points of interest when performing an Exact Globe database update with E-WMS or ICL and section Forcing a database update in the same document

Please be aware that this change in the SQL script is workstation dependent. Other workstations will still have the original script, but when the database has been updated once, there will be no reason to do so on other workstations.

For WMS and ICL, the issue has been addressed in internal ref 39.176.635

 

Related documents

• Reduce index storage costs with filtered indexes on an Exact Globe database
• Points of interest when performing an Exact Globe database update with E-WMS or ICL
   
• External link: SET ANSI_NULLS (Transact-SQL) (Microsoft MSDN)
• Internal ref: Error on cspickitcustomsolutions.sql after adding filtered indexes