Error Message

In EVBAAdmin under Info in the Current Status of the Customer database for the MERGE command when executing line 17699 in AcceleratorDatabasePrograms.sql:

Incorrect syntax near 'MERGE'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

In EVBAAdmin under Info in the Current Status of the Customer database for the INDEX command when executing line 364 in AcceleratorDatabaseSchemaUpdateV12_5.sql:

Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.

Enterprise Vault Event Log errors for the INDEX command when executing line 364 in AcceleratorDatabaseSchemaUpdateV12_5.sql:

Log Name: Veritas Enterprise Vault
Source: Accelerator Manager
Event ID: 49
Task Category: None
Level: Error
Keywords: Classic
Description:
APP ATM - Error Executing : C:\Program Files (x86)\Enterprise Vault Business Accelerator\AcceleratorDatabaseSchemaUpdateV12_5.sql
Line: 364
Failed to Execute the following command:

IF TYPE_ID('[VaultSampleType]') IS NULL
BEGIN
CREATE TYPE [VaultSampleType] AS TABLE(
[KVSVaultEntryID] varchar(112) NULL,
[KVSSaveSetId] [varchar](100) NULL,
[Author] [nvarchar](254) NULL,
[Subject] [nvarchar](260) NULL,
[MailDate] [datetime] NULL,
[NumAttachments] [int] NULL,
[size] [int] NULL,
[Direction] [int] NULL,
[Type] [int] NULL,
[Recipient] [nvarchar](250) NULL,
[RecipientTruncated] [bit] NULL,
[TransactionID] [char](45) NULL,
[Extension] [nvarchar](50) NULL,
[ItemLocationID] [int] NULL,
[ModifiedDate] [datetime] NULL,
[PolicyAction] [int] NULL,
[PolicyXML] [varchar](2000) NULL,
[PolicySummary] [nvarchar](100) NULL,
[PolicySummaryTruncated] [bit] NULL,
[EncryptionStatus] [tinyint] NULL,
[SentimentScore] [tinyint] NULL,
INDEX CX_VaultSampleType CLUSTERED (KVSSaveSetId)
)
END

. System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase.Do[T](Func`1 action)
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlCommand.ExecuteNonQuery()
at KVS.Accelerator.Application.Install.RunScript(String ScriptName, EVSqlCommand CMD, Int32 CustomerID)
ClientConnectionId:f22687cf-2b49-43c2-a287-5ab909779741

Error Number:1018,State:1,Class:15

V-437-49

Log Name: Veritas Enterprise Vault
Source: Accelerator Manager
Event ID: 59
Task Category: None
Level: Error
Keywords: Classic
Description:
APP ATM - Failed to update database. System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near 'INDEX'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlBase.Do[T](Func`1 action)
at Symantec.EnterpriseVault.DatabaseAccess.EVSqlCommand.ExecuteNonQuery()
at KVS.Accelerator.Application.Install.RunScript(String ScriptName, EVSqlCommand CMD, Int32 CustomerID)
at KVS.Accelerator.Application.Install.UpdateDatabase(CustomerRow theCustomerRow, String configDSN, ProductType productType)
ClientConnectionId:f22687cf-2b49-43c2-a287-5ab909779741
Error Number:1018,State:1,Class:15

V-437-59

Microsoft's SQL MERGE command is used by CA and DA to combine information from one table into another table and is a part of normal processing to log data. The INDEX command is designed to create an index as specified.

Newer versions of SQL can include updated syntax for T-SQL commands. For example, SQL's use of the MERGE command changed in SQL Server version 2008 and higher. As such, the SQL Server must know what version the database is set to so it can correctly process the MERGE command. Similarly, the use of INDEX can now be combined with other commands in newer SQL versions.

In order to support the updated syntax, SQL reads the database's compatibility level property, compares it to the SQL Server version's compatibility level and then selects the appropriate processing steps. A full list of compatibility levels can be found here.

The errors above are displayed when the database's compatibility level is set to a version that does not appropriately support the MERGE statement and/or the use of INDEX with other statements, and does not match the current SQL Server version.

Note this error may be seen for commands other than MERGE and INDEX.

A database's compatibility level is not automatically upgraded when CA or DA are upgraded and requires a manual change. The one exception is if the database is moved to a SQL server whose minimum supported compatibility level is higher than the database's current compatibility level; in this case, SQL will automatically increase the compatibility level to the minimum compatibility level supported by that SQL Server version. A list of minimum compatibility levels can be found here. This also applies to the Master database. If a SQL server was upgraded in-place, the Master database will likely remain at a lower compatibility level than the other system databases. This was done by Microsoft in order to ensure existing applications can continue to function after the upgrade. However, a side-effect is not being able to use the newer features, functionality, operators, etc., until the Master database's and user databases' compatibility levels are edited to match the server level. Additional information is provided by Microsoft here.

Check the latest Compatibility Charts to verify a supported version of SQL is in use.

First verify the Master database is at a compatibility level that matches the SQL server version. This should be performed by the on-site SQL team and corrected by the on-site SQL team if needed.

Then manually verify and edit the Customer database's compatibility level if found to not match the Master database, then re-try the upgrade:

1. Open SQL Server Management Studio with an account having permissions to update the Customer database's properties | Connect to the Database Engine server (and instance) housing the Customer database.
2. In the Object Explorer pane, expand Databases | Right-click the Customer database | Properties | Options | Select the Compatibility level from the dropdown list that matches the SQL server version (typically the highest compatibility level listed) | OK.
3. Browse to the EVBAAdmin administration website using the Vault Service Account credentials on the CA or DA server, typically at http://localhost/evbaadmin .
4. Right-click on the appropriate Customer in the left pane | Properties | Click the Re-try Upgrade button (button title may vary between versions) | Monitor the upgrade through completion.
5. Repeat these steps for each Customer database.