vRealize Automation generates the following error: "String or binary data would be truncated"
search cancel

vRealize Automation generates the following error: "String or binary data would be truncated"

book

Article ID: 326152

calendar_today

Updated On:

Products

VMware Aria Suite

Issue/Introduction

  • This script will increase the storage size of the [Message] column in the [dbo].[UserLog] table. This is necessary to prevent "String or binary data would be truncated." errors when inserting into this table.


Symptoms:
  • The following error is generated:  "String or binary data would be truncated"



Environment

VMware vRealize Automation 7.3.x
VMware vRealize Automation 6.2.x
VMware vRealize Automation 7.2.x
VMware vRealize Automation 7.1.x
VMware vRealize Automation 7.0.x

Cause

  • The column [Message] on the table [dbo].[UserLog] is too small. It is defined as NVARCHAR(2000).

Resolution

  • This issue is resolved in vRealize Automation 7.4.


Workaround:
  • Run the below SQL statement to increase the column size and update one stored procedure:
    • /**********************************************************************************************************
      Notes:
      Alter statements are used, so there is no downtime. And more importantly no permissions on these
      objects are changed. These statements can be ran anytime. There should be no impact to running these
      on an up and running system.
      **********************************************************************************************************/
      ALTER TABLE [dbo].[UserLog]
      ALTER COLUMN [Message] [nvarchar](4000) NOT NULL
      GO

      ALTER TABLE [dbo].[UserLogArchive]
      ALTER COLUMN [Message] [nvarchar](4000) NOT NULL
      GO

      SET ANSI_NULLS ON
      GO

      SET QUOTED_IDENTIFIER ON
      GO

      ALTER PROCEDURE [dbo].[usp_InsertUserLog]
       @User nvarchar(256),
       @Type tinyint,
       @Message nvarchar(4000),
       @GroupID uniqueidentifier
      AS
      BEGIN
       SET NOCOUNT ON;
       
       INSERT INTO [UserLog]
         ([UserName]
         ,[Timestamp]
         ,[Type]
         ,[Message]
         ,[GroupID])
        VALUES
         (@User
         ,GETUTCDATE()
         ,@Type
         ,@Message
         ,@GroupID)
      END
      GO