0

I am trying to run this on SQL Server 2008 R2.

SELECT * FROM 
OPENQUERY(linked_oracle_server, 'SELECT A.Column1, A.Column2 from A')

This used to work before. We started facing the problem when the A.Column1 was altered to nvarchar2(4000) on the Oracle side.

Now it fails with the following error:

Cannot create a column accessor for OLE DB provider "OraOLEDB.Oracle" for "linked_oracle_server"

I was playing around a bit, if I do SUBSTR(A.Column1,1,2000) it works! And if I change it to SUBSTR(A.Column1,1,2001) it gives the same error as above. Does this have to do with NVARCHAR having a maximum limit of 4000 on the SQL Server side? Is there no way to import an Oracle column of size greater then NVARCHAR2(2000), even though NVARCHAR(MAX) is capable of supporting much bigger data on SQL Server?

M.Ali
  • 67,945
  • 13
  • 101
  • 127
Amith Raravi
  • 235
  • 3
  • 18
  • Please run this query: `select table_name, column_name, data_length, char_col_decl_length, char_used from all_tab_columns where table_name = 'TABLENAME' and column_name = 'COLUMN1' ;` where `TABLENAME` is the name of the table, and `COLUMN` is the name of this column, and paste results to the question. – krokodilko Dec 14 '13 at 21:04
  • That doesn't seem likely, SQL Server can definitely handle nvarchar columns longer than 2000 characters. I did find this seemingly related bug: http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9ede558a-9ac5-4c61-97ed-f8f52c7f8f44/msg-7340-level-16-state-4-line-1-cannot-create-a-column-accessor-for-ole-db-provider-ibmdasql?forum=sqldatabaseengine. The other thing you might want to try is isolating the problem. So just make a table in Oracle with a single `nvarchar2(4000)` column and try to select from that. – Milimetric Dec 14 '13 at 21:05
  • @kordinko: DATA_LENGTH 4000, CHAR_COL_DECL_LENGTH 4000, CHAR_USED C. This is what i get. – Amith Raravi Dec 14 '13 at 21:18
  • @Milimetric : A.Column1 is nvarchar2(4000). I know the problem is due to that, as i said in description if i SUBSTR the column, it works fine. – Amith Raravi Dec 14 '13 at 21:21
  • This doesn't necessarily mean the column length is the problem. It could be other things. I think it's a bug in the Oracle driver. I only have a little experience with this, but I found many different versions and the old ones usually had pretty glaring problems. – Milimetric Dec 15 '13 at 04:45

1 Answers1

4

The solution to this was to take only half the column length from the oracle side.

If the SQL side column is defined as NVARCHAR(MAX)(MAX being 4000), then the maximum length you can import from the Oracle side is half that. So doing SUBSTR(A.Column1,1,2000) on the Oracle column becomes necessary!

Amith Raravi
  • 235
  • 3
  • 18