Connection properties

The following table lists properties available for each connection to a SQL Anywhere database.

You can use the CONNECTION_PROPERTY system function to retrieve the value for an individual property, or you can use the sa_conn_properties system procedure to retrieve the values of all connection properties. Property names are case insensitive.

Examples

To retrieve the value of a connection property

Use the CONNECTION_PROPERTY system function. The following statement returns the number of pages that have been read from file by the current connection.
SELECT CONNECTION_PROPERTY ( 'DiskRead' );

To retrieve the values of all connection properties

Use the sa_conn_properties system procedure:
CALL sa_conn_properties( );
A separate row appears for each connection.

Descriptions

Property	Description
allow_nulls_by_default	Returns a value indicating whether columns created without specifying either NULL or NOT NULL are allowed to contain NULL values. See allow_nulls_by_default option [compatibility].
allow_read_client_file	Returns a value indicating whether the database server allows the reading of files on a client computer. See allow_read_client_file option [database].
allow_snapshot_isolation	Returns a value indicating whether snapshot isolation is enabled or disabled. See allow_snapshot_isolation option [database].
allow_write_client_file	Returns a value indicating whether the database server allows the writing of files to a client computer. See allow_write_client_file option [database].
ansi_blanks	Returns a value indicating when character data is truncated at the client side. See ansi_blanks option [compatibility].
ansi_close_cursors_on_rollback	Returns a value indicating whether cursors opened WITH HOLD are closed when a ROLLBACK is performed. See ansi_close_cursors_on_rollback option [compatibility].
ansi_permissions	Returns a value indicating whether permissions are checked for DELETE and UPDATE statements. See ansi_permissions option [compatibility].
ansi_substring	Returns a value indicating how the SUBSTRING (SUBSTR) function behaves when negative values are provided for the start or length parameters. See ansi_substring option [compatibility].
ansi_update_constraints	Returns a value indicating the range of updates that are permitted. See ansi_update_constraints option [compatibility].
ansinull	Returns a value that indicates how NULL values are interpreted. See ansinull option [compatibility].
AppInfo	Returns information about the client that made the connection. For HTTP connections, this includes information about the browser. For connections using older versions of jConnect or Open Client, the information may be incomplete. The API value can be DBLIB, ODBC, OLEDB, ADO.NET, iAnywhereJDBC, PHP, PerlDBD, or DBEXPRESS. For more information about the values returned for other types of connections, see AppInfo connection parameter [APP].
ApproximateCPUTime	Returns an estimate of the amount of CPU time accumulated by a given connection, in seconds. The value returned may differ from the actual value by as much as 50%, although typical variations are in the 5-10% range. On multi-processor computers, each CPU (or hyperthread or core) accumulates time, so the sum of accumulated times for all connections may be greater than the elapsed time. This property is supported on Windows and Linux.
auditing	Returns On if the PUBLIC.auditing option is set to On. Otherwise, returns Off. If the auditing option is set to On, and the conn_auditing option is set to Off, the auditing connection property still returns On, even though the current connection is not being audited. See Controlling auditing and auditing option [database].
auditing_options	This property is reserved for system use. Do not change the setting of this option.
Authenticated	Returns Yes if the application has sent a valid connection authentication string. Returns No if the application has not sent a valid connection authentication string.
AuthType	Returns the type of authentication used when connecting. The value returned is one of Standard, Integrated, Kerberos, or an empty string. An empty string is returned for internal connections and connections for HTTP services that use AUTHORIZATION OFF.
background_priority	Deprecated. Returns a value indicating how much impact the current connection has on the performance of other connections. See background_priority option [database] [deprecated].
BlockedOn	Returns zero if the current connection isn't blocked, or if it is blocked, the connection number on which the connection is blocked because of a locking conflict.
blocking	Returns a value indicating the database server's behavior in response to locking conflicts. See blocking option [database].
blocking_timeout	Returns the length of time, in milliseconds, a transaction waits to obtain a lock. See blocking_timeout option [database].
BytesReceived	Returns the number of bytes received during client/server communications. This value is updated for HTTP and HTTPS connections.
BytesReceivedUncomp	Returns the number of bytes that would have been received during client/server communications if compression was disabled. This value is the same as the value for BytesReceived if compression is disabled.
BytesSent	Returns the number of bytes sent during client/server communications. This value is updated for HTTP and HTTPS connections.
BytesSentUncomp	Returns the number of bytes that would have been sent during client/server communications if compression was disabled. This value is the same as the value for BytesSent if compression is disabled.
CacheHits	Returns the number of successful reads of the cache.
CacheRead	Returns the number of database pages that have been looked up in the cache.
CacheReadIndInt	Returns the number of index internal-node pages that have been read from the cache.
CacheReadIndLeaf	Returns the number of index leaf pages that have been read from the cache.
CacheReadTable	Returns the number of table pages that have been read from the cache.
CacheReadWorkTable	Returns the number of cache work table reads .
CarverHeapPages	Returns the number of heap pages used for short-term purposes such as query optimization.
chained	Returns the transaction mode used in the absence of a BEGIN TRANSACTION statement. See chained option [compatibility].
CharSet	Returns the CHAR character set used by the connection.
checkpoint_time	Returns the maximum time, in minutes, that the database server runs without doing a checkpoint. See checkpoint_time option [database].
cis_option	Returns 0 if debugging information for remote data access appears in the database server messages window and 7 if the debugging information for remote data access does not appear in the database server messages window. See cis_option option [database].
cis_rowset_size	Returns the number of rows that are returned from remote servers for each fetch. See cis_rowset_size option [database].
ClientLibrary	Returns jConnect for jConnect connections; CT_Library for Open Client connections; None for HTTP connections, and CmdSeq for ODBC, embedded SQL, OLE DB, ADO.NET, and iAnywhere JDBC driver connections.
ClientNodeAddress	Returns the node for the client in a client/server connection. When the client and server are both on the same computer, an empty string is returned. This is a synonym for the NodeAddress property.
ClientPort	Returns the client's TCP/IP port number or 0 if the connection isn't a TCP/IP connection.
ClientStmtCacheHits	Returns the number of prepares that were not required for this connection because of the client statement cache. This is the number of additional prepares that would be required if client statement caching was disabled. See max_client_statements_cached option [database].
ClientStmtCacheMisses	Returns the number of statements in the client statement cache for this connection that were prepared again. This is the number of times a cached statement was considered for reuse, but could not be reused because of a schema change, a database option setting, or a DROP VARIABLE statement. See max_client_statements_cached option [database].
close_on_endtrans	Returns On or Off to indicate whether cursors are closed at the end of a transaction. See close_on_endtrans option [compatibility].
collect_statistics_on_dml_updates	Returns On or Off to indicate whether statistics are gathered during the execution of data-altering DML statements such as INSERT, DELETE, and UPDATE. See collect_statistics_on_dml_updates option [database].
Commit	Returns the number of Commit requests that have been handled.
CommLink	Returns the communication link for the connection. This is one of the network protocols supported by SQL Anywhere, or local for a same-computer connection.
CommNetworkLink	Returns the communication link for the connection. This is one of the network protocols supported by SQL Anywhere. Values include SharedMemory and TCPIP. The CommNetworkLink property always returns the name of the link, regardless of whether it is same-computer or not.
CommProtocol	Returns TDS for Open Client and jConnect connections, HTTP for HTTP connections, and CmdSeq for ODBC, embedded SQL, OLE DB, ADO.NET, and iAnywhere JDBC driver connections.
Compression	Returns On or Off to indicate whether communication compression is enabled on the connection.
conn_auditing	Returns On if auditing is enabled for the connection, even if the auditing option is set to Off. See Controlling auditing.
connection_authentication	Returns the string used to authenticate the client. Authentication is required before the database can be modified. See connection_authentication option [database].
continue_after_raiserror	Returns On or Off to indicate whether execution of a procedure or trigger is stopped whenever the RAISERROR statement is encountered. See continue_after_raiserror option [compatibility].
conversion_error	Returns On or Off to indicate data type conversion failures are reported when fetching information from the database. See conversion_error option [compatibility].
cooperative_commit_timeout	Returns the time, in milliseconds, that the database server waits for other connections to fill a page of the log before writing to disk. See cooperative_commit_timeout option [database].
cooperative_commits	Returns On or Off to indicate when commits are written to disk. See cooperative_commits option [database].
CurrentLineNumber	Returns the current line number of the procedure or compound statement a connection is executing. The procedure can be identified using the CurrentProcedure property. If the line is part of a compound statement from the client, an empty string is returned.
CurrentProcedure	Returns the name of the procedure that a connection is currently executing. If the connection is executing nested procedure calls, the name is the name of the current procedure. If there is no procedure executing, an empty string is returned.
Cursor	Returns the number of declared cursors that are currently being maintained by the server.
CursorOpen	Returns the number of open cursors that are currently being maintained by the server.
database_authentication	Returns the string used to authenticate the database. Authentication is required for authenticated database servers before the database can be modified. See database_authentication [database].
date_format	Returns a string indicating the format for dates retrieved from the database. See date_format option [database].
date_order	Returns a string indicating how dates are formatted. See date_order option [database].
DBNumber	Returns the ID number of the database.
debug_messages	Returns On or Off to indicate whether MESSAGE statements that include a DEBUG ONLY clause are executed. See debug_messages option [database].
dedicated_task	Returns On or Off to indicate whether a request handling task is dedicated exclusively to handling requests for the connection. See dedicated_task option [database].
default_dbspace	Returns the name of the default dbspace, or an empty string if the default dbspace has not been specified. See default_dbspace option [database].
default_timestamp_increment	Returns a value, in microseconds, that is added to a column of type TIMESTAMP to keep values in the column unique. See default_timestamp_increment option [database] [MobiLink client].
delayed_commit_timeout	Returns the time, in milliseconds, that the database server waits to return control to an application following a COMMIT. See delayed_commit_timeout option [database].
delayed_commits	Returns On or Off to indicate when the database server returns control to an application following a COMMIT. See delayed_commits option [database].
DiskRead	Returns the number of pages that have been read from disk.
DiskReadHint	Returns the number of disk read hints.
DiskReadHintPages	Returns the number of disk read hint pages.
DiskReadIndInt	Returns the number of index internal-node pages that have been read from disk.
DiskReadIndLeaf	Returns the number of index leaf pages that have been read from disk.
DiskReadTable	Returns the number of table pages that have been read from disk.
DiskReadWorkTable	Returns the number of disk work table reads.
DiskSyncRead	Returns the number of disk reads issued synchronously.
DiskSyncWrite	Returns the number of writes issued synchronously.
DiskWaitRead	Returns the number of times the database server waited for an asynchronous read.
DiskWaitWrite	Returns the number of times the database server waited for an asynchronous write.
DiskWrite	Returns the number of modified pages that have been written to disk.
DiskWriteHint	Returns the number of disk write hints.
DiskWriteHintPages	Returns the number of disk write hint pages.
Encryption	Returns a value that indicates whether the connection is encrypted. See Encryption connection parameter [ENC].
escape_character	This property is reserved for system use. Do not change the setting of this option.
EventName	Returns the name of the associated event if the connection is running an event handler. Otherwise, the result is NULL.
exclude_operators	This property is reserved for system use. Do not change the setting of this option.
ExprCacheAbandons	Returns the number of times that the expression cache was abandoned because the hit rate was too low.
ExprCacheDropsToReadOnly	Returns the number of times that the expression cache dropped to read-only status because the hit rate was low.
ExprCacheEvicts	Returns the number of evictions from the expression cache.
ExprCacheHits	Returns the number of hits in the expression cache.
ExprCacheInserts	Returns the number of values inserted into the expression cache.
ExprCacheLookups	Returns the number of lookups done in the expression cache.
ExprCacheResumesOfReadWrite	Returns the number of times that the expression cache resumed read-write status because the hit rate increased.
ExprCacheStarts	Returns the number of times that the expression cache was started.
extended_join_syntax	Returns On if queries with duplicate correlation name syntax for multi-table joins are allowed, Off if they are reported as an error. See extended_join_syntax option [database].
fire_triggers	Returns On if triggers are fired in the database, otherwise, returns Off. See fire_triggers option [compatibility].
first_day_of_week	Returns the number that is used for the first day of the week, where 7=Sunday and 1=Monday. See first_day_of_week option [database].
for_xml_null_treatment	Returns Omit if elements and attributes that contain NULL values are omitted from the result and Empty if empty elements or attributes are generated for NULL values when the FOR XML clause is used in a query. See for_xml_null_treatment option [database].
force_view_creation	This property is reserved for system use. Do not change the setting of this option.
FullCompare	Returns the number of comparisons that have been performed beyond the hash value in an index.
GetData	Returns the number of GETDATA requests.
global_database_id	Returns the starting value used for columns created with DEFAULT GLOBAL AUTOINCREMENT. See global_database_id option [database].
HashForcedPartitions	Returns the number of times that a hash operator was forced to partition because of competition for memory.
HashRowsFiltered	Returns the number of probe rows rejected by bit-vector filters.
HashRowsPartitioned	Returns the number of rows written to hash work tables.
HashWorkTables	Returns the number of work tables created for hash-based operations.
HeapsCarver	Returns the number of heaps used for short-term purposes such as query optimization..
HeapsLocked	Returns number of relocatable heaps currently locked in the cache.
HeapsQuery	Returns the number of heaps used for query processing (hash and sort operations).
HeapsRelocatable	Returns the number of relocatable heaps.
http_session_timeout	Returns the current HTTP session timeout, in minutes. See http_session_timeout option [database].
HttpServiceName	Returns the service name origin for a web application. The property is useful for error reporting and control flow. An empty string is returned when this property is selected from a stored procedure that did not originate from an HTTP request or if the connection is currently inactive waiting to continue an HTTP session.
IdleTimeout	Returns the idle timeout value of the connection. See Idle connection parameter.
IndAdd	Returns the number of entries that have been added to indexes.
IndLookup	Returns the number of entries that have been looked up in indexes.
integrated_server_name	Returns the name of the Domain Controller server used for looking up Windows user group membership for integrated logins. See integrated_server_name option [database].
IsDebugger	Returns Yes or No to distinguish connections that are being used to run the SQL Anywhere debugger. The value is Yes if the current connection number corresponds to the connection number of a debugger connection, and No otherwise. See Debugging procedures, functions, triggers, and events.
isolation_level	Returns the isolation level of the connection (0, 1, 2, 3, snapshot, statement-snapshot, or readonly-statement-snapshot). See isolation_level option [database] [compatibility].
java_location	Returns the path of the Java VM for the database if one has been specified. See java_location option [database].
java_main_userid	Returns the name of the database user whose connection can be used for installing classes and other Java-related administrative tasks. See java_main_userid option [database].
java_vm_options	Returns the command line options that the database server uses when it launches the Java VM. See java_vm_options option [database].
Language	Returns the locale language.
LastIdle	Returns the number of ticks between requests.
LastPlanText	Returns the long text plan of the last query executed on the connection. You control the remembering of the last plan by setting using the RememberLastPlan option of the sa_server_option system procedure, or using the -zp server option. See -zp server option.
LastReqTime	Returns the time at which the last request for the specified connection started.
LastStatement	Returns the most recently prepared SQL statement for the current connection. See -zl server option. The LastStatement value is set when a statement is prepared, and is cleared when a statement is dropped. Only one statement string is remembered for each connection. If sa_conn_activity reports a non-empty value for a connection, it is most likely the statement that the connection is currently executing. If the statement had completed, it would likely have been dropped and the property value would have been cleared. If an application prepares multiple statements and retains their statement handles, the LastStatement value does not reflect what a connection is currently doing. When client statement caching is enabled, and a cached statement is reused, this property returns an empty string.
LivenessTimeout	Returns the liveness timeout period for the current connection. See LivenessTimeout connection parameter [LTO].
lock_rejected_rows	This property is reserved for system use. Do not change the setting of this option.
LockCount	Returns the number of locks held by the connection.
LockIndexID	Returns the identifier of the locked index.
LockName	Returns a 64-bit unsigned integer value representing the lock for which a connection is waiting.
LockRowID	Returns the identifier of the locked row.
LockTableOID	Returns zero if the connection isn't blocked, or if the connection is on a different database than the connection calling CONNECTION _PROPERTY. Otherwise, this is the object ID of the table for the lock on which this connection is waiting. The object ID can be used to look up table information using the SYSTAB system view. See SYSTAB system view.
log_deadlocks	Returns On if deadlock information is reported; otherwise, returns Off. See log_deadlocks option [database].
LogFreeCommit	Returns the number of redo free commits. A redo free commit occurs when a commit of the transaction log is requested but the log has already been written (so the commit was done for free.)
login_mode	Returns one or more of Standard, Integrated, or Kerberos to indicate whether integrated logins and Kerberos are supported. See login_mode option [database].
login_procedure	Returns the name of the stored procedure used to set compatibility options at startup. See login_procedure option [database].
LoginTime	Returns the date and time the connection was established.
LogWrite	Returns the number of pages that have been written to the transaction log.
materialized_view_optimization	Returns a value indicating whether materialized views are used during query optimization: Disabled Fresh Stale N Minute[s] N Hour[s] N Day[s] N Week[s] N Month[s] See materialized_view_optimization option [database].
max_client_statements_cached	Returns the number of statements cached by the client. See max_client_statements_cached option [database].
max_cursor_count	Returns a value specifying the maximum number of cursors that a connection can use at once. See max_cursor_count option [database].
max_hash_size	This property is deprecated.
max_plans_cached	Returns a value specifying the maximum number of execution plans to be stored in a cache. See max_plans_cached option [database].
max_priority	Returns a value indicating the maximum priority level a connection can have. See max_priority option [database].
max_query_tasks	Returns the maximum number of requests that the database server can use to process a query. See max_query_tasks option [database].
max_recursive_iterations	Returns a value specifying the maximum number of iterations a recursive common table expression can make. See max_recursive_iterations option [database].
max_statement_count	Returns a value specifying the maximum number of prepared statements that a connection can use simultaneously. See max_statement_count option [database].
max_temp_space	Returns a value indicating the maximum amount of temporary file space available for a connection. See max_temp_space option [database].
MessageReceived	Returns the string that was generated by the MESSAGE statement that caused the WAITFOR statement to be interrupted. Otherwise, an empty string is returned.
min_password_length	Returns the minimum length for new passwords in the database. See min_password_length option [database].
Name	Returns the name of the current connection.
NcharCharSet	Returns the NCHAR character set used by the connection.
nearest_century	Returns a value that indicates how two-digit years are interpreted in string-to-date conversions. See nearest_century option [compatibility].
NodeAddress	Returns the node for the client in a client/server connection. When the client and server are both on the same computer, an empty string is returned.
non_keywords	Returns a list of keywords, if any, that are turned off so they can be used as identifiers. See non_keywords option [compatibility].
Number	Returns the ID number of the connection.
odbc_describe_binary_as_varbinary	Returns Off if the SQL Anywhere ODBC driver describes both BINARY and VARBINARY columns as SQL_BINARY and returns On if the ODBC driver describes BINARY and VARBINARY columns as SQL_VARBINARY. See odbc_describe_binary_as_varbinary [database].
odbc_distinguish_char_and_varchar	Returns Off if CHAR columns are described as SQL_VARCHAR, and On if CHAR columns are described as SQL_CHAR. See odbc_distinguish_char_and_varchar option [database].
oem_string	Returns the string stored in the header page of the database file. See oem_string option [database].
on_charset_conversion_failure	Returns one of Ignore, Warning, or Error to indicate the behavior when an error is encountered during character set conversion. See on_charset_conversion_failure option [database].
on_tsql_error	Returns one of Stop, Conditional, or Continue to indicate the behavior when an error is encountered while executing a stored procedure. See on_tsql_error option [compatibility].
optimization_goal	Returns one of First-row or All-rows to indicate how query processing is optimized. See optimization_goal option [database].
optimization_level	Returns a value between 0 and 15. This number is used to control the amount of effort made by the SQL Anywhere query optimizer to find an access plan for a SQL statement. See optimization_level option [database].
optimization_workload	Returns a value indicating the amount of effort made by the SQL Anywhere query optimizer to find an access plan for a SQL statement. See optimization_workload option [database].
OSUser	Returns the operating system user name associated with the client process. If the client process is impersonating another user (or the set ID bit is set on Unix), the impersonated user name is returned. An empty string is returned for version 10.0.1 and earlier clients, and for HTTP and TDS clients.
PacketSize	Returns the packet size used by the connection, in bytes.
PacketsReceived	Returns the number of client/server communication packets received. This value is not updated for HTTP or HTTPS connections.
PacketsReceivedUncomp	Returns the number of packets that would have been received during client/server communications if compression was disabled. (This value is the same as the value for PacketsReceived if compression is disabled.)
PacketsSent	Returns the number of client/server communication packets sent. This value is not updated for HTTP or HTTPS connections.
PacketsSentUncomp	Returns the number of packets that would have been sent during client/server communications if compression was disabled. (This value is the same as the value for PacketsSent if compression is disabled.)
pinned_cursor_percent_of_cache	Returns the percentage of the cache that can be used for pinning cursors. See pinned_cursor_percent_of_cache option [database].
post_login_procedure	Returns the name of the procedure whose result set contains messages that should be displayed by applications when a user connects. See post_login_procedure option [database].
precision	Returns the decimal and numeric precision setting. See precision option [database].
prefetch	Returns Off if no prefetching is done, Conditional if prefetching occurs unless the cursor type is SENSITIVE or the query includes a proxy table, or Always if prefetching is done even for SENSITIVE cursor types and cursors that involve a proxy table. See prefetch option [database].
Prepares	Returns the number of statement preparations performed for the connection.
PrepStmt	Returns the number of prepared statements currently being maintained by the server.
preserve_source_format	Returns On if the original source definition of procedures, triggers, views, and event handlers is saved in system tables, otherwise, returns Off. See preserve_source_format option [database].
prevent_article_pkey_update	Returns On if updates are not allowed to the primary key columns of tables involved in publications, otherwise returns Off. See prevent_article_pkey_update option [database] [MobiLink client].
priority	Returns a value indicating the priority level of a connection. See priority option [database].
query_mem_timeout	Returns the value of the query_mem_timeout option. See query_mem_timeout option [database].
QueryBypassed	Returns the number of requests optimized by the optimizer bypass.
QueryBypassedCosted	Returns the number of requests processed by the optimizer bypass using costing.
QueryBypassedHeuristic	Returns the number of requests processed by the optimizer bypass using heuristics.
QueryBypassedOptimized	Returns the number of requests initially processed by the optimizer bypass and subsequently fully optimized by the SQL Anywhere optimizer.
QueryCachedPlans	Returns the number of query execution plans currently cached for the connection.
QueryCachePages	Returns the number of cache pages used to cache execution plans.
QueryDescribedBypass	Returns the number of describe requests processed by the optimizer bypass.
QueryDescribedOptimizer	Returns the number of describe requests processed by the optimizer.
QueryHeapPages	Returns the number of cache pages used for query processing (hash and sort operations).
QueryJHToJNLOptUsed	Returns the number of times a hash join was converted to a nested loops join.
QueryLowMemoryStrategy	Returns the number of times the server changed its execution plan during execution as a result of low memory conditions. The strategy can change because less memory is now available than the optimizer estimated, or because the execution plan required more memory than the optimizer estimated.
QueryMemActiveCurr	Returns the number of requests actively using query memory.
QueryMemGrantFailed	Returns the total number of times a request waited for query memory, but failed to get it.
QueryMemGrantGranted	Returns the number of pages currently granted to requests.
QueryMemGrantRequested	Returns the total number of times any request attempted to acquire query memory.
QueryMemGrantWaited	Returns the total number of times any request waited for query memory.
QueryMemGrantWaiting	Returns the current number of requests waiting for query memory.
QueryOpened	Returns the number of OPEN requests for execution.
QueryOptimized	Returns the number of requests fully optimized.
QueryReused	Returns the number of requests that have been reused from the plan cache.
QueryRowsBufferFetch	Returns the number of rows fetched using buffering.
QueryRowsMaterialized	Returns the number of rows are written to work tables during query processing.
quoted_identifier	Returns On if strings enclosed in double quotes are interpreted as identifiers, or Off if they are interpreted as literal strings. See quoted_identifier option [compatibility].
read_past_deleted	Returns On if sequential scans at isolation levels 1 and 2 skip uncommitted deleted rows, and Off if sequential scans block on uncommitted deleted rows at isolation levels 1 and 2. See read_past_deleted option [database].
recovery_time	Returns the maximum length of time, in minutes, that the database server will take to recover from system failure. See recovery_time option [database].
RecursiveIterations	Returns the number of iterations for recursive unions.
RecursiveIterationsHash	Returns the number of times recursive hash join used a hash strategy.
RecursiveIterationsNested	Returns the number of times recursive hash join used a nested loops strategy.
RecursiveJNLMisses	Returns the number of index probe cache misses for recursive hash join.
RecursiveJNLProbes	Returns the number of times recursive hash join attempted an index probe.
remote_idle_timeout	Returns the time, in seconds, of inactivity that web service client procedures and functions will tolerate. See remote_idle_timeout option [database].
replicate_all	Returns On if the database is acting as a primary site in a Replication Server installation; otherwise, returns Off. See replicate_all option [Replication Agent].
ReqCountActive	Returns the number of requests processed, or NULL if the RequestTiming server property is set to Off. See -zt server option.
ReqCountBlockContention	Returns the number of times the connection waited for atomic access, or NULL if the -zt option was not specified. See -zt server option.
ReqCountBlockIO	Returns the number of times the connection waited for I/O to complete, or NULL if the -zt option was not specified. See -zt server option.
ReqCountBlockLock	Returns the number of times the connection waited for a lock, or NULL if the -zt option was not specified. See -zt server option.
ReqCountUnscheduled	Returns the number of times the connection waited for scheduling, or NULL if the -zt option was not specified. See -zt server option.
ReqStatus	Returns the status of the request. It can be one of the following values: Idle The connection is not currently processing a request. Unscheduled* The connection has work to do and is waiting for a worker thread. BlockedIO* The connection is blocked waiting for an I/O. BlockedContention* The connection is blocked waiting for access to shared database server data structures. BlockedLock The connection is blocked waiting for a locked object. Executing The connection is executing a request. The values marked with an asterisk (*) are only returned when logging of request timing information has been turned on for the database server using the -zt server option. If request timing information is not being logged (the default), the values are reported as Executing. For more information, see -zt server option.
ReqTimeActive	Returns the amount of time spent processing requests, or NULL if the -zt option was not specified. See -zt server option.
ReqTimeBlockContention	Returns the amount of time spent waiting for atomic access, or NULL if the RequestTiming server property is set to Off. See -zt server option.
ReqTimeBlockIO	Returns the amount of time spent waiting for I/O to complete, or NULL if the -zt option was not specified. See -zt server option.
ReqTimeBlockLock	Returns the amount of time spent waiting for a lock, or NULL if the -zt option was not specified. See -zt server option.
ReqTimeUnscheduled	Returns the amount of unscheduled time, or NULL if the -zt option was not specified. See -zt server option.
ReqType	Returns the type of the last request.
request_timeout	Returns the maximum time a single request can run. See request_timeout option [database].
RequestsReceived	Returns the number of client/server communication requests or round trips. It is different from PacketsReceived in that multi-packet requests count as one request, and liveness packets are not included.
return_date_time_as_string	Returns On if date, time, and timestamp values are returned to applications as a string, and Off if they are returned as a date or time data type. See return_date_time_as_string option [database].
Rlbk	The number of rollback requests that have been handled.
rollback_on_deadlock	Returns After when referential integrity actions are executed after the UPDATE or DELETE, and Before if they are executed before the UPDATE or DELETE. See rollback_on_deadlock [database].
RollbackLogPages	Returns the number of pages in the rollback log.
row_counts	Returns On if the row count is always accurate, and Off if the row count is usually an estimate. See row_counts option [database].
scale	Returns the decimal and numeric scale for the connection. See scale option [database].
secure_feature_key	Stores the key that is used to enable and disable features for a database server. Selecting the value of this property always returns an empty string.
ServerNodeAddress	Returns the node for the server in a client/server connection. When the client and server are both on the same computer, an empty string is returned.
ServerPort	Returns the database server's TCP/IP port number or 0.
SessionCreateTime	Returns the time the HTTP session was created.
SessionID	Returns the session ID for the connection if one has been defined, otherwise, returns an empty string.
SessionLastTime	Returns the time of the last request for the HTTP session.
SessionTimeout	Returns the time, in minutes, the HTTP session persists during inactivity. See sa_set_http_option system procedure.
SnapshotCount	Returns the number of snapshots associated with the connection.
sort_collation	Returns Internal if the ORDER BY clause remains unchanged, otherwise the collation name or collation ID is returned. See sort_collation option [database].
SortMergePasses	Returns the number of merge passes used during sorting.
SortRowsMaterialized	Returns the number of rows written to sort work tables.
SortRunsWritten	Returns the number of sorted runs written during sorting.
SortSortedRuns	Returns the number of sorted runs created during run formation.
SortWorkTables	Returns the number of work tables created for sorting.
sql_flagger_error_level	Returns one of the following values to indicate which SQL that is not part of a specified set of SQL/2003 is flagged as an error: E Flag syntax that is not entry-level SQL/2003 syntax I Flag syntax that is not intermediate-level SQL/2003 syntax F Flag syntax that is not full-SQL/2003 syntax W Allow all supported syntax For more information, see sql_flagger_error_level option [compatibility].
sql_flagger_warning_level	Returns one of the following values to indicate which SQL that is not part of a specified set of SQL/2003 is flagged as a warning: E Flag syntax that is not entry-level SQL/2003 syntax I Flag syntax that is not intermediate-level SQL/2003 syntax F Flag syntax that is not full-SQL/2003 syntax W Allow all supported syntax For more information, see sql_flagger_warning_level option [compatibility].
StatementDescribes	Returns the total number of statements processed by DESCRIBE requests.
StatementPostAnnotates	Returns the number of statements processed by the semantic query transformation phase.
StatementPostAnnotatesSimple	Returns the number of statements processed by the semantic query transformation phase, but which skipped some of the semantic transformations.
StatementPostAnnotatesSkipped	Returns the number of statements that have completely skipped the semantic query transformation phase.
string_rtruncation	Returns On if an error is raised when a string is truncated, and returns Off if an error is not raised and the character string is silently truncated. See string_rtruncation option [compatibility].
subsume_row_locks	Returns On if the database server acquires individual row locks for a table, otherwise, returns Off. See subsume_row_locks option [database].
suppress_tds_debugging	Returns Off if TDS debugging information appears in the database server messages window, and returns On if debugging information does not appear in the database server messages window. See suppress_tds_debugging option [database].
synchronize_mirror_on_commit	Returns On if the database mirror server is synchronized on commit, otherwise returns Off. See synchronize_mirror_on_commit option [database].
tds_empty_string_is_null	Returns On if empty strings are returned as NULL for TDS connections, and returns Off if a string containing one blank character is returned for TDS connections. See tds_empty_string_is_null option [database].
temp_space_limit_check	Returns On if the database server checks the amount of temporary space available for a connection, and returns Off if the database server does not check the amount of space available for a connection. See temp_space_limit_check option [database].
TempTablePages	Returns the number of pages in the temporary file used for temporary tables.
time_format	Returns the string format used for times retrieved from the database. See time_format option [compatibility].
time_zone_adjustment	Returns the number of minutes that must be added to the Coordinated Universal Time (UTC) to display time local to the connection. See time_zone_adjustment option [database].
timestamp_format	Returns the number of minutes that must be added to the Coordinated Universal Time (UTC) to display time local to the connection. See timestamp_format option [compatibility].
TimeZoneAdjustment	Returns the number of minutes that must be added to the Coordinated Universal Time (UTC) to display time local to the connection. See time_zone_adjustment option [database].
TransactionStartTime	Returns a string containing the time the database was first modified after a COMMIT or ROLLBACK, or an empty string if no modifications have been made to the database since the last COMMIT or ROLLBACK.
truncate_timestamp_values	Returns On if the number of decimal places used in the timestamp values is limited, otherwise, returns Off. See truncate_timestamp_values option [database] [MobiLink client].
tsql_outer_joins	Returns On if Transact-SQL outer joins can be used in DML statements. See tsql_outer_joins option [compatibility].
tsql_variables	Returns On if you can use the @ sign instead of the colon as a prefix for host variable names in embedded SQL, otherwise, returns Off. See tsql_variables option [compatibility].
UncommitOp	Returns the number of uncommitted operations.
updatable_statement_isolation	Returns the isolation level (0, 1, 2, or 3) used by updatable statements when the isolation_level option is set to readonly-statement-snapshot. See updatable_statement_isolation option [database].
update_statistics	This property is reserved for system use. Do not change the setting of this option.
upgrade_database_capability	This property is reserved for system use. Do not change the setting of this option.
user_estimates	Returns one of the following values that controls whether selectivity estimates in query predicates are respected or ignored by the query optimizer: Enabled All user-supplied selectivity estimates are respected. You can also use On to turn on this option. Override-Magic A user selectivity estimate is respected and used only if the optimizer would otherwise choose to use its last-resort, heuristic value (also called the magic value). Disabled All user estimates are ignored and magic values are used when no other estimate data is available. You can also use Off to turn off this option. For more information, see user_estimates option [database].
UserAppInfo	Returns the string specified by the AppInfo connection parameter in a connection string. For more information, see AppInfo connection parameter [APP].
UserID	Returns the user ID for the connection.
UtilCmdsPermitted	Returns On or Off to indicate whether utility commands such as CREATE DATABASE, DROP DATABASE, and RESTORE DATABASE are permitted for the connection. See -gu server option.
verify_password_function	Returns the name of the function used for password verification if one has been specified. See verify_password_function option [database].
wait_for_commit	Returns On if the database does not check foreign key integrity until the next COMMIT statement. Otherwise, returns Off and all foreign keys that are not created with the check_on_commit option are checked as they are inserted, updated or deleted. See wait_for_commit option [database].
WaitStartTime	Returns the time at which the connection started waiting (or an empty string if the connection is not waiting).
WaitType	Returns the reason for the wait, if it is available. Possible values for WaitType are: lock Returned if the connection is waiting on a lock. waitfor Returned if the connection is executing a waitfor statement. empty string Returned if the connection is not waiting, or if the reason for the wait is not available.
webservice_namespace_host	Returns the hostname to be used as the XML namespace within generated WSDL documents if one has been specified. See webservice_namespace_host option [database].

Discuss this page in DocCommentXchange.
Send feedback about this page using email.

Connection properties

Examples

To retrieve the value of a connection property

To retrieve the values of all connection properties

See also

Descriptions