Firstly, I am NO expert in oracle (BOOO!!!).
But I am trying to resolve an error a friend is getting and I am unsure where to go.
There are a few views and tables that he can do a SELECT on but one in particular, he cannot and it makes no sense.
This is the error I get:
OLE DB provider "OraOLEDB.Oracle" for linked server "ORACLE" returned message "ORA-01858: a non-numeric character was found where a numeric was expected"
Any ideas what this means? They are using Oracle 11g if this helps.
Thank you
Shot in the dark but...
If the SELECT is going through a View, it could be that the View needs to be refreshed. This can happen when the View is "select * from...." and the underlying table gets changed. There is a system sproc that can refresh the View in SQL Server. I wouldn't know the details in Oracle but, if nothing else, re-creating the View using an ALTER VIEW (with the same definition) would work.
Thanks.
I believe my friend is using the refresh approach. He is also using SSIS to import data but wants to verify that the number of records imported MATCH the number of records on the Oracle end but the problem is when he tries to do the SELECT COUNT(*) from the oracle linked table, he gets the error above.
Imports data just fine.
CAN view the SAME view in MS Access 2010 no problem
SQL just doesn't seem to be able to access/read that view.
Again: MS Access CAN see the view and view all the records in that view linked to Oracle. SQL cannot.
there are times when sql server will not be able to interpret a value if it contains commas but the other system includes them in its numeric values. Check for this.
Thank you.
I will see if it is possible for him to check but what doesn't make sense is that he can see the data in MS Access no problem, but in SQL Server - it errors as above.
The columns are all of string value in oracle I believe, but I could be wrong.
why would it matter if he is querying against the oracle DB as a linked server? Does SQL then do the data type conversions when querying?
From Oracle to Access, or Oracle to SQL you're taking a data type from one system and turning it into a data type in another. They are not always 1:1 so there is some interpretation to get it into a a SQL Server or Access data type. There is also no guarantee, even though made by the same company, that Access & SQL Server are going to interpret that oracle data type the same way and even the data types between Access & SQL Server are slightly different. Then there's also Unicode encoding to consider.
Thought as much. thank you! I appreciate it.
It's just a bit weird that MS Access works fine but not SQL Server even though the driver, I believe, is the same.