Reference Manual
   CHAPTER 14. System Procedures and Functions     

System and catalog stored procedures

System and catalog stored procedures are owned by the user ID dbo. Some of these procedures are for internal system use. This section documents those not intended solely for system and internal use.

  sa_conn_info system procedure

Function 

Reports connection property information.

Syntax 

sa_conn_info ( [ connection-id ] )

Permissions 

None.

Side effects 

None

Description 

Returns a result set consisting of the Number, Name, Userid, DBNumber, LastReqTime, ProcessTime, Port, ReqType, CommLink, NodeAddr, LastIdle, CurrTaskSw, BlockedOn, and UncmtOps properties for each connection. If no connection-id is supplied, information for all current connections to databases on the server is returned.

In a deadlock situation, the BlockedOn value returned by this procedure allows you to check which users are blocked, and who they are blocked on.

  sa_conn_properties system procedure

Function 

Reports connection property information

Syntax 

sa_conn_properties ( [ connection-id ] )

Permissions 

None.

Side effects 

None

See also 

sa_conn_properties_by_conn system procedure

sa_conn_properties_by_name system procedure

Description 

Returns the connection id as Number, and the PropNum, PropName, PropDescription, and Value for each available connection property.

     For a listing of available connection properties, see System functions. These system procedures are owned by the dbo user ID. The PUBLIC group has EXECUTE permission on these procedures.

  sa_conn_properties_by_conn system procedure

Function 

Reports connection property information

Syntax 

sa_conn_properties_by_conn ( [property-name ] )

Permissions 

None.

Side effects 

None

See also 

sa_conn_properties system procedure

Description 

This is a variant on the sa_conn_properties system procedure. It returns the connection id as Number, and the PropNum, PropName, PropDescription, and Value, but only for connection properties that match the property-name string. You can use wild cards in property-name, as the comparison uses a LIKE operator. The result set is sorted by number and property name.

     For a listing of available connection properties, see System functions. These system procedures are owned by the dbo user ID. The PUBLIC group has EXECUTE permission on these procedures.

Example 

• The following statement returns the AnsiNull option setting for the current connection:

  call sa_conn_properties_by_conn( 'ansinull' )

• The following statement returns the Ansi-related option settings for the current connection:

  call sa_conn_properties_by_conn( 'ansi%' )

  sa_conn_properties_by_name system procedure

Function 

Reports connection property information

Syntax 

sa_conn_properties_by_name ( [connection-id ] )

Permissions 

None.

Side effects 

None

See also 

sa_conn_properties system procedure

Description 

This is a variant on the sa_conn_properties system procedure. It returns the connection id as Number, and the PropNum, PropName, PropDescription, and Value for each available connection property. The information is sorted by property name and number.

     For a listing of available connection properties, see System functions. These system procedures are owned by the dbo user ID. The PUBLIC group has EXECUTE permission on these procedures.

  sa_db_info system procedure

Function 

Reports database property information

Syntax 

sa_db_info ( [ database-id ] )

Permissions 

None.

Side effects 

None

See also 

sa_db_properties system procedure

Description 

Returns a single row containing the Number, Alias, File, ConnCount, PageSize, and LogName for the specified database.

Examples 

• The following statement returns a single row describing the current database:

  call sa_db_info

  Sample values are as follows:

  Property	Value
  Number	0
  Alias	asademo
  File	c:\asa6\asademo.db
  ConnCount	1
  PageSize	1024
  LogName	c:\asa6\asademo.log

  sa_db_properties system procedure

Function 

Reports database property information

Syntax 

sa_db_properties ( [ database-id ] )

Permissions 

None.

Side effects 

None

See also 

sa_db_info system procedure

Description 

Returns the database ID number and the Number, PropNum, PropName, PropDescription, and Value, for each property returned by the sa_db_info system procedure.

  sa_eng_properties system procedure

Function 

Reports database server property information

Syntax 

sa_eng_properties

Permissions 

None.

Side effects 

None

Description 

Returns the PropNum, PropName, PropDescription, and Value for each available engine property.

     For a listing of available engine properties, see System functions.

Example 

• The following statement returns a set of available server properties

  call sa_eng_properties()

  PropNum	PropName	...
  0	IdleCheck	...
  1	IdleWrite	...
  2	IdleChkPt	...
  ...	...	...

  sa_table_page_usage system procedure

Function 

Reports information about the usage of database tables.

Syntax 

sa_table_page_usage

Permissions 

None.

Side effects 

None

See also 

The Information utility

Description 

• The results include the same information provided by the Information utility.

     For information on the Information utility, see The Information utility.

  sa_validate system procedure

Function 

To validate all tables in a database.

Syntax 

sa_validate

Permissions 

None

Side effects 

None

Description 

This procedure is equivalent to using the Validation utility from outside the database.

     For information on the Validation utility, see The Validation utility.

Example 

• Validate all tables in the current database

  sa_validate

  The procedure returns a single column, named msg. If all tables are valid, the column contains No errors detected.

  sp_login_environment system procedure

Function 

To set connection options when users log in.

Syntax 

sp_login_environment

Permissions 

None

Side effects 

None

Description 

At startup, sp_login_environment is the default procedure called by the LOGIN_PROCEDURE database option.

It is recommended that you not edit this procedure. Instead, to change the login environment, set the LOGIN_PROCEDURE option to point to a different procedure.

     For more information about LOGIN_PROCEDURE, see LOGIN_PROCEDURE option.

Example 

• Here is the text of the sp_login_environment procedure:

  CREATE PROCEDURE dbo.sp_login_environment()
  BEGIN
    IF connection_property('CommProtocol')='TDS' THEN
      CALL dbo.sp_tsql_environment()
    END IF
  END

  sp_remote_columns system procedure

Function 

This procedure will produce a list of the columns on a remote table and a description of those data types.

Syntax 

sp_remote_columns servername [,tablename] [, owner ] [, database]

Permissions 

None

Side effects 

None

Description 

If you are entering a CREATE EXISTING statement and you are specifiying a column list, it may be helpful to get a list of the columns that are available on a remote table. sp_remote_columns will produce a list of the columns on a remote table and a description of those data types.

Standards and compatibility 

• SQL/92     Entry-level feature.

• Sybase     Supported by Open Client/Open Server.

Example 

• To get a list of the columns in the sysobjects table in the production database in an ASE named "asetest".

  sp_remote_columns  asetest, sysobjects, 
  null, production

  sp_remote_tables system procedure

Function 

This procedure returns a list of the tables on a server.

Syntax 

sp_remote_tables servername [,tablename] [, owner ] [, database ]

Permissions 

None

Side effects 

None

Description 

It may be helpful when you are configuring your ASA to get a list of the remote tables available on a particular server. This procedure returns a list of the tables on a server.

If a tablename, owner, or database name is given, the list of tables will be limited to only those that match.

Standards and compatibility 

• SQL/92     Entry-level feature.

• Sybase     Supported by Open Client/Open Server.

Examples 

• To get a list of all of the Microsoft Excel worksheets available from an ODBC datasource named 'excel':

  sp_remote_tables  excel

• To get a list of all of the tables in the 'production' database in an ASE named "asetest", owned by 'fred':

  sp_remote_tables  asetest, null, fred, production

  sp_servercaps system procedure

Function 

To display information about a remote server's capabilities.

Syntax 

sp_servercaps servername

Permissions 

None

Side effects 

None

Description 

This procedure will display information about a remote server's capabilities. Adaptive Server Anywhere will use this capability information to determine how much of a SQL statement can be passed of to a remote server. The system tables which contain server capabilities are not populated until after Adaptive Server Anywhere first connects to the remote server. This information comes from syscapability and syscapabilityname. The servername specified must be the same servername used in the CREATE SERVER statement.

Standards and compatibility 

• SQL/92     Entry-level feature.

• Sybase     Supported by Open Client/Open Server.

Example 

• To display information about the remote server testasa issue the following stored procedure:

  sp_servercaps  testasa

  sp_tsql_environment system procedure

Function 

To set connection options when users connect from jConnect or Open Client applications.

Syntax 

sp_tsql_environment

Permissions 

None

Side effects 

None

See also 

sp_login_environment system procedure

Description 

At startup, sp_login_environment is the default procedure called by the LOGIN_PROCEDURE database option. If the connection uses the TDS communicatino protocol (that is, if it is an Open Client or jConnect connection), then sp_login_environment in turn calls sp_tsql_environment.

This procedure sets database options so that they are compatible with default Sybase Adaptive Server Enterprise behavior.

If you wish to change the default behavior, it is recommended that you create new procedures and alter your LOGIN_PROCEDURE option to point to these new procedures.

     For more information about LOGIN_PROCEDURE, see LOGIN_PROCEDURE option.

Example 

• Here is the text of the sp_tsql_environment procedure:

  create procedure dbo.sp_tsql_environment()
  begin
    if db_property('IQStore')='OFF' then
      -- ASA datastore
      set temporary option AUTOMATIC_TIMESTAMP='ON'
    end if;
    set temporary option ANSINULL='OFF';
    set temporary option TSQL_VARIABLES='ON';
    set temporary option ANSI_BLANKS='ON';
    set temporary option TSQL_HEX_CONSTANT='ON';
    set temporary option CHAINED='OFF';
    set temporary option QUOTED_IDENTIFIER='OFF';
    set temporary option ALLOW_NULLS_BY_DEFAULT='OFF';
    set temporary option CONTINUE_AFTER_RAISERROR='ON';
    set temporary option FLOAT_AS_DOUBLE='ON';
    set temporary option ISOLATION_LEVEL='1';
    set temporary option DATE_FORMAT='YYYY-MM-DD';
    set temporary option TIMESTAMP_FORMAT='YYYY-MM-DD HH:NN:SS.SSS';
    set temporary option TIME_FORMAT='HH:NN:SS.SSS';
    set temporary option DATE_ORDER='MDY';
    set temporary option ESCAPE_CHARACTER='OFF'
  end