sql-server - los datos de cadena o binarios se truncarían
La cadena de SQL Server o los datos binarios se truncarán (17)
Estoy involucrado en un proyecto de migración de datos. Recibo el siguiente error cuando intento insertar datos de una tabla en otra tabla (SQL Server 2005):
Msg 8152, nivel 16, estado 13, línea 1
Cadena o datos binarios podrían truncarse.
Las columnas de datos de origen coinciden con el tipo de datos y están dentro de las definiciones de longitud de las columnas de la tabla de destino, por lo que no sé qué podría estar causando este error.
Aquí hay una respuesta ligeramente diferente. Sus nombres y longitudes de columna pueden coincidir, pero quizás esté especificando las columnas en el orden incorrecto en su instrucción SELECT. Diga tableX y tableY tienen columnas con el mismo nombre, pero en diferente orden
Como ya han dicho otros, uno de los tipos de datos de sus columnas en la tabla fuente es más grande que sus columnas de destino.
Debido a que nadie lo mencionó aquí, una solución simple (similar a la solución CAST Thomas) es simplemente apagar la advertencia y permitir que se produzca el truncamiento. Por lo tanto, si recibe este error pero está seguro de que es aceptable que se trunquen los datos en su base de datos / tabla anterior (recortar a tamaño), simplemente puede hacer lo siguiente;
SET ANSI_WARNINGS OFF;
-- Your insert TSQL here.
SET ANSI_WARNINGS ON;
Como se indica arriba, siempre recuerde volver a activar las advertencias nuevamente. Espero que esto ayude.
Creé un procedimiento almacenado que analiza una tabla fuente o una consulta con varias características por columna, entre las que se encuentran la longitud mínima (min_len) y la longitud máxima (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
Guardo este procedimiento en la base de datos maestra para poder usarlo en cada base de datos de esta manera:
sp_analysetable ''table_name'', 1
// deep=1 for doing value analyses
Y la salida es:
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,
Estaba usando cadena vacía '''' en la creación de la tabla y luego recibo el error ''Msg 8152, String o datos binarios se truncarán'' en la actualización posterior. Esto estaba sucediendo debido al valor de actualización que contiene 6 caracteres y que es más grande que la definición de columna anticipada. Usé "ESPACIO" para evitar esto solo porque sabía que estaría actualizando a granel después de la creación de datos inicial, es decir, la columna no iba a permanecer vacía por mucho tiempo.
GRAN CAVEAT AQUÍ: Esta no es una solución especialmente ingeniosa, pero es útil en el caso en que usted está reuniendo un conjunto de datos, por ejemplo, para solicitudes de inteligencia únicas, donde está creando una tabla para la extracción de datos, aplicando procesamiento / interpretación masiva y almacenando resultados antes y después para una comparación / extracción posterior. Esto es una ocurrencia frecuente en mi línea de trabajo.
Puede poblar inicialmente con la palabra clave SPACE, es decir,
select
Table1.[column1]
,Table1.[column2]
,SPACE(10) as column_name
into table_you_are_creating
from Table1
where ...
Las actualizaciones posteriores a "column_name" de 10 caracteres o menos (sustituir según corresponda) se permitirán luego sin causar un error truncado. Nuevamente, solo usaría esto en escenarios similares a los descritos en mi advertencia.
Este error se produce cuando la columna de una tabla pone restricción [principalmente longitud]. . Por ejemplo, si el esquema de la base de datos para la columna myColumn es CHAR (2), cuando se realice una llamada desde cualquiera de las aplicaciones para insertar el valor, debe pasar String de longitud dos.
El error básicamente lo dice; una cadena de longitud tres o superior es inconsistente para ajustarse a la restricción de longitud especificada por el esquema de la base de datos. Es por eso que SQL Server advierte y arroja pérdida de datos / error de truncamiento.
Esto puede ser un error desafiante. Aquí hay algunas notas tomadas de https://connect.microsoft.com/SQLServer/feedback/details/339410/ busque el comentario de AmirCharania.
Ajusté la respuesta dada por AmirCharania para los datos seleccionados en una tabla real, en lugar de uno temporal. Primero, seleccione su conjunto de datos en una tabla de desarrollo y luego ejecute lo siguiente:
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)
Me encontré con este problema hoy, y en mi búsqueda de una respuesta a este mensaje de error informativo mínimo también encontré este enlace:
Por lo tanto, parece que Microsoft no tiene planes de ampliar el mensaje de error en el corto plazo.
Así que recurrí a otros medios.
Copié los errores para sobresalir:
(1 fila (s) afectadas)
(1 fila (s) afectadas)
(1 fila (s) afectadas) Msg 8152, nivel 16, estado 14, línea 13 Los datos binarios o de cadena se truncarán. La instrucción se ha terminado.
(1 fila (s) afectadas)
conté el número de filas en excel, llegué cerca del contador de registros que causó el problema ... ajusté mi código de exportación para imprimir el SQL cerca de él ... luego ejecuté las inserciones de 5 - 10 sql alrededor del problema sql y se las arregló para identificar el problema uno, ver la cadena que era demasiado larga, aumentar el tamaño de esa columna y luego el gran archivo de importación no corrió ningún problema.
Un truco y una solución alternativa, pero cuando te fuiste con muy pocas opciones, haces lo que puedes.
Otra posible razón para esto es si tiene una configuración de valor predeterminado para una columna que excede la longitud de la columna. Parece que alguien tocó una columna con una longitud de 5, pero el valor predeterminado excedió la longitud de 5. Esto me volvió loco ya que estaba tratando de entender por qué no funcionaba en ninguna inserción, incluso si todo lo que estaba insertando era una sola columna con un número entero de 1. Debido a que el valor predeterminado en el esquema de la tabla violaba el valor predeterminado, lo estropeó todo, lo que supongo nos lleva a la lección aprendida, evite tener tablas con valores predeterminados en el esquema. :)
Para los demás, también verifique su procedimiento almacenado . En mi caso, en mi procedimiento almacenado CustomSearch , accidentalmente declare que no hay suficiente longitud para mi columna, así que cuando ingresé un gran dato, recibí ese error a pesar de que tengo una gran longitud en mi base de datos. Acabo de cambiar la longitud de mi columna en mi búsqueda personalizada, el error desaparece. Esto es solo para el recordatorio. Gracias.
Por favor intente con el siguiente código:
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]
Sí, "una pinta en una olla de media pinta no irá". No he tenido mucha suerte (por la razón que sea) con los diversos SP que la gente haya sugerido, PERO, siempre que las dos tablas estén en el mismo DB (o puede obtenerlas en el mismo DB), puede usar INFORMATION_SCHEMA. COLUMNAS para ubicar el campo (s) errante, así:
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
Esto le permitirá desplazarse hacia arriba y hacia abajo, comparando las longitudes de campo sobre la marcha. Las secciones comentadas te permiten ver (una vez descomentadas, obviamente) si hay desajustes en los tipos de datos, o mostrar específicamente aquellos que difieren en la longitud del campo, porque soy demasiado perezoso para desplazarte, solo ten en cuenta que todo se basa en la fuente nombres de columnas que coinciden con los del objetivo.
Sí, también me enfrento a este tipo de problema.
REMARKS VARCHAR(500)
to
REMARKS VARCHAR(1000)
Aquí, he modificado la longitud de las OBSERVACIONES archivadas de 500 a 1000
Tendrá que publicar las definiciones de tabla para las tablas de origen y de destino para que descubramos dónde está el problema, pero la conclusión es que una de sus columnas en la tabla fuente es más grande que las columnas de destino . Podría ser que estés cambiando los formatos de una forma que no conocías. El modelo de la base de datos desde el que se está mudando también es importante para descifrarlo.
Tuve un problema similar. Estaba copiando datos de una tabla a una tabla idéntica en todo menos en el nombre.
Eventualmente, dejé la tabla fuente en una tabla temporal usando una instrucción SELECT INTO.
SELECT *
INTO TEMP_TABLE
FROM SOURCE_TABLE;
Comparé el esquema de la tabla fuente con la tabla temporal. Encontré que una de las columnas era varchar(4000) cuando esperaba un varchar(250) .
ACTUALIZACIÓN: El problema varchar (4000) se puede explicar aquí en caso de que esté interesado:
Para Nvarchar (Max) solo estoy obteniendo 4000 caracteres en TSQL?
Espero que esto ayude.
Voy a agregar otra causa posible de este error solo porque nadie lo mencionó y podría ayudar a una persona en el futuro (ya que el OP ha encontrado su respuesta). Si la tabla en la que está insertando tiene desencadenantes, podría ser que el desencadenante genere el error. He visto esto suceder cuando se cambiaron las definiciones del campo de tabla, pero no las tablas de auditoría.
esto también puede suceder cuando no tienes los permisos adecuados
El problema es bastante simple: una o más de las columnas en la consulta fuente contiene datos que exceden la longitud de su columna de destino. Una solución simple sería tomar su consulta de origen y ejecutar Max(Len( source col )) en cada columna. Es decir,
Select Max(Len(TextCol1))
, Max(Len(TextCol2))
, Max(Len(TextCol3))
, ...
From ...
Luego compare esas longitudes con las longitudes del tipo de datos en su tabla de destino. Al menos uno, excede su longitud de columna de destino.
Si está absolutamente seguro de que este no debería ser el caso y no le importa si no es así , entonces otra solución es convertir forzosamente las columnas de consulta de origen a su longitud de destino (lo que truncará cualquier dato que sea demasiado largo):
Select Cast(TextCol1 As varchar(...))
, Cast(TextCol2 As varchar(...))
, Cast(TextCol3 As varchar(...))
, ...
From ...