Search code examples
sqlsybasesap-aseembedded-sql

Query the state of 'COMPATIBILITY_MODE' ON / OFF from another session in ASE

As of Sybase ASE 15.0.3 one can set the so called COMPATIBILITY_MODE within a session to ON or OFF with the SQL or ESQL/C command:

SQL SET COMPATIBILITY_MODE ON

This can be configured too as server wide option, but this is not meant here.

The question is, if one can query from another session, for example with SQL, the actual value of a given session.

Solution

  • Yes you can, for a given SPID, check the SUID of the process and run:

    dbcc traceon(3604)
go
dbcc pss(@suid, @spid)
go

    In the output you have for example:

    ...
poptions=7 (OPT_TRUNCABORT) 8 (OPT_ARITHABORT) 40 (OPT_PREFETCH) 
41 (OPT_TRIGGERS) 42 (OPT_REPLICATION_1) 43 (OPT_REPLICATION_2) 
48 (OPT_TRANSRPC) 58 (OPT_REMOTE_INDEXES) 62 (OPT_STMT_CACHE) 
64 (OPT_PROC_RETURN_STATUS) 65 (OPT_PROC_OUTPUT_PARAMS) 
84 (OPT_LITERAL_AUTOPARAM) 93 (OPT_COMPATIBILITY_MODE) 
107 (OPT_FDP_CURRENCY) 
...

    The flag OPT_COMPATIBILITY_MODE indicates that this session has compatiblity mode turned on.