SQL Server 문자열 또는 이진 데이터가 잘립니다.
데이터 이행 프로젝트에 종사하고 있습니다.어떤 테이블에서 다른 테이블로 데이터를 삽입하려고 하면 다음 오류가 발생합니다(SQL Server 2005).
16, 13, 1의 8152, 16, 13, 1
문자열 또는 이진 데이터가 잘립니다.
소스 데이터 열은 데이터 유형과 일치하고 대상 테이블 열의 길이 정의 내에 있으므로 이 오류의 원인이 무엇인지 알 수 없습니다.
문제의 위치를 파악하기 위해 소스 테이블과 타깃 테이블의 테이블 정의를 게시해야 합니다.단, 중요한 것은 소스 테이블의 열 중 하나가 타깃 열보다 크다는 것입니다.사용자가 인식하지 못한 방식으로 형식을 변경하고 있을 수 있습니다.이행하는 데이터베이스 모델도 이 점을 파악하는 데 중요합니다.
다른 사용자가 이미 언급했듯이 원본 테이블의 열 데이터 유형 중 하나가 대상 열보다 큽니다.
간단한 해결책은 경고를 끄고 잘라내는 것입니다.따라서 이 오류가 발생하지만 이전 데이터베이스/테이블의 데이터를 잘라내는(크기에 맞게 잘라내는) 것이 허용된다고 확신하는 경우 다음 작업을 수행할 수 있습니다.
SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;
위와 같이 경고 후에는 반드시 다시 켜야 합니다.
문제는 매우 간단합니다. 소스 쿼리의 하나 이상의 열에 대상 열의 길이를 초과하는 데이터가 포함되어 있습니다.은 소스 해 보는 입니다.Max(Len( source col )),,,,,,,,,,,. 즉 ,,,,
Select Max(Len(TextCol1))
, Max(Len(TextCol2))
, Max(Len(TextCol3))
, ...
From ...
그런 다음 이러한 길이를 대상 테이블의 데이터 유형 길이와 비교합니다.하나 이상의 이 대상 열 길이를 초과합니다.
그렇지 않다고 확신하고, 그렇지 않다고 해도 상관하지 않는 경우에는 소스 쿼리 열을 대상 길이로 강제 캐스트하는 방법도 있습니다(너무 긴 데이터는 잘라냅니다).
Select Cast(TextCol1 As varchar(...))
, Cast(TextCol2 As varchar(...))
, Cast(TextCol3 As varchar(...))
, ...
From ...
SQL Server 2019가 드디어 더 의미 있는 오류 메시지를 반환합니다.
이진 또는 문자열 데이터가 잘립니다. => 오류 메시지 확장
(실가동 중) 오류가 발생한 경우 오류가 발생한 열 또는 행과 정확한 위치를 알 수 없습니다.
, 「」를 사용할 .DBCC TRACEON(460)에서의 sys.messages:
SELECT * FROM sys.messages WHERE message_id = 2628
2628 – 문자열 또는 이진 데이터는 '%' 표에서 잘립니다.*ls', 열 '%'*ls. 잘린 값: '%'.*ls'입니다.
문자열 또는 이진 데이터가 잘립니다. 악명 높은 오류 8152를 바꿉니다.
이 새 메시지는 SQL Server 2017 CU12(및 곧 출시될 SQL Server 2016 SP2 CU)에도 백포트되지만 기본적으로는 그렇지 않습니다.메시지 ID 8152를 2628로 대체하려면 트레이스 플래그 460을 세션레벨 또는 서버레벨 중 하나로 유효하게 할 필요가 있습니다.
지금은 SQL Server 2019 CTP 2.0에서도 동일한 트레이스 플래그 460을 활성화해야 합니다.향후 SQL Server 2019 릴리스에서는 기본적으로 메시지 8152가 메시지 2628로 대체됩니다.
SQL Server 2017 CU12도 이 기능을 지원합니다.
개선점: SQL Server 2017에서 "String or binary data would be truncutted" 메시지를 확장 정보와 함께 선택적으로 대체
이 SQL Server 2017 업데이트에서는 다음과 같은 추가 컨텍스트 정보가 포함된 선택적 메시지가 도입됩니다.
Msg 2628, Level 16, State 6, Procedure ProcedureName, Line Linenumber String or binary data would be truncated in table '%.*ls', column '%.*ls'. Truncated value: '%.*ls'.새 메시지 ID는 2628입니다.트레이스 플래그 460이 네이블일 경우 오류 출력 메시지 8152가 이 메시지로 대체됩니다.
VERBOSE_TRUNCATION_WARNGS = { ON | OFF }
적용 대상: SQL Server (SQL Server 2019 (15.x) 이후)및 Azure SQL 데이터베이스
새 문자열 또는 이진 데이터가 잘린 오류 메시지를 사용하거나 사용하지 않도록 설정할 수 있습니다.SQL Server 2019(15.x)에서는 이 시나리오에 대해 보다 구체적인 새로운 오류 메시지(2628)가 도입되었습니다.
String or binary data would be truncated in table '%.*ls', column'%.*ls'. Truncated value: '%.*ls'.데이터베이스 호환성 수준 150에서 ON으로 설정하면 잘라내기 오류에 의해 새로운 오류 메시지 2628이 표시되므로 더 많은 컨텍스트를 제공하고 문제 해결 프로세스를 단순화할 수 있습니다.
데이터베이스 호환성 수준 150에서 OFF로 설정하면 잘라내기 오류로 인해 이전 오류 메시지 8152가 발생합니다.
데이터베이스 호환성 수준 140 이하의 경우 오류 메시지 2628은 트레이스 플래그 460을 활성화해야 하는 옵트인 오류 메시지로 남습니다.이 데이터베이스 범위 설정은 영향을 주지 않습니다.
이 밖에 컬럼의 길이를 초과하는 컬럼에 대해 디폴트값이 설정되어 있는 경우도 생각할 수 있습니다.길이가 5인 열을 뚱뚱하게 손가락질했지만 기본값은 길이 5를 초과한 것으로 보입니다.1의 정수를 가진 하나의 컬럼만 삽입해도 왜 삽입이 안 되는지를 이해하려고 노력했기 때문에 저는 매우 당황했습니다.테이블 스키마의 디폴트 값은 디폴트값을 위반하기 때문에 모든 것이 엉망이 되어버렸습니다.이것에 의해, 학습한 교훈이 됩니다만, 스키마에 디폴트값이 있는 테이블이 있는 것은 피할 수 있습니다.:)
여기 조금 다른 답이 있다.열 이름과 길이가 모두 일치할 수 있지만 SELECT 문에서 열을 잘못된 순서로 지정했을 수 있습니다.예를 들어 tableX와 tableY에는 이름은 같지만 순서가 다릅니다.
이 에러의 다른 생각할 수 있는 원인을 추가하겠습니다.이것은, 아무도 이 에러를 언급하지 않았기 때문에, 장래의 사람에게 도움이 될 가능성이 있기 때문입니다(OP가 답을 찾아냈기 때문입니다).삽입하려는 테이블에 트리거가 있는 경우 트리거가 오류를 생성하고 있을 수 있습니다.테이블 필드의 정의가 변경되었을 때 이러한 현상이 발생하는 것을 보았습니다만, 감사 테이블은 변경되지 않았습니다.
SQL Server 2016-2017: 수정하려면 추적 플래그 460을 켜십시오.
DBCC TRACEON(460, 1);
GO
다음 시간 후에 꺼야 합니다.
DBCC TRACEOFF(460, 1);
GO
그 외의 경우는, 보존 프로시저도 확인해 주세요.저장 프로시저의 경우CustomSearch실수로 컬럼 길이가 부족하다고 선언했기 때문에 빅 데이터를 입력하면 데이터베이스에 큰 길이가 있는데도 오류가 발생했습니다.사용자 지정 검색에서 열 길이를 변경했는데 오류가 사라집니다.★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★★감사해요.
이는 어려운 오류일 수 있습니다.다음은 AmirCharania의 코멘트를 https://connect.microsoft.com/SQLServer/feedback/details/339410/에서 찾아본 메모입니다.
선택된 데이터에 대한 AmirCharania의 답변을 임시 테이블이 아닌 실제 테이블로 조정했습니다.먼저 데이터 세트를 개발 테이블로 선택한 후 다음을 실행합니다.
WITH CTE_Dev
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TARGET TABLE NAME HERE, WITH SCHEMA')
)
,CTE_Temp
AS (
SELECT C.column_id
,ColumnName = C.NAME
,C.max_length
,C.user_type_id
,C.precision
,C.scale
,DataTypeName = T.NAME
FROM sys.columns C
INNER JOIN sys.types T ON T.user_type_id = C.user_type_id
WHERE OBJECT_ID = OBJECT_ID('YOUR TEMP TABLE NAME HERE, WITH SCHEMA')
)
SELECT *
FROM CTE_Dev D
FULL OUTER JOIN CTE_Temp T ON D.ColumnName = T.ColumnName
WHERE ISNULL(D.max_length, 0) < ISNULL(T.max_length, 999)
네, 저도 이런 문제에 직면해 있습니다.
REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)
여기서 비고 파일 길이를 500에서 1000으로 변경했습니다.
그래, "반쪽 핀트짜리 냄비에 1파인트는 들어가지 않을 거야.사람들이 제안하는 다양한 SP는 별로 없지만, 두 테이블이 같은 DB(또는 같은 DB로 가져올 수 있음)에 있는 한 INFORMATION_SCHEMA를 사용할 수 있습니다.COLUMNS: 오류 필드를 찾습니다.
select c1.table_name,c1.COLUMN_NAME,c1.DATA_TYPE,c1.CHARACTER_MAXIMUM_LENGTH,c2.table_name,c2.COLUMN_NAME, c2.DATA_TYPE,c2.CHARACTER_MAXIMUM_LENGTH
from [INFORMATION_SCHEMA].[COLUMNS] c1
left join [INFORMATION_SCHEMA].[COLUMNS] c2 on
c1.COLUMN_NAME=c2.COLUMN_NAME
where c1.TABLE_NAME='MyTable1'
and c2.TABLE_NAME='MyTable2'
--and c1.DATA_TYPE<>c2.DATA_TYPE
--and c1.CHARACTER_MAXIMUM_LENGTH <> c2.CHARACTER_MAXIMUM_LENGTH
order by c1.COLUMN_NAME
그러면 위아래로 스크롤하여 필드 길이를 비교할 수 있습니다.코멘트 섹션에서는 데이터 타입의 불일치가 있는 경우(코멘트 없이 명확하게 표시), 또는 필드 길이가 다른 경우(스크롤이 귀찮기 때문에)를 표시할 수 있습니다.이 섹션 전체가 타겟의 이름과 일치하는 소스 컬럼 이름에 근거하고 있는 것에 주의해 주세요.
오늘 이 문제를 발견했는데, 이 최소한의 정보 오류 메시지에 대한 답을 찾다가 다음 링크도 찾았습니다.
따라서 마이크로소프트는 조만간 오류 메시지를 확대할 계획이 없는 것 같습니다.
그래서 나는 다른 방법을 택했다.
오류를 복사하여 우수하게 만들었습니다.
(해당 행 1개)
(해당 행 1개)
(해당 행 1개) Msg 8152, 레벨 16, 스테이트 14, 13행 문자열 또는 바이너리 데이터가 잘립니다.문이 종료되었습니다.
(해당 행 1개)
Excel 행 수를 세어 문제의 원인이 된 레코드 카운터에 접근했습니다.내보내기 코드를 조정하여 SQL을 인쇄했습니다...그런 다음 문제가 있는 sql 주위에 5~10개의 sql 삽입을 실행하여 문제가 있는 sql 삽입을 특정하여 너무 긴 문자열을 확인하고 해당 컬럼의 크기를 늘린 후 큰 Import 파일을 실행하지 않았습니다.
약간의 해킹과 회피책이지만 선택의 여지가 거의 없을 때 할 수 있는 일을 하는 거야.
테이블 작성 시 빈 문자열 "을 사용하다가 이후 업데이트 시 "Msg 8152, String or binary data would buttled" 오류가 발생했습니다.이 문제는 6자로 구성된 업데이트 값이 열 정의보다 크기 때문에 발생했습니다.이 문제를 피하기 위해 "SPACE"를 사용한 이유는 첫 번째 데이터 생성 후 대량으로 업데이트된다는 것을 알았기 때문입니다. 즉, 열이 오래 비어 있지 않을 것입니다.
중요한 경고:이는 특별히 슬릭한 솔루션은 아니지만 데이터 마이닝을 위한 테이블을 작성하고 대량 처리/해석을 적용하여 나중에 비교/마이닝 결과를 저장해야 하는 일회성 인텔리전스 요구 등 데이터 세트를 정리하는 경우에 유용합니다.이것은 제 업무에서 자주 있는 일입니다.
처음에 SPACE 키워드를 사용하여 입력할 수 있습니다.
select
Table1.[column1]
,Table1.[column2]
,SPACE(10) as column_name
into table_you_are_creating
from Table1
where ...
이후 10자 이하의 "column_name"에 대한 업데이트(해당되는 경우 수정)는 잘라내기 오류 없이 허용됩니다.다시 말씀드리지만 경고에 기재된 것과 유사한 시나리오에서만 사용합니다.
최소 길이(min_len)와 최대 길이(max_len)를 포함한 열당 몇 가지 특성을 가진 소스 테이블 또는 쿼리를 분석하는 저장 프로시저를 구축했습니다.
CREATE PROCEDURE [dbo].[sp_analysetable] (
@tableName varchar(8000),
@deep bit = 0
) AS
/*
sp_analysetable 'company'
sp_analysetable 'select * from company where name is not null'
*/
DECLARE @intErrorCode INT, @errorMSG VARCHAR(500), @tmpQ NVARCHAR(2000), @column_name VARCHAR(50), @isQuery bit
SET @intErrorCode=0
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
if CHARINDEX('from', @tableName)>0
set @isQuery=1
IF @intErrorCode=0 BEGIN
if @isQuery=1 begin
--set @tableName = 'USE '+@db+';'+replace(@tableName, 'from', 'into ##tmpTableToAnalyse from')
--replace only first occurance. Now multiple froms may exists, but first from will be replaced with into .. from
set @tableName=Stuff(@tableName, CharIndex('from', @tableName), Len('from'), 'into ##tmpTableToAnalyse from')
exec(@tableName)
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NULL BEGIN
set @intErrorCode=1
SET @errorMSG='Error generating temporary table from query.'
end
else begin
set @tableName='##tmpTableToAnalyse'
end
end
end
IF @intErrorCode=0 BEGIN
SET @tmpQ='USE '+DB_NAME()+';'+CHAR(13)+CHAR(10)+'
select
c.column_name as [column],
cast(sp.value as varchar(1000)) as description,
tc_fk.constraint_type,
kcu_pk.table_name as fk_table,
kcu_pk.column_name as fk_column,
c.ordinal_position as pos,
c.column_default as [default],
c.is_nullable as [null],
c.data_type,
c.character_maximum_length as length,
c.numeric_precision as [precision],
c.numeric_precision_radix as radix,
cast(null as bit) as [is_unique],
cast(null as int) as min_len,
cast(null as int) as max_len,
cast(null as int) as nulls,
cast(null as int) as blanks,
cast(null as int) as numerics,
cast(null as int) as distincts,
cast(null as varchar(500)) as distinct_values,
cast(null as varchar(50)) as remarks
into ##tmpColumns'
if @isQuery=1 begin
SET @tmpQ=@tmpQ+' from tempdb.information_schema.columns c, (select null as value) sp'
end
else begin
SET @tmpQ=@tmpQ+'
from information_schema.columns c
left join sysobjects so on so.name=c.table_name and so.xtype=''U''
left join syscolumns sc on sc.name=c.column_name and sc.id =so.id
left join sys.extended_properties sp on sp.minor_id = sc.colid AND sp.major_id = sc.id and sp.name=''MS_Description''
left join information_schema.key_column_usage kcu_fk on kcu_fk.table_name = c.table_name and c.column_name = kcu_fk.column_name
left join information_schema.table_constraints tc_fk on kcu_fk.table_name = tc_fk.table_name and kcu_fk.constraint_name = tc_fk.constraint_name
left join information_schema.referential_constraints rc on rc.constraint_name = kcu_fk.constraint_name
left join information_schema.table_constraints tc_pk on rc.unique_constraint_name = tc_pk.constraint_name
left join information_schema.key_column_usage kcu_pk on tc_pk.constraint_name = kcu_pk.constraint_name
'
end
SET @tmpQ=@tmpQ+' where c.table_name = '''+@tableName+''''
exec(@tmpQ)
end
IF @intErrorCode=0 AND @deep = 1 BEGIN
DECLARE
@count_rows int,
@count_distinct int,
@count_nulls int,
@count_blanks int,
@count_numerics int,
@min_len int,
@max_len int,
@distinct_values varchar(500)
DECLARE curTmp CURSOR LOCAL FAST_FORWARD FOR
select [column] from ##tmpColumns;
OPEN curTmp
FETCH NEXT FROM curTmp INTO @column_name
WHILE @@FETCH_STATUS = 0 and @intErrorCode=0 BEGIN
set @tmpQ = 'USE '+DB_NAME()+'; SELECT'+
' @count_rows=count(0), '+char(13)+char(10)+
' @count_distinct=count(distinct ['+@column_name+']),'+char(13)+char(10)+
' @count_nulls=sum(case when ['+@column_name+'] is null then 1 else 0 end),'+char(13)+char(10)+
' @count_blanks=sum(case when ltrim(['+@column_name+'])='''' then 1 else 0 end),'+char(13)+char(10)+
' @count_numerics=sum(isnumeric(['+@column_name+'])),'+char(13)+char(10)+
' @min_len=min(len(['+@column_name+'])),'+char(13)+char(10)+
' @max_len=max(len(['+@column_name+']))'+char(13)+char(10)+
' from ['+@tableName+']'
exec sp_executesql @tmpQ,
N'@count_rows int OUTPUT,
@count_distinct int OUTPUT,
@count_nulls int OUTPUT,
@count_blanks int OUTPUT,
@count_numerics int OUTPUT,
@min_len int OUTPUT,
@max_len int OUTPUT',
@count_rows OUTPUT,
@count_distinct OUTPUT,
@count_nulls OUTPUT,
@count_blanks OUTPUT,
@count_numerics OUTPUT,
@min_len OUTPUT,
@max_len OUTPUT
IF (@count_distinct>10) BEGIN
SET @distinct_values='Many ('+cast(@count_distinct as varchar)+')'
END ELSE BEGIN
set @distinct_values=null
set @tmpQ = N'USE '+DB_NAME()+';'+
' select @distinct_values=COALESCE(@distinct_values+'',''+cast(['+@column_name+'] as varchar), cast(['+@column_name+'] as varchar))'+char(13)+char(10)+
' from ('+char(13)+char(10)+
' select distinct ['+@column_name+'] from ['+@tableName+'] where ['+@column_name+'] is not null) a'+char(13)+char(10)
exec sp_executesql @tmpQ,
N'@distinct_values varchar(500) OUTPUT',
@distinct_values OUTPUT
END
UPDATE ##tmpColumns SET
is_unique =case when @count_rows=@count_distinct then 1 else 0 end,
distincts =@count_distinct,
nulls =@count_nulls,
blanks =@count_blanks,
numerics =@count_numerics,
min_len =@min_len,
max_len =@max_len,
distinct_values=@distinct_values,
remarks =
case when @count_rows=@count_nulls then 'all null,' else '' end+
case when @count_rows=@count_distinct then 'unique,' else '' end+
case when @count_distinct=0 then 'empty,' else '' end+
case when @min_len=@max_len then 'same length,' else '' end+
case when @count_rows=@count_numerics then 'all numeric,' else '' end
WHERE [column]=@column_name
FETCH NEXT FROM curTmp INTO @column_name
END
CLOSE curTmp DEALLOCATE curTmp
END
IF @intErrorCode=0 BEGIN
select * from ##tmpColumns order by pos
end
IF @intErrorCode=0 BEGIN --Clean up temporary tables
IF OBJECT_ID('tempdb..##tmpTableToAnalyse') IS NOT NULL BEGIN
DROP TABLE ##tmpTableToAnalyse
END
IF OBJECT_ID('tempdb..##tmpColumns') IS NOT NULL BEGIN
DROP TABLE ##tmpColumns
END
end
IF @intErrorCode<>0 BEGIN
RAISERROR(@errorMSG, 12, 1)
END
RETURN @intErrorCode
다음과 같이 모든 데이터베이스에서 사용할 수 있도록 마스터 데이터베이스에 다음 절차를 저장합니다.
sp_analysetable 'table_name', 1
// deep=1 for doing value analyses
출력은 다음과 같습니다.
column description constraint_type fk_table fk_column pos default null data_type length precision radix is_unique min_len max_len nulls blanks numerics distincts distinct_values remarks
id_individual NULL PRIMARY KEY NULL NULL 1 NULL NO int NULL 10 10 1 1 2 0 0 70 70 Many (70) unique,all numeric,
id_brand NULL NULL NULL NULL 2 NULL NO int NULL 10 10 0 1 1 0 0 70 2 2,3 same length,all numeric, guid NULL NULL NULL NULL 3 (newid()) NO uniqueidentifier NULL NULL NULL 1 36 36 0 0 0 70 Many (70) unique,same length,
customer_id NULL NULL NULL NULL 4 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
email NULL NULL NULL NULL 5 NULL YES varchar 100 NULL NULL 0 4 36 0 0 0 31 Many (31)
mobile NULL NULL NULL NULL 6 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
initials NULL NULL NULL NULL 7 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_short NULL NULL NULL NULL 8 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
title_long NULL NULL NULL NULL 9 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
firstname NULL NULL NULL NULL 10 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
lastname NULL NULL NULL NULL 11 NULL YES varchar 50 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
address NULL NULL NULL NULL 12 NULL YES varchar 100 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
pc NULL NULL NULL NULL 13 NULL YES varchar 10 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
kixcode NULL NULL NULL NULL 14 NULL YES varchar 20 NULL NULL 0 NULL NULL 70 0 0 0 NULL all null,empty,
date_created NULL NULL NULL NULL 15 (getdate()) NO datetime NULL NULL NULL 1 19 19 0 0 0 70 Many (70) unique,same length,
created_by NULL NULL NULL NULL 16 (user_name()) NO varchar 50 NULL NULL 0 13 13 0 0 0 1 loyalz-public same length,
id_location_created NULL FOREIGN KEY location id_location 17 NULL YES int NULL 10 10 0 1 1 0 0 70 2 1,2 same length,all numeric, id_individual_type NULL FOREIGN KEY individual_type id_individual_type 18 NULL YES int NULL 10 10 0 NULL NULL 70 0 0 0 NULL all null,empty,
optin NULL NULL NULL NULL 19 NULL YES int NULL 10 10 0 1 1 39 0 31 2 0,1 same length,
INSERT SELECT 문이 사용되었을 때 텍스트 잘라내기(문자열 또는 이진 데이터가 잘립니다) 문제를 식별하고 해결하는 데 도움이 되는 유용한 저장 절차를 작성했습니다.CHAR, VARCHAR, NCHAR 및 NVARCHAR 필드만 비교하고 오류가 발생할 가능성이 있는 경우 필드별로 평가 필드를 반환합니다.
EXEC dbo.GetFieldStringTruncate SourceTableName, TargetTableName
이 저장 프로시저는 INSERT SELECT 문이 생성될 때 텍스트가 잘리는 문제를 대상으로 합니다.
이 스토어드 프로시저의 동작은 이전에 문제가 있는 INSERT 문을 식별한 사용자에 따라 달라집니다.그런 다음 소스 데이터를 글로벌 임시 테이블에 삽입합니다.SELECT INTO 문이 권장됩니다.
SELECT 문의 각 필드의 에일리어스에 행선지 테이블의 필드의 같은 이름을 사용해야 합니다.
기능 코드:
DECLARE @strSQL nvarchar(1000)
IF NOT EXISTS (SELECT * FROM dbo.sysobjects where id = OBJECT_ID(N'[dbo].[GetFieldStringTruncate]'))
BEGIN
SET @strSQL = 'CREATE PROCEDURE [dbo].[GetFieldStringTruncate] AS RETURN'
EXEC sys.sp_executesql @strSQL
END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
------------------------------------------------------------------------------------------------------------------------
Description:
Syntax
---------------
dbo.GetFieldStringTruncate(SourceTable, TargetTable)
+---------------------------+-----------------------+
| SourceTableName | VARCHAR(255) |
+---------------------------+-----------------------+
| TargetTableName | VARCHAR(255) |
+---------------------------+-----------------------+
Arguments
---------------
SourceTableName
The name of the source table. It should be a temporary table using double charp '##'. E.g. '##temp'
TargetTableName
The name of the target table. It is the table that receives the data used in the INSERT INTO stament.
Return Type
----------------
Returns a table with a list of all the fields with the type defined as text and performs an evaluation indicating which field would present the problem of string truncation.
Remarks
----------------
This stored procedure is oriented to the problem of text truncation when an INSERT SELECT statement is made.
The operation of this stored procedure depends on the user previously identifying the INSERT statement with the problem. Then inserting the source data into a global temporary table. The SELECT INTO statement is recommended.
You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.
Examples
====================================================================================================
--A. Test basic
IF EXISTS (SELECT * FROM sys.objects WHERE OBJECT_ID = OBJECT_ID(N'[dbo].[tblDestino]') AND TYPE IN (N'U'))
DROP TABLE tblDestino
CREATE TABLE tblDestino
(
Id INT IDENTITY,
Field1 VARCHAR(10),
Field2 VARCHAR(12),
Field3 VARCHAR(11),
Field4 VARCHAR(16),
Field5 VARCHAR(5),
Field6 VARCHAR(1),
Field7 VARCHAR(1),
Field8 VARCHAR(6),
Field9 VARCHAR(6),
Field10 VARCHAR(50),
Field11 VARCHAR(50),
Field12 VARCHAR(50)
)
INSERT INTO dbo.tblDestino
(
Field1 ,
Field2 ,
Field3 ,
Field4 ,
Field5 ,
Field6 ,
Field7 ,
Field8 ,
Field9 ,
Field10 ,
Field11 ,
Field12
)
SELECT
'123456789' , -- Field1 - varchar(10)
'123456789' , -- Field2 - varchar(12)
'123456789' , -- Field3 - varchar(11)
'123456789' , -- Field4 - varchar(16)
'123456789' , -- Field5 - varchar(5)
'123456789' , -- Field6 - varchar(1)
'123456789' , -- Field7 - varchar(1)
'123456789' , -- Field8 - varchar(6)
'123456789' , -- Field9 - varchar(6)
'123456789' , -- Field10 - varchar(50)
'123456789' , -- Field11 - varchar(50)
'123456789' -- Field12 - varchar(50)
GO
Result:
String or binary data would be truncated
*Here you get the truncation error. Then, we proceed to save the information in a global temporary table.
*IMPORTANT REMINDER: You must use the same name of the field of the destination table in the alias of each field of the SELECT statement.
Process:
IF OBJECT_ID('tempdb..##TEMP') IS NOT NULL DROP TABLE ##TEMP
go
SELECT
[Field1] = '123456789' ,
[Field2] = '123456789' ,
[Field3] = '123456789' ,
[Field4] = '123456789' ,
[Field5] = '123456789' ,
[Field6] = '123456789' ,
[Field7] = '123456789' ,
[Field8] = '123456789' ,
[Field9] = '123456789' ,
[Field10] = '123456789' ,
[Field11] = '123456789' ,
[Field12] = '123456789'
INTO ##TEMP
Result:
(1 row(s) affected)
Test:
EXEC dbo.GetFieldStringTruncate @SourceTableName = '##TEMP', @TargetTableName = 'tblDestino'
Result:
(12 row(s) affected)
ORIGEN Nombre Campo ORIGEN Maximo Largo DESTINO Nombre Campo DESTINO Tipo de campo Evaluación
-------------------------- -------------------- ------------------------ ----------------------- -------------------------
Field1 9 02 - Field1 VARCHAR(10)
Field2 9 03 - Field2 VARCHAR(12)
Field3 9 04 - Field3 VARCHAR(11)
Field4 9 05 - Field4 VARCHAR(16)
Field5 9 06 - Field5 VARCHAR(5) possible field with error
Field6 9 07 - Field6 VARCHAR(1) possible field with error
Field7 9 08 - Field7 VARCHAR(1) possible field with error
Field8 9 09 - Field8 VARCHAR(6) possible field with error
Field9 9 10 - Field9 VARCHAR(6) possible field with error
Field10 9 11 - Field10 VARCHAR(50)
Field11 9 12 - Field11 VARCHAR(50)
Field12 9 13 - Field12 VARCHAR(50)
====================================================================================================
------------------------------------------------------------------------------------------------------------
Responsible: Javier Pardo
Date: October 19/2018
WB tests: Javier Pardo
------------------------------------------------------------------------------------------------------------
*/
ALTER PROCEDURE dbo.GetFieldStringTruncate
(
@SourceTableName AS VARCHAR(255)
, @TargetTableName AS VARCHAR(255)
)
AS
BEGIN
BEGIN TRY
DECLARE @colsUnpivot AS NVARCHAR(MAX),
@colsUnpivotConverted AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
SELECT @colsUnpivot = stuff((
SELECT DISTINCT ',' + QUOTENAME(col.NAME)
FROM tempdb.sys.tables tab
INNER JOIN tempdb.sys.columns col
ON col.object_id = tab.object_id
INNER JOIN tempdb.sys.types typ
ON col.system_type_id = TYP.system_type_id
WHERE tab.NAME = @SourceTableName
FOR XML path('')
), 1, 1, '')
,@colsUnpivotConverted = stuff((
SELECT DISTINCT ',' + 'CONVERT(VARCHAR(MAX),' + QUOTENAME(col.NAME) + ') AS ' + QUOTENAME(col.NAME)
FROM tempdb.sys.tables tab
INNER JOIN tempdb.sys.columns col
ON col.object_id = tab.object_id
INNER JOIN tempdb.sys.types typ
ON col.system_type_id = TYP.system_type_id
WHERE tab.NAME = @SourceTableName
FOR XML path('')
), 1, 1, '')
--https://stackoverflow.com/questions/11158017/column-conflicts-with-the-type-of-other-columns-in-the-unpivot-list
IF OBJECT_ID('tempdb..##TablaConMaximos') IS NOT NULL DROP TABLE ##TablaConMaximos
set @query
= 'SELECT u.d AS colname, MAX(LEN(u.data)) as [maximo_largo]
INTO ##TablaConMaximos
FROM
(
SELECT ' + @colsUnpivotConverted + '
FROM ' + @SourceTableName + '
) T
UNPIVOT
(
data
for d in ('+ @colsunpivot +')
) u
GROUP BY u.d'
PRINT @query
exec sp_executesql @query;
------------------------------------------------------------------------------------------------------------
SELECT --'Nombre de campo' = RIGHT('00' + ISNULL(CONVERT(VARCHAR,col.column_id),''),2) + ' - ' + col.name + ' '
--, 'Tipo de campo' = ISNULL(CONVERT(VARCHAR,upper(typ.name)),'') + '(' + ISNULL(CONVERT(VARCHAR,col.max_length),'') + ')'
[ORIGEN Nombre Campo] = tcm.colname
, [ORIGEN Maximo Largo] = tcm.maximo_largo
, [DESTINO Nombre Campo] = DESTINO.[Nombre de campo]
, [DESTINO Tipo de campo] = DESTINO.[Tipo de campo]
, [Evaluación] = CASE WHEN DESTINO.maximo_largo < tcm.maximo_largo THEN 'possible field with error' ELSE '' END
--, *
FROM tempdb.sys.tables tab
INNER JOIN tempdb.sys.columns col
ON col.object_id = tab.object_id
INNER JOIN tempdb.sys.types typ
ON col.system_type_id = TYP.system_type_id
RIGHT JOIN
(
SELECT column_id
, [Nombre de campo] = RIGHT('00' + ISNULL(CONVERT(VARCHAR,col.column_id),''),2) + ' - ' + col.name + ' '
, [Tipo de campo] = ISNULL(CONVERT(VARCHAR,upper(typ.name)),'') + '(' + ISNULL(CONVERT(VARCHAR,col.max_length),'') + ')'
, [maximo_largo] = col.max_length
, [colname] = col.name
FROM sys.tables tab
INNER JOIN sys.columns col
ON col.object_id = tab.object_id
INNER JOIN sys.types typ
ON col.system_type_id = TYP.system_type_id
WHERE tab.NAME = @TargetTableName
) AS DESTINO
ON col.name = DESTINO.colname
INNER JOIN ##TablaConMaximos tcm
ON tcm.colname = DESTINO.colname
WHERE tab.NAME = @SourceTableName
AND typ.name LIKE '%char%'
ORDER BY col.column_id
END TRY
BEGIN CATCH
SELECT 'Internal error ocurred' AS Message
END CATCH
END
현재로서는 CHAR, VARCHAR, NCHAR 및 NVARCHAR 데이터 유형만 지원합니다.이 코드의 마지막 버전은 다음 링크에서 확인할 수 있으며, 이를 개선하기 위해 서로 도와드립니다.GetFieldStringTruncate.sql
https://gist.github.com/jotapardo/210e85338f87507742701aa9d41cc51d
데이터 타입 nchar(10)의 길이를 적어도nchar(255)로 변경합니다.
적절한 권한이 없는 경우에도 발생할 수 있습니다.
저도 비슷한 문제가 있었어요.한 테이블에서 이름을 제외한 모든 것을 동일한 테이블로 데이터를 복사하고 있었습니다.
최종적으로 SELECT INTO 문을 사용하여 소스 테이블을 임시 테이블에 덤프했습니다.
SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;
소스 테이블의 스키마를 임시 테이블과 비교했습니다.칼럼 중 하나가 '칼럼'이varchar(4000)기대했을 때varchar(250).
업데이트: 관심 있는 경우 여기서 varchar(4000) 문제를 설명할 수 있습니다.
Nvarchar(최대)의 경우 TSQL에서 4000자만 표시됩니까?
이게 도움이 됐으면 좋겠다.
이 오류는 테이블의 컬럼에 제약 조건 [대부분 길이]가 있을 때 발생합니다.예를 들어 myColumn 컬럼의 데이터베이스 스키마가 CHAR(2)인 경우 응용 프로그램에서 값을 삽입하기 위한 호출이 있을 때 길이2의 문자열을 전달해야 합니다.
이 오류는 기본적으로 3 이상의 문자열은 데이터베이스 스키마에서 지정된 길이 제한에 맞지 않습니다.SQL Server가 경고하고 데이터 손실/잘라짐 오류를 발생시키는 이유입니다.
다음 코드를 사용해 보십시오.
CREATE TABLE [dbo].[Department](
[Department_name] char(10) NULL
)
INSERT INTO [dbo].[Department]([Department_name]) VALUES ('Family Medicine')
--error will occur
ALTER TABLE [Department] ALTER COLUMN [Department_name] char(50)
INSERT INTO [dbo].[Department]([Department_name]) VALUES ('Family Medicine')
select * from [Department]
같은 얼굴을 하고 있다.
소스 테이블의 열 길이가 대상보다 깁니다.
source column length - 50그리고.destination column length - nvarchar(25)으로 늘렸습니다.Nvarchar(50)그리고 그것은 성공하였다.
Acumatica ERP에서 주문 Import 시 동일한 오류가 발생하였습니다.
문자열 또는 이진 데이터는 'MyDatabase.dbo' 테이블에서 잘립니다.ARInvoice', 열 'InvoiceNbr' 값 'Something'이 잘렸습니다.
이 링크의 문자열 또는 바이너리 잘라내기 오류를 수정하는 방법에서 설명한 절차를 수행한 후 "Quantity would be negative"라는 오류가 다시 표시되었으며, "Quantity would be negative"는 영수증 화면에서 수량을 생성하면 해결됩니다.
SQLSERVER 2019의 경우:
이 에러는 마지막 필드(패키지 5.2)와 숫자 필드에 아무것도 포함되지 않은 경우를 제외하고 varchar 데이터가 포함된 테이블에 행을 삽입할 경우 표시됩니다.
모든 BLACK 값을 0으로 채워야 합니다.
(물론 이 필드는 NULL 값을 허용합니다.)
언급URL : https://stackoverflow.com/questions/6388756/sql-server-string-or-binary-data-would-be-truncated
'codememo' 카테고리의 다른 글
| 테이블의 열 이름을 반환하려면 어떻게 해야 합니까? (0) | 2023.04.08 |
|---|---|
| 이 시스템에서 실행 중인 스크립트를 사용하지 않도록 설정되었기 때문에 ps1을 로드할 수 없습니다. (0) | 2023.04.08 |
| SQL Server에서 기존 테이블에 자동 증가 기본 키 추가 (0) | 2023.04.08 |
| '다중 부품 식별자'란 무엇이며 왜 바인딩할 수 없는가? (0) | 2023.04.08 |
| jQuery UI 대화 상자에서 닫기 버튼을 제거하는 방법 (0) | 2023.04.08 |