Feeds:
Posts
Comments

Posts Tagged ‘String or binary data would be truncated’

‘Verbose Truncation Warnings’ is one of the greatest features launched in SQL Server 2019. I have been waiting for this feature for quiet a long time because troubleshooting was a nightmare in such cases. This feature can literally save a lot of time while importing, inserting & updating huge amount of data. Let me cut short the story.

Let me create a sample database and a table to demonstrate the issue in earlier version (Any older version than SQL Sever 2019) of SQL Server Step by Step.

Step 1 : 

Let’s create a database and set the compatibility to 140 (SQL Server 2017).

USE [master]
GO
--Create Sample Database
CREATE DATABASE [Sample DB 2017]
GO
ALTER DATABASE  [Sample DB 2017]
SET COMPATIBILITY_LEVEL = 140  -- SQL Server 2017
GO

Step 2 : 

Let’s create a sample table and insert few records.

USE [Sample DB 2017]
GO
CREATE TABLE [dbo].[tbl_Color](
	[Color ID] [int] IDENTITY(1,1) NOT NULL,
	[Color Name] [varchar](3) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tbl_Color]
           ([Color Name])
     VALUES
	       ('Red'),
	       ('Blue'),
	       ('Green')
GO
--OUTPUT

 

Verbose Truncation Warnings-1

This is general error message which shows that one or more than one Table’s column size is less than the actual data limit. In other words, the data size is exceeding the Tables’ column size. The issue is, it does not tell you which column and which data is exceeding the limit. Lets say if you are inserting or updating more than 1 million rows in a table which has more than 50 cloumns. Nightmare right ?

Now, you should not worry anymore because this issue has been fixed in SQL Server 2019. 

Let me demonstrate step by step. How it’s fixed in SQL Server 2019 and make it very simple for us.

Step 1 : 

Let’s create a database in SQL Server 2019 by default the compatibility is 150.

USE [master]
GO
--Create Sample Database
CREATE DATABASE [Sample DB 2019]
GO

Step 2 : 

Let’s create a sample table and insert few records

 
USE [Sample DB 2019]
GO
CREATE TABLE [dbo].[tbl_Color](
	[Color ID] [int] IDENTITY(1,1) NOT NULL,
	[Color Name] [varchar](3) NULL
) ON [PRIMARY]
GO

INSERT INTO [dbo].[tbl_Color]
           ([Color Name])
     VALUES
		   ('Red'),
		   ('Blue'),
		   ('Green')
GO
--OUTPUT

Verbose Truncation Warnings-2

As we can see in the above output which is in SQL Server 2019, the error message is very precise and does not only show the database name, table name & column name but also shows which data is exceeding the limit. In the above case ‘Blue’ in Column ‘Color Name’ is exceeding the column size which is varchar(3).

I hope this feature will make your troubeshooting a lot more easier. Do let me know your feedback about this feature. 

Read Full Post »

String or binary data would be truncated (Error number 8152) is a very common error. It usually happens when we try to insert any data in string (varchar,nvarchar,char,nchar) data type column which is more than size of the column. So you need to check the data size with respect to the column width and identify which column is creating problem and fix it. It is very simple if you are dealing with less columns in a table. But it becomes nightmare if you are dealing with inert into query with huge number of columns and you need to check one by one column. I received this query from one of my Blog readers Mr Ram Kumar asking if there is a shortcut to resolve this issue and give the column name along with the data creating problems. I started searching for the solution but could not get proper one. So I started developing this solution.
Before proceeding with the solution, I would like to create a sample to demonstrate the problem.

SAMPLE :

--This script is compatible with SQL Server 2005 and above.
--DROP TABLE tbl_sample
--GO
CREATE TABLE tbl_sample
(
 [ID] INT,
 [NAME] VARCHAR(10),
)
GO
INSERT INTO tbl_sample VALUES (1,'Bob Jack Creasey')
GO
INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,'Frank Richard Wedge')
GO
--OUTPUT

Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.
Msg 8152, Level 16, State 14, Line 2
String or binary data would be truncated.
The statement has been terminated.

SOLTUION :
Given below is the stored procedure that can find the exact column name and its data which is exceeding the limit of column width.

--DROP PROCEDURE usp_String_or_binary_data_truncated
--GO
CREATE PROCEDURE usp_String_or_binary_data_truncated
@String VARCHAR(MAX)
AS

DECLARE @VARCHAR AS VARCHAR(MAX)
DECLARE @Xml AS XML
DECLARE @TCount AS INT
SET @String= REPLACE(REPLACE(REPLACE(REPLACE(@String,'''','')
             ,'[',''),']',''),CHAR(13) + CHAR(10),'')
SET @Xml = CAST(('<a>'+REPLACE(@String,'(','</a><a>')
           +'</a>') AS XML)

SELECT @TCount=COUNT(*)
FROM @Xml.nodes('A') AS FN(A)

;WITH CTE AS
     (SELECT
     (CASE
	 WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))>0)
     THEN 1
     WHEN CHARINDEX('VALUES',A.value('.', 'varchar(max)'))>0
     THEN 2
	 WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
     AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
     AND @TCount=2  THEN 2
	 WHEN (CHARINDEX('INSERT INTO',A.value('.', 'varchar(max)'))=0
     AND CHARINDEX('VALUES',A.value('.', 'varchar(max)'))=0)
     AND @TCount=3  THEN 3
     END) AS[Batch Number],
     REPLACE(REPLACE(A.value('.', 'varchar(max)')
     ,'INSERT INTO',''),'VALUES ','') AS [Column]
     FROM @Xml.nodes('A') AS FN(A))

, [CTE2] AS
(
    SELECT
	[Batch Number],
	CAST('' + REPLACE([Column], ',' , '')
    + '' AS XML)
    AS [Column name And Data]
    FROM  [CTE]
)
,[CTE3] AS
(
    SELECT [Batch Number],
	ROW_NUMBER() OVER(PARTITION BY [Batch Number]
    ORDER BY [Batch Number] DESC) AS [Row Number],
    Split.a.value('.', 'VARCHAR(MAX)') AS [Column name And Data]
FROM [CTE2]
CROSS APPLY [Column name And Data].nodes('/M')Split(A))

SELECT
 ISNULL(B.[Column name And Data],C.name) AS [Column Name]
,A.[Column name And Data] AS [Column Data]
,C.max_length As [Column Length]
,DATALENGTH(A.[Column name And Data])
AS [Column Data Length]

FROM [CTE3] A
LEFT JOIN [CTE3] B
ON A.[Batch Number]=2 AND B.[Batch Number]=3
AND A.[Row Number] =B.[Row Number]
LEFT JOIN sys.columns C
ON C.object_id =(
	SELECT object_ID(LTRIM(RTRIM([Column name And Data])))
	FROM [CTE3] WHERE [Batch Number]=1
)
AND (C.name = B.[Column name And Data]
OR  (C.column_id =A.[Row Number]
And A.[Batch Number]<>1))
WHERE a.[Batch Number] <>1
AND DATALENGTH(A.[Column name And Data]) >C.max_length
AND C.system_type_id IN (167,175,231,239)
AND C.max_length>0

GO

EXAMPLE :
Now, you simply need to replace all single quotes of your insert into query to double quotes and pass it into the stored procedure.
Given below is the sample.

EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample VALUES (1,''Bob Jack Creasey'')'
GO
EXEC usp_String_or_binary_data_truncated 'INSERT INTO tbl_sample ([ID],[NAME]) VALUES (2,''Frank Richard Wedge'')'
GO
--OUTPUT

string or binary data truncated.1.1

As you can see above, it returned only the column name(s) whose data sizes exceed the limit of the column width.
Do let me know if you come across situation like that and resolve it in a different ways.

Read Full Post »