Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Thu, 28 Jun 2007 11:33:16 Z

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

Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Fri, 29 Jun 2007 06:55:10 Z

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.

Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Fri, 29 Jun 2007 16:05:11 Z

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

Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Sat, 30 Jun 2007 17:49:31 Z

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

Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Tue, 03 Jul 2007 21:20:52 Z

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

Jens K. Suessmeyer.

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

Openrowset to Oracle doesnt work after upgrading to SQL Server 2005

Thu, 05 Jul 2007 20:14:42 Z

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