Bad Data in linked table

Question

I pull data to SQL Server from a cobol database that is connected as a linked server. we have ended up with bad data in one of our tables, and I am trying to track down the offending record. specifically we have a letter entered in to a year field, when SQL pulls the data over it attempts to convert that column to a numeric data type.

I believe what I need is a combination of openquery and cast to select all columns with at least that specific column as varchar, so that I can retrieve the specific offending record and have the dept. fix the error.

I have tried the following two syntax but both produces an error.

select * from [incode]...ctvehl 
 where VEH_YEAR like '992D'

select * from openquery (incode, 'select cast(* as nvarchar) from ctvehl')

for clarity

 linked server name = incode
  table name = CTVEHL
  Specific offending column = VEH_YEAR

assistance with this would be greatly appreciated.

Thanks

Answer 1

You could just initially insert the data into a work table within SQL Server that has all varchar() columns. You could then validate and parse the work table for possible errors, moving the bad rows to an "error" table for other processing/reporting. Then insert the remaining rows into your actual table.

You should look into SQL Server Integration Services, it offers ways to mass import data and handle bad rows, see: SQL Server Integration Services Dealing with Bad Data