Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

invalid cursor state...

357111Jun 28 2005 — edited Jul 19 2005
Hi folks,
I have a problem:

I connect to the perfx database, and try to access the sql database through the database link to MS Sql server, I get this

SQL> select * from Actions@PEIS;
ERROR:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][unixODBC][FreeTDS][SQL Server]Invalid cursor
state (SQL State: 24000; SQL Code: 0)
ORA-02063: preceding 2 lines from PEIS
no rows selected
###################
now, here are all the details...
################

we have set up oracle 10.1.0.3 on redhat linux AS3. we have set up a database named perfx that has a database link in it named PEIS that points to a M$ sql server. we are using unixODBC version 2.2.8.3.0.2 (with Devel package) from redhad distribution.

we've compiled the freetds version 0.63 (latest stable release from freetds.org) odbc driver with these options.
./configure prefix=/usr sysconfdir=/etc --with-tdsver=8

then make; make install; make clean

we've configured hsodbc, the odbc.ini, the fretds.ini, and the odbcinst.ini.

we've been able to connect to the sql database
using isql

$ isql -v PEIS <username> <password>

SQL> select * from Actions@PEIS;

and we get 4188 rows of data.

this proves that the odbc stuff is working and the freetds driver is working. (I think)
###################
now, if from my pc, I connect to the linux oracle server using sql*plus

and I connect to the perfx database, and try to access the sql database through the link, I get this

SQL> select * from Actions@PEIS;
ERROR:
ORA-28500: connection from ORACLE to a non-Oracle system returned this message:
[Generic Connectivity Using ODBC][unixODBC][FreeTDS][SQL Server]Invalid cursor
state (SQL State: 24000; SQL Code: 0)
ORA-02063: preceding 2 lines from PEIS
no rows selected

but..
We can describe tables e.g.

SQL> describe actions@peis;
Name Null? Type
----------------------------------------- -------- --------------------
ActionID NOT NULL NUMBER(10)
ActionNumber NUMBER(10)
Assignee VARCHAR2(50)
AssigneeID VARCHAR2(50)
Subject VARCHAR2(80)
Originator VARCHAR2(50)
OriginatorID VARCHAR2(50)
DateOpened DATE
DateDue DATE
DateCompleted DATE
DateRejected DATE
DateClosed DATE
Priority VARCHAR2(40)
Description LONG
Result LONG
Category VARCHAR2(40)
ReferenceID VARCHAR2(50)
Reference VARCHAR2(40)
Reviewed NOT NULL NUMBER(10)
ActionType NUMBER(5)
Flags VARCHAR2(32512 CHAR)
Alert NOT NULL NUMBER(3)
ExternalDueDate DATE
ActionLocation VARCHAR2(40)
ActionNotes LONG
ActionSource VARCHAR2(40)
ArchiveFlag NUMBER(10)

We can select counts:

SQL> select count(*) from actions@peis;

COUNT(*)
----------
4188

We can select individual fields but only if enclosed in double quotes:

SQL> select max("DateDue") DateDue from actions@peis;

DATEDUE
---------
17-DEC-04

##########################

any thoughts on this? or maybe a pointer to some documentation that can help us with this?
Thanks



--
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 16 2005
Added on Jun 28 2005
1 comment
5,894 views