Hotfix for CA/DA 9.0 SP2 Getting error "Incorrect Syntax Near "*"" when adding a user to a Role Assignment

Article:TECH181411  |  Created: 2012-02-14  |  Updated: 2013-08-01  |  Article URL http://www.symantec.com/docs/TECH181411
NOTE: If you are experiencing this particular known issue, we recommend that you Subscribe to receive email notification each time this article is updated. Subscribers will be the first to learn about any releases, status changes, workarounds or decisions made.
Article Type
Technical Solution


Environment

Issue



When making any Role Assignment changes in Compliance Accelerator (CA) or Discovery Accelerator (DA), the error "Incorrect Syntax Near "*"" is displayed in a pop-up message on the CA or DA Client


Error



- From CA/DA Client pop-up:
Incorrect Syntax Near "*"

- From Symantec Enterprise Vault Event Log:
Event Type: Error
Event Source: Accelerator Service Processor
Event Category: None
Event ID: 100
Description:
APP AS - Customer ID: 2 - An error has occured when initializing the Cases. System.Data.SqlClient.SqlException: Incorrect syntax near '*'.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at KVS.Accelerator.Case.Case.GetBaseDS(Int32 CaseID, CaseType aCaseType, String StatusID, String PrincipalLogin, Int64 RowVersion)

 


Environment



Enterprise Vault 9.0 SP2 on Microsoft Windows Server 2003 or 2008
Compliance Accelerator and / or Discovery Accelerator 9.0 SP2 on Microsoft Windows Server 2003 or 2008
Microsoft SQL Server 2005 or 2008

 


Cause



Certain CA and DA tables use a column containing a numeric value for the rowversion counter. This rowversion counter can only contain limited number of integer characters, with 10 integer characters typically being enough for most installations. When enough permissions changes have occurred, the rowversion counter can exceed 10 characters. When between 11 and 19 integer characters are present in the rowversion counter, the error above is thrown by the SQL processing.

Nine Stored Procedures in each CA or DA Customer database contain code that does not properly handle the rowversion counter when more than 10 integer characters are needed. The hotfix resolves this issue by expanding the limitation of the rowversion counter to handle more integer characters.

When the database timestamp (RowVersion) exceeds 10 integer characters or the hex value of 0x000000007FFFFFFFF, one of nine stored procedures fails to properly convert the RowVersion to a BIGINT value. This failure causes the Permission Cache in the Accelerator memory to not update properly.


Solution



This issue has been resolved in the following releases:
 

Enterprise Vault 9.0.4
http://www.symantec.com/docs/TECH147785

Enterprise Vault 10.0.1
http://www.symantec.com/docs/TECH147787


WORKAROUND for all versions:

Objective of the workaround is to prevent the end user and housekeeping routines from accessing the Permissions Cache.

 

1. Open the Accelerator Client
2. Configure the end users to not use the Permission Cache by modifying the Permission Cache Option to 0 Configuration | Settings | Security | (hidden setting) Permission Cache Option = 0 (Display hidden settings by holding the Ctrl key while selecting the words Configuration Settings with the mouse)
3. Configure housekeeping routines to not update the Permission Cache by modifying the Cache Policies File. Configuration | Settings | System | (hidden setting) Cache Policies File
    a. Save as... (save file to a location for modification)
    b. Access the saved file via Note Pad
    c. Modify the file depicted in Bold

<Policies

Cache="0"
UseDatabaseNotification="0"
SlidingExpirationDelay="5"
AbsoluteExpirationDelay="0"
CacheItemPriority="3"
UseUSN = "0"
EnableNotificationPolling = "0"
IntermediaryExpirationDelay="10"
UsePolling="0"
PollingInterval="2"
NotificationSQLCMD="select NotificationKey from dbo.tblCacheNotification where NotificationKey='{0.EN_US}'" >
<!--SlidingExpirationDelay should be set to a low value when using database notifications-->
<!-- this section allows to overide some of the default settings for specific object.
e.g. <Policy Key="BO_LegalCases" Cache="1" UseDatabaseNotification="1" />
in this case BO_LegalCases will be cache and will use database notification.
all the none specified attributes will de default to the values defined in Policies

NOTE: BO_Principal and BO_Target cannot use notifications without updates to the notification
triggers.
-->

<Policy Key="BO_LegalHolds" Cache="0" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000"/>
<Policy Key="BO_Cases" Cache="0" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000" />
<Policy Key="BO_Principals" Cache="0" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000"/>
<Policy Key="BO_Searches" Cache="0" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000" />
<Policy Key="BO_ProductionRuns" Cache="0" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000"/>
<Policy Key="BO_Targets" Cache="0" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000"/>
<Policy Key="BO_NTGroup" Cache="0" CacheItemPriority="6" UseDatabaseNotification="0" UseUSN="0" EnableNotificationPolling="0" AbsoluteExpirationDelay="3000" />
<Policy Key="BO_Customer" Cache="0" />
<Policy Key="BO_Customers" Cache="0" />
<Policy Key="BO_Install" Cache="0" />
<Policy Key="BO_Config" Cache="0"/>
<Policy Key="BO_User" Cache="0" UseDatabaseNotification="0" EnableNotificationPolling="0" UseUSN="0" AbsoluteExpirationDelay="3000" />
<Policy Key="BO_CasePrincipalPermission" Cache="0" UseDatabaseNotification="0" UseUSN="0" AbsoluteExpirationDelay="3000" />

</Policies>
</CachePolicies>


4. Import the modified file back into the Accelerator.
            Configuration | Settings | System | (hidden setting) Cache Policies File
    a. Browse... (navigate to the modified file)
    b. Open the modified file
    c. Press Save in the lower right of the screen
5. Restart Enterprise Vault Accelerator Manager Service
6. (Compliance Accelerator Only) Restart Journal Task on Enterprise Vault Journaling Servers utilizing the Journal Connector
7. Reopen the Accelerator Clients

For CA and DA 9.0 SP3
Symantec Corporation has acknowledged that the above-mentioned issue is present in the current version(s) of the product(s) mentioned at the end of this article. Symantec Corporation is committed to product quality and satisfied customers.

There are no plans to address this issue by way of a patch or hotfix in the current or previous versions of the software at the present time. However, the issue is currently scheduled to be addressed in the next major revision of the product. Please be sure to refer back to this document periodically as any changes to the status of the defect will be reflected here. Please note that Symantec Corporation reserves the right to remove any fix from the targeted release if
it does not pass quality assurance tests. Symantec’s plans are subject to change and any action taken by you based on the above information or your reliance upon the above information is made at your own risk.
 


For Compliance Accelerator (CA) or Discovery Accelerator (DA) 9.0 SP2:

 

Hotfix installation steps:

1) Download the hotfix file below and save it to a folder on a computer that has a file decompression application installed.

2) Decompress the hotfix file to reveal the following 2 files:

  • 9_0SP2_CA_HF_ET2677298.sql.SAFE
  • Hotfix_9_0_2_Details_Etrack_2677298.txt

3) Rename the file '9_0SP2_CA_HF_ET2677298.sql.SAFE' to remove the '.SAFE' extension and be named "9_0SP2_CA_HF_ET2677298.sql".

4) Copy the file "9_0SP2_CA_HF_ET2677298.sql" to the SQL Server(s) hosting the CA or DA Customer database(s).

- For each CA/DA 9.0 SP2 Customer database -

- On the CA or DA  Server

5) Stop the Enterprise Vault Accelerator Manager Service (EVAMS)

- On each SQL Server \ Instance hosting a CA or DA  Customer database

6) Create a backup of the following 9 Stored Procedures using the steps below:

- Stored Procedure Names

  • usp_CacheNotification_sel
  • usp_Case_Base_Sel
  • usp_Case_Sel
  • usp_LegalCase_Sel
  • usp_Principal_Sel
  • usp_Productions_sel <-- Note the 's' at the end of Productions as there is also a Stored Procedure named usp_Production_sel that is not modified by the hotfix.
  • usp_Search_Sel
  • usp_Target_Base_Sel
  • usp_Target_Sel

- Backup procedure

     a) Launch SQL Server Management Studio using an account with permissions to modify the contents of the CA or DA Customer database, such as the Vault Service Account, then:

i) Expand Databases
ii) Expand the CA Customer database
iii) Expand Programmability
iv) Expand Stored Procedures

     b) For each of the above Stored Procedures

i) Right click on the Stored Procedure.
ii) Select the 'Script Stored Procedure as' option.
iii) Select the 'ALTER To' option.
iv) Select the 'File ...' option
v) When prompted for the file name, navigate to the drive and folder where the backup copies are to be stored, then enter "Pre-ET2677298-" followed by the name of the Stored Procedure (i.e., E:\Safety\Pre-ET2677298-usp_CacheNotification_Sel.sql).

7) Open a SQL Query window focused on the CA or DA Customer database.

8) Use NotePad to open the hotfix file "9_0SP2_CA_HF_ET2677298.sql".

9) Copy and paste the contents of the hotfix file to the SQL Query window.

10) Execute the SQL Query window contents to drop and create the corrected stored procedures.

11)  When all CA and DA  Customer databases to which the hotfix is to be applied have been processed, close SQL  Server Management Studio and log off of the SQL Server.

- On the CA or DA  Server

12)  Start EVAMS.

- On a workstation with the CA or DA  Client installed

13)  Log onto the workstation with an account that has the permissions to modify role assignments.

14)  Modify any row and save the change to confirm the hotfix has resolved the issue. 


Hotfix removal steps - to be followed only if the hotfix must be removed:

- On the CA or DA  Server

1) Stop the Enterprise Vault Accelerator Manager Service (EVAMS).

- On each SQL Server \ Instance hosting a CA or DA  Customer database

2) Follow the steps below for each Stored Procedure (SP) listed to restore the original version of each SP.

- Stored Procedure Names

  • usp_CacheNotification_sel
  • usp_Case_Base_Sel
  • usp_Case_Sel
  • usp_LegalCase_Sel
  • usp_Principal_Sel
  • usp_Productions_sel <-- Note the 's' at the end of Productions as there is also a Stored Procedure named usp_Production_sel that is not modified by the hotfix.
  • usp_Search_Sel
  • usp_Target_Base_Sel
  • usp_Target_Sel

- Restore procedure

     a) Launch SQL Server Management Studio using an account with permissions to modify the contents of the CA or DA Customer database, such as the Vault Service Account, then:

i) Expand Databases
ii) Expand the CA Customer database
iii) Expand Programmability
iv) Expand Stored Procedures

     b) Open a SQL Query window focused ont he CA or DA Customer database.

     c) For each of the above Stored Procedures

i) Use NotePad to open the associated backup file (i.e., E:\Safety\Pre-ET2677298-usp_CacheNotification_Sel.sql).
ii) Copy and paste the contents of NotePad to the SQL Query window.
iii) Execute the contents of the SQL Query window to return the stored procedure back to its pre-hotfix content.

3)  When all CA and DA Customer databases to which the hotfix is to be applied have been returned to their pre-hotfix condition, close SQL Server Management Studio and log off of the SQL Server.

- On the CA or DA  Server

4)  Start EVAMS.

- On a workstation with the CA or DA  Client installed

5)  Log onto the workstation with an account that has the permissions to modify role assignments.

6)  Modify any row and save the change to confirm the hotfix has been removed.


Attachments

Hotfix for Etrack 2677298 - Attempting to add role
CADA_9.0.2_Etrack_2677298 - Attempting to add roles thorws error Incorrect syntax near.zip (6 kBytes)

Supplemental Materials

SourceETrack
Value2677298
Description

CA: Attempting to add roles to Monitored Employees throws error: Incorrect syntax near '*'




Article URL http://www.symantec.com/docs/TECH181411


Terms of use for this information are found in Legal Notices