Solved

Retrieve numeric data from Oracle table

Posted on 2003-11-05
4
373 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

867 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now