Cannot fetch a rows from oracle (linked server) using openquery in sql server

Question

I am rewriting a replication package between oracle and sql server. Currently the data is moved by ssis packages, but we move towards stored procedures because of better performance and easier maintenance.

I get the following error when I try to fetch rows from oracle using openquery in sql server.

""OLE DB provider "MSDASQL" for linked server "" returned message "[oracle_db_name][ODBC 64bit driver][OpenAccess SDK SQL Engine]Failed to retrieve data. Error ticket# kkfiquod1il1svbg21nxw[401]". OLE DB provider "MSDASQL" for linked server "" returned message "[oracle_db_name][ODBC 64bit driver]Error in row.". Msg 7330, Level 16, State 2, Line 2 Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "".""

The column that is causing the issue is an ntext column in oracle, I have tried to cast it in many ways, nothing.

Interestingly if I use LEFT(NVL(Column_Name, ''''), 4000) on the problematic column it returns some rows - the first 100 - or it returns lot more if I order my results by the id, but I can not get all.

When I located the 'last' id from the batch that did not fail and selected the next id's row the column was null just as some of the previous rows.

Is anyone ever experienced similar or have a solution for this issue?

Thanks in advance!

question from:https://stackoverflow.com/questions/65922020/cannot-fetch-a-rows-from-oracle-linked-server-using-openquery-in-sql-server

Answer 1

Waitting for answers