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: 2014-08-15  |  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

Product(s)

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.5.  For more information about this release, see the following article:
http://www.symantec.com/docs/DOC6418

Enterprise Vault 10.0.4.  For more information about this release, see the following article:
http://www.symantec.com/docs/DOC6282


WORKAROUND for all versions:

Objective of the workaround is to prevent the end user and housekeeping routines from accessing the Permissions Cache.  This can have a detrimental impact on the client performance.

1. Open the Accelerator Client
2. Configure the end users to not use the Permission Cache by modifying the Permission Cache Option to 0
     a. Click on the Configuration tab
     b. Click on the Settings sub-tab.
     c. Hold the Ctrl key and click on "Configuration Settings" in the banner area to display the hidden settings.
     d. Expand the Security folder
     e. Locate and click on the Permission Cache Option option's Value column entry.
     f. Change the value from the default of 2 to 0.
3. Configure housekeeping routines to not update the Permission Cache by modifying the Cache Policies File.
     a. Expand the System folder.
     b. Locate the Cache Policies File option and click on 'Save as ...' in its Value column.
     c. Browse to the drive and folder to contain the Cache Policies configuration information.
     d. Save the file using a file name to reflect the contents or leave the default file name in place (default file name is 'Cache Policies File.txt').  Note: If the file does not already exist, a dialog box will be displayed requesting confirmation that the file is to be created.  Click the Yes button in this dialog box to create the file.
     e. Access the saved file via Note Pad.
     f. Modify the file as depicted in Bold then save and close the file.

<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.
     a. If the focus of the Accelerator Client has moved from the Settings sub-tab, access that sub-tab again using the steps above, including showing the hidden settings. System | (hidden setting) Cache Policies File
     b. In the System folder, Cache Policies File option link, click on the Browse... button and navigate to the modified file.
     c. Open the modified file.
     e. Press the Save button in the lower right of the screen.
5. Restart Enterprise Vault Accelerator Manager Service.
6. For Compliance Accelerator only, restart all Journal Tasks on all Enterprise Vault Journaling Servers on which the Journal Connector has been installed and activated.
7. Reopen the Accelerator Clients

 


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 '*'


SourceEvent ID
Value100
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)

 


SourceUMI
ValueV-437-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)

 




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


Terms of use for this information are found in Legal Notices