0

I was trying to copy a table from a view in linked server with the following script:

SELECT 
    [order_no]
    , [cust_code]
    , [order_date]
    , [order_status]
    , [reference]
INTO dbo.Sales
FROM [BIT].[bit].[BI].[sales_order]

I got the error below:

Cannot get the data of the row from the OLE DB provider for linked server "BIT". Could not convert the data value due to reasons other than sign mismatch or overflow.

The script runs well if I remove the [reference] column. I have checked the data type and it is matched.

What can I do if I really want the reference column to be in the table? I have tried to copy only the reference column and it is failed as well with the same error message.

Dale K
  • 25,246
  • 15
  • 42
  • 71
cvbn402517
  • 19
  • 2
  • _I have checked the data type and it is matched._ What does that mean? Matched what? What data type is it exactly? – AlwaysLearning Dec 09 '22 at 09:40
  • @AlwaysLearning The data type from the source showed text(20) and data type from my destination table also showed char(20). The source is from IBM informix. – cvbn402517 Dec 09 '22 at 10:58
  • How many records are in the Sales table? Could you use TOP x to try a batch at a time? Also, is there anything in the Reference field which sticks out? i.e. Weird characters – HSS Dec 09 '22 at 11:53
  • @HSS there are 6000 records in the sales table. When I try to use Top 100, it is fine, but not when it goes to top 300. What kind of character is considered as weird character? – cvbn402517 Dec 09 '22 at 13:25
  • @HSS I have tried your script of looking the weird character. It wasn't successful with the same error. However, I was able to trace the row with weird character via row by row check and it was due to the apostrophe. I have tried to use replace the apostrophe with blank and the same error returned. Is there any way that I can bring in the problematic row? – cvbn402517 Dec 10 '22 at 06:22
  • Did the script return 0 rows or error? – HSS Dec 10 '22 at 10:25
  • @HSS the script returned error – cvbn402517 Dec 10 '22 at 10:43

1 Answers1

0

See this stackoverflow post which shows how to find invalid characters in SQL fields

For your table this is what you'd use to find invalid characters in the reference field:

select [reference],
  patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,[reference]) as [Position],
  substring([reference],patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,[reference]),1) as [InvalidCharacter],
  ascii(substring([reference],patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,[reference]),1)) as [ASCIICode]
from  dbo.Sales
where patindex('%[^ !-~]%' COLLATE Latin1_General_BIN,[reference]) >0
HSS
  • 178
  • 4
  • The IBM Text data type is not a string type - it looks like it is actually a CLOB type with unicode support so it may convert to an NCHAR/NVARCHAR data type. – Jeff Dec 09 '22 at 15:32