Exception from HRESULT: 0x80131904 (Column Index Issue)
A frequently-accessed production list suddenly started exhibiting strange behaviors as users (and automated workflows!) attempted to edit existing items. Users could add new items and delete existing items from the list without issue, using both datasheet views and the list forms. However, if users tried to update existing items (evenly a newly created item), SharePoint would exhibit the following errors:
When editing in a datasheet view:
When editing in the standard (non-customized) list form OR customized InfoPath edit form:
This behavior did not replicate onto any other list in the site collection, but remained localized to this single, critical list (naturally).
The Investigation
Since the errors occurred in both datasheet views and the list edit forms, the root cause could not be limited to InfoPath forms services (since the list in question uses custom InfoPath display, edit, and new forms).
Strangely, the error message in datasheet view does not provide a correlation ID. Fortunately, the more detailed error message in form mode does provide a correlation ID. Using those correlation IDs, our troubleshooting research uncovered several frontend server log entries that harken to SQL server exceptions. At this point, the server logging level was set to HIGH.
High Batchmgr Method error. Errorcode: 0x564aa500. Error message: The operation failed because an unexpected error occurred. (Result Code: 0x80131904)Okay, we’ve found the matching error code, now let’s look a bit deeper…
High SqlError: 'The variable name '@CmpIndexValue1' has already been declared. Variable names must be unique within a query batch or stored procedure.' Source: '.Net SqlClient Data Provider' Number: 134 State: 1 Class: 15 Procedure: '' LineNumber: 3 Server: 'XXXXXXXXXXX-REDACTED-XXXXXXXXXXXXX'<em>Critical Unknown SQL Exception 134 occurred. Additional error information from SQL Server is included below.The variable name '@CmpIndexValue1' has already been declared. Variable names must be unique within a query batch or stored procedure. System.Data.SqlClient.SqlException: The variable name '@CmpIndexValue1' has already been declared. Variable names must be unique within a query batch or stored procedure. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, BooleanSystem.Data.SqlClient.SqlConnection.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.ExecuteReader(CommandBehavior behavior) at Microsoft.SharePoint.Utilities.SqlSession.ExecuteReader(SqlCommand command, CommandBehavior behavior, SqlQueryData monitoringData, Boolean retryForDeadLock)Bingo! So in the heat of the moment, we surmised that this SQL exception might be caused by a database error, or worse, database corruption (a number of forum posts across the internet seem to suggest this idea). A colleague ran some queries on the SQL server to try to locate this “CmpIndexValue1” thinking that it might be part of stored procedure, but to no avail. It is here that we engaged Microsoft support to investigate further.
After reproducing the issue with Microsoft support, we sent off a VERBOSE log dump for analysis. A short while later, the technician narrowed the possible culprits down to one (or more) of the list column indices, which are defined in the list settings.
Root Cause
System.Data.SqlClient.SqlException: The variable name '@CmpIndexValue1' has already been declared. Variable names must be unique within a query batch or stored procedure.
We then proceeded to examine each of the six indexes I had configured when the list was originally created. From the six, only one was a compound index of a number column (primary) and date column (secondary). Based on the log entries that reference “@CmpIndexValue1” this compound list index must be the source of our troubles.
First, we verified that this type of compound index is officially supported per the SharePoint documentation on TechNet. Second, we considered the data values stored in each column. The number column contained values ranging from 0 to 99,999 (integers only), and the date column contained only date values (no time per the column settings). The latter detail was less important, as Microsoft supports both date and time values when indexing the column. Values in either column were not unique, and duplicates were definitely present in the list.
Final Resolution
With the suspected index in our cross-hairs, I deleted the compound index and tried to edit some list items. Both avenues worked flawlessly, via datasheet and via the list forms. I then recreated the compound index, and the edit issue reappeared on cue. Since the compound index was created to meet a future business requirement, the support technician suggested I recreate the index, but switch the primary and secondary columns. Unfortunately, selecting the date column as primary then disables the selection of secondary column. As a workaround, I created two simple indexes, one for the number column and one for the date column. This did not cause the edit issue to return, so we considered the case resolved.
At best, we concluded that the compound index is fully supported, but somewhere among the thousands of data values in these two columns, SharePoint did not tolerate one or more of them while building the compound index. At the end of the day, I was relieved to have a business-critical list fully functional again, and only mildly disappointed to have to adjust some list views in preparation for the inevitable march above and beyond 5,000 items.
Thanks for a post, solved my problem
Glad it helped!
“Values in either column were not unique, and duplicates were definitely present in the list.” seems irrelevant, as the error is about duplicate @variable declarations. Otherwise, thanks for posting, quite helpful!
By the way, looks like such indices can be created automatically when Metadata navigation and filtering is enabled on the list.