|
 |
 |
 |
|
Microsoft Knowledge Base Article
This article contents is Microsoft Copyrighted material.
©2005-©2007 Microsoft Corporation. All rights reserved. Terms
of Use |
Trademarks
Article ID: 221552 - Last Review: December 5, 2003 - Revision: 3.1 PRB: SQL Distributed Query with Oracle NUMERIC Column Gives ErrorThis article was previously published under Q221552
Attempting to use an Oracle linked server using the MSDAORA OLE DB provider,
which queries a table with a NUMERIC column, may give an error similar to the following:
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column 'columnname'. The data type is not supported.
The query may fail also if the Numeric column is not in the select list.
The column with Numeric datatype has no Length specified (no Precision, no Default, allows NULL). The number datatype without a precision and scale is represented in Oracle by a variable-length numeric with precision of up to 255. There is no SQL Server type that this can be mapped to without loss of precision.
Here are four methods to try to work around the problem: - Set the Length property for NUMERIC column in Oracle table.
- If you are not retrieving the NUMERIC column, one workaround is to use OPENQUERY instead of a four-part name in your distributed query.
- Use the TO_CHAR function to convert the data in the numeric field to a character value (note that you may have to convert it back for applications that require math operations). For example:
SELECT * FROM OPENQUERY(mylinkedserver, 'select TO_CHAR(F1) from table1')
- The best workaround, if possible, is to change the field type in the table to a defined value, such as NUMERIC(15), because many non-Oracle applications have trouble with this data type. However, many servers have corporate restrictions on what can be changed and who can change them. If you can't change the data type on the server, then use one of the other options above.
Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example,
sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider', 'data_source','location', 'provider_string', 'catalog'
SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')
Steps to Reproduce the Behavior- Create a table on Oracle server:
Create table NumTable ( f1 NUMBER )
insert into NumTable values ( 2.3 );
- set up a linked server to Oracle server:
EXEC sp_addlinkedserver 'OracleServer', 'Oracle', 'MSDAORA', 'myOracleServer'
go
EXEC sp_addlinkedsrvlogin 'OracleServer', 'FALSE', NULL, 'demo', 'demo'
go
- Specify a four-part name query:
SELECT * FROM OracleServer..DEMO.NUMTABLE
Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDAORA' supplied invalid metadata for column 'F1'.
The data type is not supported. - Add another column to the table:
Alter table numtable add f2 integer;
Insert into numtable values ( 2.1, 23 );
- Specify a distributed query using OpenQuery:
SELECT f2 FROM OPENQUERY(OracleServer, 'SELECT f2 FROM demo.NumTable')
For additional information, please see the following
article in the Microsoft Knowledge Base:
220915Â
(http://kbalertz.com/Feedback.aspx?kbNumber=220915/EN-US/
)
PRB: SQL Distributed Query with Oracle Needs Oracle Client
APPLIES TO- Microsoft SQL Server 7.0 Standard Edition
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
- Microsoft OLE DB Provider for Oracle Server 1.0
| kbdatabase kboracle kbprb kbprovider KB221552 |
Community Feedback System
Very often, it takes hours to solve a problem. Very often, you've looked high
and low, and have tried a lot of solutions. When you finally found it, chances
are, it was because someone else helped you. Here's your chance to give back.
Use our community feedback tool to let others know what worked for you and what
didn't.
Please also understand that the community feedback system is not warranted to be
correct, it's simply a system that we've built to let people try and help each
other. If something in a feedback response doesn't make sense to you, or you're
not comfortable making changes that the feedback talks about (like registry
edits), please consult a professional.
Thank you for using kbAlertz.com Feedback System.
-- Scott Cate
|
|
 |
|
 |
|
|
|
| |