Solved

Retrieve numeric data from Oracle table

Posted on 2003-11-05
4
378 Views
Last Modified: 2006-11-17
Hi,
a simple question if you had experience this before. How to retrieve a numeric data from a table in Oracle's  from SQL?

Actually, I want to insert a data into a table in SQL db but the data I pull from Oracle table. I manage to get the varchar2 and date column type data from Oracle, but when it comes to number, it give me this error message.

"Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetNextRows returned 0x80004005:  The provider did not give any information about the error.]."

This is a sample of my simple select statement that produce the above error:

select chartid from NEWEIS..EISADMIN.tbl_chart

****note : chartid is number(4)


 
0
Comment
Question by:mantech
  • 2
4 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 70 total points
ID: 9685601
all oracle numbers are basically floats ...

what data type is sql server assuming?

have you tried converting the oracle column to a string representation and
then converting that back for SQL server

post you sql statement for further help...

0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9689273
May be try something like this using openquery.

select * from openquery( LINKED_OLAP,
'select distinct [Customer Location:Country],
[Customer Location:State Province],
[Customer Location:City]
from sales' )
0
 
LVL 15

Expert Comment

by:namasi_navaretnam
ID: 9689294
Opps. That may not be for oracle.
0
 

Author Comment

by:mantech
ID: 9691242
Fyi,
I already try to convert but it didn't help. It seems SQL is having a problem to read the number value in Oracle. BTW, here are the sample:

select chartid from NEWEIS..EISADMIN.tbl_chart

select convert(integer,chartid) from NEWEIS..EISADMIN.tbl_chart

select convert(float,chartid) from NEWEIS..EISADMIN.tbl_chart
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question