Truncation error reporting app

• mandy-570563

  SSC Enthusiast

  Points: 193

  More actions

  August 18, 2009 at 4:37 am

  #138157

  We do a lot of data loading into SQL2005. Our consultants get a lot of truncation errors, and have asked us for a method/process/ap that will enable them to easily identify truncation errors, report the erroring columns and remove them from the import.

  So far we have got as far as loading the data into a copy of the import table (with no length restrctions) and running an except against the imported data to see where the error is, but this only supplies the erroring row not the actual column. I have been playing with the idea of using datalength and comparing this with the set column length.

  Has anyone done anything like this before, or can suggest ways of achieving this?

  Thanks

• MVDBA (Mike Vessey)

  SSC-Insane

  Points: 21797

  More actions

  August 18, 2009 at 8:29 am

  #1040922

  ok - i would suggest something like the followiing

  declare @strsql varchar(max)

  declare @temptabname varchar(100)

  set @temptabnme='mysourcetable'

  declare @destnamevarchar(100)

  set @destname='mydestinationtable'

  set @strsql='select '

  select @strsql=@strsql+' max(datalength('+name+'),'

  set @strsql=@strsql+ 'from syscolumns where object_name(id)='''+@temptabname+''''

  exec (@strsql)

  select name,length from syscolumns where object_name(id)=@destname

  if you have a way of mapping up these columns (by column name??????) then you could left outer join the 2 and compare data sizes

  don't forget NVARCHAR/NCHAR= 2 bytes VARCHAR/CHAR=1 byte

  MVDBA