Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Question

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

Answer 1

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.

Answer 2

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
---

Answer 3

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

Answer 4

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

Jens K. Suessmeyer.

---
http://www.sqlserver2005.de
---

Answer 5

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