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