0

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

anthony hollister
  • 1

1 Answers1

0

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

KM.
  • 101,727
  • 34
  • 178
  • 212
  • using a select into statement? – anthony hollister Dec 02 '14 at 22:54
  • I would use something like `INSERT INTO local_WorkTable (col1, col2, col3) SELECT col1, col2, col3 FROM openquery (incode, 'select col1, col2, col3 from from ctvehl')`. However, you could even try to skip the WorkTable by using `INSERT INTO local_RealTable (col1, col2, col3) SELECT col1, col2, cast(col3 as varchar(23)) FROM openquery (incode, 'select col1, col2, col3 from from ctvehl')`. Openquery passes your query to the Cobol system which doesn't seem to be able to handle the `cast`, this will make the `cast` run local on SQL Server. – KM. Dec 03 '14 at 12:26
  • ok with this code `Create table local_tempTable ( VEH_VEHICLE_CODE int, VEH_YEAR varchar(23) ) INSERT INTO local_TempTable (VEH_VEHICLE_CODE, VEH_YEAR) SELECT VEH_VEHICLE_CODE,cast(VEH_YEAR as varchar(23)) FROM openquery (incode, 'select VEH_VEHICLE_CODE, VEH_YEAR from ctvehl')` I am still seeing this error OLE DB provider "MSDASQL" for linked server "incode" returned message "[TOD][ODBC][GENESIS]VISION: Error converting field VEH_YEAR string '992D' to numeric". Msg 7330, Level 16, State 2, Line 6 Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "incode". – anthony hollister Dec 03 '14 at 16:44
  • try just selecting the data: `select * FROM openquery (incode, 'select VEH_VEHICLE_CODE, VEH_YEAR from ctvehl')` – KM. Dec 03 '14 at 20:07
  • gives the same error when it reaches that record but it looks like the veh_vehicle_code may be in numerical order... so that may help me out. – anthony hollister Dec 03 '14 at 20:38
  • this must be a problem on the Cobol side, since you can't even select the data – KM. Dec 04 '14 at 14:28