I have a database view which returns syscolum and systable columns.
ALTER VIEW "DBA"."vBaseColumnDescriptions" as select (select user_name from SYS.SYSUSERPERM where user_id = SYS.SYSTABLE.creator) as creator, sys.SYSCOLUMN.column_name as cname, sys.SYSTABLE.table_name as tname, (select domain_name from SYS.SYSDOMAIN where domain_id = SYS.SYSCOLUMN.domain_id) as coltype, SYS.SYSCOLUMN.nulls, SYS.SYSCOLUMN.width as length, SYS.SYSCOLUMN.scale as syslength, SYS.SYSCOLUMN.pkey as in_primary_key, SYS.SYSCOLUMN.column_id as colno, SYS.SYSCOLUMN."default" AS defaut_value, sys.SYSCOLUMN.remarks as remarks from SYS.SYSCOLUMN join SYS.SYSTABLE ON sys.SYSCOLUMN.table_id = sys.SYSTABLE.table_id
Problem is when I query this view from Visual Basic 6.0 application using an rdoResultset
called rsResults like:
strColName = rsResults!cName & "" strColName = rsResults!REMARKS & ""
rsResults!REMARKS raises an error: System Error 40002:S0002: [Sybase][ODBC Driver][SQL Anywhere]Column 'column_name' not found.
I can get information on the data column such as rsResults.rdoResultset("remarks").name and others, but the "value" raises the error.
The view and above query in Visual Basic 6.0 worked with Sybase ASA 9, but not with Sybase ASA 12.
Are you sure the SQLSTATE was 40002?
Caveat Emptor: This is a WAG...
Since the view looks OK, and runs OK in SQL Anywhere 12, the error message may be coming from somewhere else in the "call stack" between your VB code and the desired result.
To investigate this, try changing this line of code in the view
sys.SYSCOLUMN.column_name as cname,
'xxx' as cname,
and run it again. You SHOULD get a completely different result, perhaps a completely different error message.
HOWEVER, if you get the same message, then there is some other reference to column_name happening.