-->
ZDIRY-TUFWT-EBONM-EYJ00-IDBLANTER.COM
ZDIRY-TUFWT-EBONM-EYJ00

BLANTERWISDOM105

Solusi Terbaik Untuk Mengatasi String or binary data would be truncated

10/22/2020

Problem

String or binary data would be truncated (Error number 8152) adalah kesalahan yang sangat umum. Ini biasanya terjadi ketika kita mencoba memasukkan data apa pun dalam kolom tipe data string (varchar, nvarchar, char, nchar) yang lebih dari length kolom. Jadi, Anda harus memeriksa lebar kolom dan mengidentifikasi kolom mana yang menimbulkan masalah dan memperbaikinya. Ini akan sangat sederhana apabila table yang dimaksud memiliki kolom yang sedikit. Namun menjadi mimpi masalah Ketika anda mencoba insert ke dalam query dengan jumlah kolom yang banyak dan Anda perlu memeriksa satu per satu kolom. Saya menemukan cara ini setelah membaca beberapa referensi pada mbah google bagaimana cara pintas untuk mengatasi masalah ini agar dapat dengan mudah mengidentifikasi kolom mana yang menjadi masalah.

Solusi

Sebelum melanjutkan dengan solusi, saya ingin membuat contoh untuk mendemonstrasikan masalahnya.


CREATE TABLE TestTruncated
(
 [ID] INT,
 [NAME] VARCHAR(10),
 [Address] varchar(50)
)
GO
INSERT INTO TestTruncated VALUES (1,'Agung Panduan Kasep Pisan','cianjur')
GO
INSERT INTO TestTruncated ([ID],[NAME],[Address]) VALUES (2,'Saha ieu teh','Bandung')
GO

Jika kita execute maka akan menghasilkan pesan bahwa data tidak bisa di insert disebabkan truncated.

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 1

String or binary data would be truncated.

The statement has been terminated.

Dibawah ini adalah sebuah stored procedures yang dapat menemukan nama kolom yang menjadi masalah truncate dengan tepat saat akan melakuakn insert.


CREATE PROCEDURE Find_truncated_column
    -- =============================================  
    -- Author:  Agung Panduan  
    -- Create date: <2020 10 22>  
    -- Description: Find Column Truncated
	-- Website: AgungPanduan.com  
    -- =============================================  
	@string varchar(max),
	@targetTable varchar(max)
AS
BEGIN
	SET NOCOUNT ON;

	DECLARE @@pattern varchar(10)
	DECLARE @@NewString varchar(max)
	set @@pattern='('

    --kita akan mengambil data
	set @string =REPLACE(REPLACE(REPLACE(REPLACE(@string,'''','')
		,'[',''),']',''),CHAR(13) + CHAR(10),'')

	set @@NewString =(SELECT REPLACE(REVERSE(SUBSTRING(REVERSE(@string), 1, 
           CHARINDEX(@@pattern, REVERSE(@string), 1) - 1)),')',''))
	
	-- jadi kan sebuah ro
	-- SELECT * FROM dbo.fnSplitString(@@NewString, ',')
	-- terus menghitung length masing-masing data
	-- select splitdata, len(splitdata) as [length] from (SELECT * FROM dbo.fnSplitString(@@NewString, ','))a	
	-- agungpanduan.com
	-- bandingkan dengan length dari table
	SELECT Table_Name, Column_Name, Character_maximum_length, z.Data_Length,
	case when Character_maximum_length < z.Data_Length then 'truncated' end as Status_Truncated
	FROM INFORMATION_SCHEMA.Columns as b
	inner join (select row_number() over (order by splitdata asc) rn, splitdata, len(splitdata) as [Data_Length] from (SELECT * FROM dbo.fnSplitString(@@NewString, ','))a)z
	on z.rn = b.ORDINAL_POSITION
	WHERE Table_Name =@targetTable
	order by b.ORDINAL_POSITION asc

	-- berdasarkan row
END

Tidak hanya stored procedures yang dibutuhkan tetapi function juga yaitu;


CREATE FUNCTION [dbo].[fnSplitString] 
( 
    @string NVARCHAR(MAX), 
    @delimiter CHAR(1) 
) 
RETURNS @output TABLE(splitdata NVARCHAR(MAX) 
) 
BEGIN 
    DECLARE @start INT, @end INT 
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @string) 
    WHILE @start < LEN(@string) + 1 BEGIN 
        IF @end = 0  
            SET @end = LEN(@string) + 1
       
        INSERT INTO @output (splitdata)  
        VALUES(SUBSTRING(@string, @start, @end - @start)) 
        SET @start = @end + 1 
        SET @end = CHARINDEX(@delimiter, @string, @start)
        
    END 
    RETURN 
END

Sekarang, Anda hanya perlu mengganti semua tanda kutip tunggal dari sisipan Anda ke dalam query menjadi tanda kutip ganda dan meneruskannya ke prosedur tersimpan.


EXEC Find_truncated_column 
@String='INSERT INTO TestTruncated VALUES (1,''Agung Panduan Kasep Pisan'',''cianjur'')',
@targetTable='TestTruncated'
--atau
EXEC Find_truncated_column 
@String='INSERT INTO TestTruncated ([ID],[NAME],[Address]) VALUES (1,''Agung Panduan Kasep Pisan'',''cianjur'')',
@targetTable='TestTruncated'

Seperti yang Anda lihat di bawah ini, bahwa hasilnya akan menampilkan nama table, maximum length dari kolom, data length untuk data, serta status _truncated.

Share This :

2 Comments

  1. Mas, klo truncated saat sedang import data banyak gmn ya sebaiknya untuk mencari data yang ga clean tersebut?

    BalasHapus
    Balasan
    1. itu blm saya kembangkan lagi, mungkin nanti saya update untuk insert data dari table temporary ke table target.. Makasih atas kunjungannya

      Hapus