Solved

Retrieve numeric data from Oracle table

Posted on 2003-11-05
4
379 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

738 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