Users with Long Names Cannot Delete Items in Sitecore

17.12.2019 by Stefan Busch

How an overly restrictive default in Sitecore's database may result in cryptic error messages.

Sitecore has a limit for the length of the username for archived items in the database. The field is of the type nvarchar (50). This can be a problem because the limit of the username length in the core DB is set to nvarchar(256). Usually, this is not a problem for normal names, but there are cases, where this is not enough. If you ever face an error like this when a user tries to delete an item:

# sitecore log
String or binary data would be truncated.
The statement has been terminated.

Server Error in '/' Application.
String or binary data would be truncated.
The statement has been terminated.
Description: String or binary data would be truncated. The statement has been terminated.

Exception Details: System.Data.SqlClient.SqlException: String or binary data would be truncated.
The statement has been terminated.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:


[SqlException (0x80131904): String or binary data would be truncated.
The statement has been terminated.]
   System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +2555922
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5959184
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +285
   System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +4169
   System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted) +255
   System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest) +2598
   System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +1483
   System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry) +374
   System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +286
   Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery() +50

[DataException: Error executing SQL command:  INSERT INTO [Archive] (
                  [ArchivalId], [ItemId], [ParentId], [Name], [OriginalLocation], [ArchiveDate], [ArchivedBy], [ArchiveName]
                )
                VALUES (
                  @archivalId, @itemId, @parentId, @name, @originalLocation, @archiveDate, @archivedBy, @archiveName
                )]

[Exception: String or binary data would be truncated.
The statement has been terminated.]
   Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteNonQuery() +183
   Sitecore.Data.DataProviders.Sql.<>c__DisplayClass15.b__14() +57
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +150
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action) +33
   Sitecore.Data.DataProviders.Sql.SqlDataApi.Execute(String sql, Object[] parameters) +124
   Sitecore.Data.Archiving.SqlArchive.GetArchival(Item item, ID& id) +718
   Sitecore.Data.Archiving.<>c__DisplayClass2.b__0() +23
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action, Action recover) +150
   Sitecore.Data.DataProviders.NullRetryer.Execute(Func`1 action) +33
   Sitecore.Data.Archiving.SqlArchive.ArchiveItem(Item item) +450
   Sitecore.Data.Items.Item.Recycle() +220
   Sitecore.Shell.Framework.Pipelines.DeleteItems.Delete(List`1 items) +236
   Sitecore.Shell.Framework.Pipelines.DeleteItems.Execute(ClientPipelineArgs args) +87

You should try to increase the max characters for the field "ArchivedBy" in the table “Archive” and “ArchivedVersions” in the master database. To do this you can run the following commands on the SQL DB.

# T-SQL
ALTER TABLE Archive ALTER COLUMN ArchivedBy nvarchar(max) NOT NULL;
ALTER TABLE ArchivedVersions ALTER COLUMN ArchivedBy nvarchar(max) NOT NULL;

This solution worked for us on a Sitecore 8.2 environment. We didn’t try the solution for Sitecore 9. If you check the Database of Sitecore 9 you will see that there is the same limit in the database. Therefore, the solution should work for Sitecore 9 as well.

Stefan Busch

VIU. Einfach die Lösung.

VIU AGRennweg 388001 ZürichCH-Switzerland+41 44 500 96 76

team@viu.ch+41 44 500 96 76

©viu AG

Imprint