Solved

Retrieve numeric data from Oracle table

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Attaching Database Failed ? 3 39
Simple SQL query from two tables 13 51
Update a text value in another table 10 37
New to SSRS, extremely slow running report. 8 19
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 …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

785 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