String or binary data would be truncated

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

String or binary data would be truncated

Author

Topic

alejo46
Posting Yak Master

157 Posts

Posted - 2015-04-29 : 21:19:39

Good evening,

in an DML command (Insert) being populated from diferent sources
an error message is yileded:
Msg 8152, Level 16, State 14, Line 5
String or binary data would be truncated

the sentence is something like that

insert into abonado_svcl
select f1,f2 from table1
--nested inner joins table2
inner joins table3
inner join tablen

I now there must be an incorrect record which character field overflowing, the problem is there are so many tables from the source, so how can i identify which tables have this datatype to trap this error ?

Could you give me some guidance ?

Thnaks for your help in advanced

Kristen
Test

22859 Posts

Posted - 2015-04-30 : 04:51:03

Good luck with that! promised by Microsoft to be fixed in "next release" back in 2005 ...

https://connect.microsoft.com/SQLServer/feedback/details/339410/please-fix-the-string-or-binary-data-would-be-truncated-message-to-give-the-column-name

... I dread to think of the number of developer hours that have been wasted fixing data due solely to the poor quality of the error message

My solution is:

Instead of

INSERT INTO abonado_svcl
SELECT f1,f2

use

SELECT f1,f2
INTO #TEMP

and then compare the MAX length of the fields in #TEMP with the actual table's columns' defined lengths.

We import all "external" data into #TEMP tables with char columns set to VARCHAR(8000) (or VARCHAR(MAX) ) and two additional columns for HasError and ErrorMessage.

We then check LENGTH of each varchar column exceeds length of the target table. If so we append a message to [ErrorMessage] and set HasError=1. We also check that String Dates are valid, associated data exists, and so on.

We can then report, to user, any row where HasError >= 1

We then import row where HasError=0 (but you could abort the whole import if restricting import to "clean" rows would cause problems elsewhere)

(We actually have some errors that are Critical and some Non-Critical, were we, for example, just truncate the over-long data so it imports without error - but we still report the message to the user so they can go fix the data in the source - or tell us to expand the target column size)

alejo46
Posting Yak Master

157 Posts

Posted - 2015-04-30 : 19:30:02

OK Thanks you very much for your support abn suggestions, i wiil work on it to see what happens

alejo46
Posting Yak Master

157 Posts

Posted - 2015-05-02 : 22:25:30

Thanks for your suggestions and i read and analyze your solution and its a good choice and a very smart solution, the problem I´ve got is due to my lack of expereience and knowledge to follow these steps because im not a DBA, maybe i could tell our DBA, but the purpose is to learn as much as possible, so i came across into different questions, but before i printed some fragments of code where it failed

(1 row(s) affected)

INSERT INTO ABONADO (
COD_CLIENTE
,COD_ABONADO
,FEC_ALTA
,FEC_ALTACEN
,NOM_USUARIO
,TIP_IDENT
,NUM_IDENT
,COD_PROFESION
,EST_CIVIL
,FEC_NACIMIENTO
,IMP_INGBRUTO
,IND_SEXO
,TIP_EMPLEO
,NUM_NPA
,NUM_CELULAR
,FEC_BAJA
,FEC_BAJACEN
,DIR_USUARIO
,TEL_USUARIO_OF
,TEL_USUARIO_EXT
,COD_VENTA
,E_MAIL
,CANAL
,COD_VENDEDOR
,COD_OFICINA
,NOM_OFICINA
,COD_CONCESIONARIO
,COD_USUARIO
,NSR_HEXAGECIMAL
,COD_MODCONTRATO
,NOM_USUARORA
,TIP_TECNOLOGIA
,ICC_GSM
,IMSI_GSM
,COD_CLIENTE_DIST
,COD_ABONADO_ELITE
,COD_CLIENTE_ELITE
)
SELECT
TAS.COD_CLIENTE
,TAS.COD_ABONADO
,TAS.FEC_ALTA
,TAS.HOR_ALTACEN
,TUS.NOMBRE
,TUS.COD_TIPIDENT
,TUS.NUM_IDENT
,TUS.COD_OCUPACION
,TUS.COD_ESTCIVIL
,TUS.FEC_NACIMIEN
,TUS.IMP_BRUTO
,CASE
TUS.IND_SEXO
WHEN
'V'
THEN
'M'
WHEN
'M'
THEN
'F'
ELSE TUS.IND_SEXO
END IND_SEXO
,TUS.IND_TIPOTRAB
,TAS.NUM_NPA
,TAS.NUM_CELULAR
,TAS.FEC_BAJA
,TAS.FEC_BAJACEN
,SUBSTRING (LTRIM (TDUS.DIRECCION), 1, 80) AS DIR_USUARIO
,TUS.TEF_CLIENTE1
,TUS.TEF_CLIENTE2
,TAS.COD_VENTA
,TUS.NOM_EMAIL
,HVS.COD_CANAL
,TAS.COD_VENDEDOR
,DO.COD_OFICINA
,DO.DES_OFICINA
,DO.COD_CONCESIONARIO
,TAS.COD_USUARIO
,TAS.NSR_HEXADECIMAL
,TAS.COD_MODCONTRATO
,TAS.NOM_USUARORA
,TAS.TIP_TECNOLOGIA
,TAS.ICC_GSM
,DGS.IMSI_GSM
,TAS.COD_CLIENTE_DIST
,CM.COD_ABONADO
,CM.COD_CLIENTE
FROM
TEMP_ABONADO TAS
LEFT JOIN TEMP_USUARIO_SCL TUS
ON TAS.COD_USUARIO = TUS.COD_USUARIO
AND NOT EXISTS
(SELECT 'X'
FROM TEMP_USUARIO_SCL TUS2
WHERE TUS.COD_USUARIO = TUS2.COD_USUARIO
AND DBO.FN_ASIGNA_VALOR (TUS.FEC_ALTA, TUS.NUM_IDENT) >
DBO.FN_ASIGNA_VALOR (TUS2.FEC_ALTA, TUS2.NUM_IDENT))
LEFT JOIN HECHOS_VENTA_SCL HVS
ON TAS.COD_VENTA = HVS.COD_VENTA
LEFT JOIN DIM_OFICINA DO
ON (TAS.COD_OFICINA = DO.COD_OFICINA
AND HVS.COD_CANAL = DO.COD_CANAL
AND DO.FUENTE = 1)
LEFT JOIN DETALLE_GSM_SIMCARD DGS
ON TAS.ICC_GSM = DGS.NUM_SIMCARD
LEFT JOIN CLIENTES_MIGRADOS CM
ON TAS.COD_ABONADO = CM.COD_ABONADO_SCL
LEFT JOIN TEMP_DIRECCION_USUARIOS_SCL TDUS
ON TAS.COD_USUARIO = TDUS.COD_USUARIO
(1 row(s) affected)
Server: Msg 8152, Level 16, State 9, Line 1
String or binary data would be truncated.
The statement has been terminated.
(1 row(s) affected)

Acording the above script identify 2 tables source tables: temp_abonado (Actually is a view), target table (Abonado)
2. identify wchich columns of both tables are varchar datatype
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'ABONADO' and DATA_TYPE = 'varchar'
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TEMP_ABONADO' and DATA_TYPE = 'varchar'

3.After identifying step 2 i tried to find out which columns from the source table are greather than maximun length from target table but i didnt get any records on none of these queries:

SELECT COD_VENDEDOR FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(COD_VENDEDOR))) > 6

SELECT COD_OFICINA FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(COD_OFICINA))) > 6

SELECT COD_CONCESIONARIO FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(COD_CONCESIONARIO))) > 6

SELECT NSR_HEXADECIMAL FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(NSR_HEXADECIMAL))) > 8

SELECT NOM_USUARORA FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(NOM_USUARORA))) > 30

SELECT TIP_TECNOLOGIA FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(TIP_TECNOLOGIA))) > 7

SELECT ICC_GSM FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(ICC_GSM))) > 25

SELECT IMSI_GSM FROM TEMP_ABONADO
WHERE LEN(LTRIM(RTRIM(IMSI_GSM))) > 50

So i got stuck over here

what else can i validate ?
To get back to your solution ive got some questions:

#TEMP
is a tenp and volatile table right ?

the insert command takes too long for about 10 hours since it must insert almost 70 millions of records. the SELECT f1,f2 INTO #TEMP is gonna take the same 10 hours or more ?

then compare the MAX length of the fields in #TEMP with the actual table's columns' defined lengths(Woul be)
select max(len(fieldA)) from #TEMP table

What do you mean when you say import all "external" data into #TEMP tables with char columns set to VARCHAR(8000) (or VARCHAR(MAX)

check LENGTH of each varchar column exceeds length of the target table (which command shoul i use ?)

Thanks for your reply in advanced

Kristen
Test

22859 Posts

Posted - 2015-05-03 : 03:17:37

Something like this:


SELECT	CONVERT(int, 0) AS ErrorCount,
	CONVERT(varchar(8000), NULL) AS ErrorMessage,
	PrimaryKey1, PrimaryKey2, ...
	COD_VENDEDOR, ...
INTO	#TEMP
FROM	...

UPDATE	U
SET	ErrorCount = ErrorCount + 1,
	ErrorMessage = COALESCE(ErrorMessage + ', ', '')
			+ 'COD_VENDEDOR LEN=' + CONVERT(varchar(20), LEN(COD_VENDEDOR))
FROM	#TEMP
WHERE 	LEN(COD_VENDEDOR) > 6

... repeat for all VARCHAR columns ...

... if there are data conversions you will do check the data is valid.
... e.g. if converting a String Column into a Date check that it will be a valid date
... or numbers, imported as Strings, only contain digits etc.

-- Display errors
SELECT	PrimaryKey1, PrimaryKey2, ...,
	ErrorCount,
	ErrorMessage
FROM	#TEMP
WHERE 	ErrorCount >= 1
ORDER BY PrimaryKey1, PrimaryKey2, ...

-- Optionally insert rows that are "clean"
INSERT INTO ABONADO
(
	COD_CLIENTE, ...
)
FROM #TEMP
WHERE ErrorCount = 0	-- Only insert "clean" rows

alejo46
Posting Yak Master

157 Posts

Posted - 2015-05-03 : 21:09:16

Thanks a lot once again for your support, just one more questions to understand a little bit more: The sentence below what exactly does ? and what you are refering exactly with the instruction PrimaryKey1, PrimaryKey2, ... ?

SELECT CONVERT(int, 0) AS ErrorCount,
CONVERT(varchar(8000), NULL) AS ErrorMessage,
PrimaryKey1, PrimaryKey2, ...
COD_VENDEDOR, ...
INTO #TEMP
FROM ...

secondly what exactly this update means ?

UPDATE U
SET ErrorCount = ErrorCount + 1,
ErrorMessage = COALESCE(ErrorMessage + ', ', '')
+ 'COD_VENDEDOR LEN=' + CONVERT(varchar(20), LEN(COD_VENDEDOR))
FROM #TEMP
WHERE LEN(COD_VENDEDOR) > 6

Kristen
Test

22859 Posts

Posted - 2015-05-04 : 03:46:53

"PrimaryKey1, PrimaryKey2" are the primary key columns for your table (presumably [ABONADO]) Any unique combination will do, the intention is that they are displayed in your exception report so that users can find the original data and correct it (or you can change your import proceedure for the incorrect column length)

"secondly what exactly this update means ?"

I would worry that if you don't understand this you should not be attempting to use it in your code.

The UPDATE statement is building a concatenated string of error messages for each row that has a data error - for example where an import column is too long.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources