SQL Server Linked to Oracle 10 g error

Question

SQL Server Linked to Oracle 10 g error

Carlos russo

SSC Rookie

Points: 25
More actions
February 21, 2012 at 10:10 am

#256198

I have succesfully linked my SQL server 2008 with an Oracle 10g server.
But when i run a simple query like:
select top 5 * from LINKEDSERVER..SCHEMA.TABLE
the result grid flashes the results and then throws the following error:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
Any idea what is going on? or how to avoid/fix this?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sengeet86 Ten Centuries Points: 1252 More actions · Answer 1

Carlos russo (2/21/2012)
I have succesfully linked my SQL server 2008 with an Oracle 10g server.
But when i run a simple query like:
select top 5 * from LINKEDSERVER..SCHEMA.TABLE
the result grid flashes the results and then throws the following error:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
Any idea what is going on? or how to avoid/fix this?

I think top 5 is not compatible with oracle that's why you are getting this error.

Try by eliminating top 5

select * from LINKEDSERVER..SCHEMA.TABLE

java programming

Ignacio A. Salom Rangel SSC-Insane Points: 20443 More actions · Answer 2

Carlos russo (2/21/2012)
I have succesfully linked my SQL server 2008 with an Oracle 10g server.
But when i run a simple query like:
select top 5 * from LINKEDSERVER..SCHEMA.TABLE
the result grid flashes the results and then throws the following error:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type "numeric".
Any idea what is going on? or how to avoid/fix this?

Can you see the tables on the linked Server? Have you tried other queries?

My blog

paul.knibbs SSCoach Points: 15320 More actions · Answer 3

Not an expert on Oracle but I found this FAQ about how to get the top N rows from a table:

http://www.orafaq.com/faq/how_does_one_select_the_top_n_rows_from_a_table

nick.mcdermaid Hall of Fame Points: 3809 More actions · Answer 4

At least start the troubleshooting process - isolate the column with the issue. Run your select using a single column until you find the onw with the issue.

Certainly I know that SSIS has problems with columns of data type NUMERIC in Oracle of they are not qualified with a size. It needs to be qualified with a size like this: NUMERIC(20, 9)

So you might need to cast it on the oralce side like this:

SELECT

CAST (YourColumn AS NUMERIC (20, 9)) YourColumn

FROM TABLE

... then you'll need to do that within OPENROWSET so that it is a pass through query and it's cleaned up on the Oracle side.

monpara.sanjay SSC Enthusiast Points: 110 More actions · Answer 5

monpara.sanjay

SSC Enthusiast

Points: 110

February 24, 2012 at 1:00 am

#1451209

Your syntax is right,

can u describe table of oracle?

monpara.sanjay SSC Enthusiast Points: 110 More actions · Answer 6

I think this error is because of number type column of oracle table.

if u give specific size in oracle then its not problematic like: number(5),number(5,2) etc...

I was getting error in

SELECT * FROM SQLORA..SCOTT.TEMP

where SQLORA is my linked server name.

TEMP is oracle table name &

TEST is number type column in oracle having data with floating points.

To resolve this problem use bellow solutions...

SELECT *

FROM OPENQUERY (SQLORA, 'SELECT TO_CHAR(TEST) AS NumberCol FROM TEMP');

SELECT CONVERT(INT, NumberCol) AS NumberCol

FROM OPENQUERY (SQLORA, 'SELECT TO_CHAR(ROUND(TEST)) AS NumberCol FROM TEMP');

SELECT CONVERT(FLOAT, NumberCol) AS NumberCol

FROM OPENQUERY (SQLORA, 'SELECT TO_CHAR(TEST) AS NumberCol FROM TEMP');

Carlos russo SSC Rookie Points: 25 More actions · Answer 7

Thanks Grasshopper i did that and it worked beautifully.

Thanks to all