Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Thursday, June 28, 2007 11:33 AMAraki66

0
Hi,

I just upgraded to SQL server 2005 and some of my Openrowset to Oracle doesnt work. I found out that the issue occur with field with structure Numeric.

The issue is not solve even when I try to cast the field to varchar. sometimes it succeeded and sometimes I get error 7320 & 7321.

I'm using Oracle connector from SQL to Oracle.

I've 64 bit SQL Server.

Attach the code, the Numeric field is SCORE.

select *

FROM

OPENROWSET('ORAOLEDB.ORACLE',aaaa;bbbb;cccc,

'SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE,

CAST(SCORE AS Varchar(250))

FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2')

Anyone can advice on resolution?

Thanks,

Assaf
- Reply
- Quote

All Replies

Friday, June 29, 2007 6:55 AMAnton Klimov - MSFTModerator

0
Do I understand you correctly that you used to have SQL Server 2000 and the same query worked?

It also might be useful if you could provide the full text of the messages you are getting.
- Reply
- Quote
Friday, June 29, 2007 4:05 PMJens K. Suessmeyer -MSFT, Moderator

0

Sql Server 2005 is locked down by default.

Go to Program > Microsoft SQL Server 2005 > Configuration Tools > Surface Area Configuration > Surface Area Configuration for features > AdHoc Remote Queries > Enable OPENROWSET AND OPENDATASOURCE support.

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
- Reply
- Quote
Saturday, June 30, 2007 5:49 PMAraki66

0
Hi Guys,

The openrowset is enabled. It was working in 2000 and not working in 2005

Its working for other tables without fields with Numeric.

here some errors i get

OLE DB provider "ORAOLEDB.ORACLE" for linked server "(null)" returned message "ORA-00910: specified length too long for its datatype".

Msg 7321, Level 16, State 2, Line 1

An error occurred while preparing the query "SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, DT_ISSUED, MAJOR_CODE,

CAST(SCORE AS VARCHAR(8000))

FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2" for execution against OLE DB provider "ORAOLEDB.ORACLE" for linked server "(null)".

Msg 7320, Level 16, State 2, Line 2

Cannot execute the query "SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE,

CAST(SCORE AS numeric(38,2))

FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2" against OLE DB provider "ORAOLEDB.ORACLE" for linked server "(null)".

Thanks,

Assaf
- Reply
- Quote
Tuesday, July 03, 2007 9:20 PMJens K. Suessmeyer -MSFT, Moderator

0
WHats the range of the data ? Is there any data which has a precision of 38 ?

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---
- Reply
- Quote
Thursday, July 05, 2007 8:14 PMAraki66

0
Yes,

There are Numeric(38,0)

There are Numeric (7,2)

What the relation between one data type to the import of other fields in the table?

Do I need to do cast and to what type?

Assaf
- Reply
- Quote