Error "String or binary data would be truncated" from Microsoft SQL Server and Microsoft Team Foundation Server


Source: Microsoft Support

RAPID PUBLISHING


RAPID PUBLISHING ARTICLES PROVIDE INFORMATION DIRECTLY FROM WITHIN THE MICROSOFT SUPPORT ORGANIZATION. THE INFORMATION CONTAINED HEREIN IS CREATED IN RESPONSE TO EMERGING OR UNIQUE TOPICS, OR IS INTENDED SUPPLEMENT OTHER KNOWLEDGE BASE INFORMATION.

Action




You are trying to use the work item tracking feature of Microsoft Team Foundation Server (TFS) 2005, but there is a Red-X on the Work Items node in Team Explorer. In addition, you may see crash reports from Team Foundation Error Reporting and an error similar to the following in the application event log on the TFS application tier machine:


<ERROR>
Exception: SqlException
Message: String or binary data would be truncated.
The statement has been terminated.
SQL Exception Class: 16
SQL Exception Number: 8152
SQL Exception Server: <SQL SERVER NAME>
SQL Exception Source: .Net SqlClient Data Provider
SQL Exception State: 13
SQL Exception Procedure: trgLogDDLEvent
SQL Exception Line Number: 27
SQL Error: System.Data.SqlClient.SqlError: The statement has been terminated.
            Class: 0
            Number: 3621
            Server: <SQL SERVER NAME>
            Source: .Net SqlClient Data Provider
            State: 0
            Procedure:
            Line Number: 1
Exception Data Dictionary follows
HelpLink.ProdName = Microsoft SQL Server
HelpLink.ProdVer = 09.00.3054
HelpLink.EvtSrc=MSSQLServer
HelpLink.EvtID = 8152
HelpLink.BaseHelpUrl = http://go.microsoft.com/fwlink
HelpLink.LinkId = 20476
Stack Trace:
   at Microsoft.TeamFoundation.WorkItemTracking.Server.SqlAccess.HandleDalError(Exception e)
   at Microsoft.TeamFoundation.WorkItemTracking.Server.SqlAccess.ExecuteBatchPayloadImpl(IRequestContext context, String sqlBatch, List`1 parameterList, Boolean& errorOnBulkUpdate, String connectionString)
   at Microsoft.TeamFoundation.WorkItemTracking.Server.SqlBatchBuilder.ExecuteBatchInternal(IRequestContext context, Boolean passInConnectionInfo, String server, String database)
   at Microsoft.TeamFoundation.WorkItemTracking.Server.DataAccessLayerImpl.GetMetadata(IRequestContext context, String serverName, String databaseName, String userSid, MetadataTable[] tablesRequested, Int64[] rowVersions, Payload metadataPayload, Int32& locale, Int32& comparisonStyle, String& callerIdentity, String& dbStamp, Int32& mode)
   at Microsoft.TeamFoundation.WorkItemTracking.Server.ClientService.GetMetadataImpl(MetadataTableHaveEntry[] metadataHave, Boolean useMaster, Payload& metadata, String& dbStamp, Int32& locale, Int32& comparisonStyle, String& callerIdentity, String& callerIdentitySid, Int32& mode)
</ERROR>

Cause




In this case, a trigger named "trgLogDDLEvent" was added to the TfsWorkItemTracking database. This trigger is not part of the TFS product and is impeding it's operations.


Note that the SQL trigger could be any name. The actual name would be listed in the "SQL Exception Procedure" Application event log error.

Resolution


Use SQL Management Studio to remove the offending trigger from the TfsWorkItemTracking database. (Programmability -> Database Triggers -> Right Click on Trigger -> Delete).

DISCLAIMER


MICROSOFT AND/OR ITS SUPPLIERS MAKE NO REPRESENTATIONS OR WARRANTIES ABOUT THE SUITABILITY, RELIABILITY OR ACCURACY OF THE INFORMATION CONTAINED IN THE DOCUMENTS AND RELATED GRAPHICS PUBLISHED ON THIS WEBSITE (THE “MATERIALS”) FOR ANY PURPOSE. THE MATERIALS MAY INCLUDE TECHNICAL INACCURACIES OR TYPOGRAPHICAL ERRORS AND MAY BE REVISED AT ANY TIME WITHOUT NOTICE.


TO THE MAXIMUM EXTENT PERMITTED BY APPLICABLE LAW, MICROSOFT AND/OR ITS SUPPLIERS DISCLAIM AND EXCLUDE ALL REPRESENTATIONS, WARRANTIES, AND CONDITIONS WHETHER EXPRESS, IMPLIED OR STATUTORY, INCLUDING BUT NOT LIMITED TO REPRESENTATIONS, WARRANTIES, OR CONDITIONS OF TITLE, NON INFRINGEMENT, SATISFACTORY CONDITION OR QUALITY, MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THE MATERIALS.