Data Replication with SQL Remote
   PART 4. Reference
     CHAPTER 16. System Objects for Adaptive Server Enterprise       

SQL Remote system tables

This section describes the database tables used by SQL Remote to define and manage SQL Remote information.

Caution    
These tables are for use only by SQL Remote. Do not alter these tables or their contents directly.

  #remote table

Function 

This temporary table is created by the Message Agent to hold the name of the current remote user and of the current publisher. This table exists only in Adaptive Server Enterprise.

Columns 

Column	Data type	Description
current_remote_user	VARCHAR(128)	Current remote user (from the Message Agent command line).
current_publisher	VARCHAR(128)	Current publisher

Description 

    This is not a system table. When the Message Agent for Adaptive Server Enterprise connects to the server, it holds the value of the current remote user ID and the value of the current publisher in the #remote table. This temporary table is held in TEMPDB.

The values from #remote can be used in conflict resolution procedures.

The CREATE TABLE statement for this table is:

CREATE TABLE #remote (
   current_remote_user varchar(128),
   current_publisher varhcar(128)
)

The table has a single row.

  sr_article table

Function 

Each row describes an article in a SQL Remote publication.

Columns 

Column	Data type	Description
publication_id	INT	The publication of which this article is a part.
table_id	INT	Each article consists of columns and rows from a single table. This column contains the table ID for this table.
where_expr	VARCHAR(128)	For articles that contain a subset of rows defined by a WHERE clause, this column contains the search condition.
subscribe_by_expr	VARCHAR(128)	For articles that contain a subset of rows defined by a SUBSCRIBE BY expression, this column contains the expression.

  sr_articlecol table

Function 

Each row identifies a column in an article, identifying the column, the table it is in, and the publication it is part of.

Columns 

Column	Data type	Description
publication_id	INT	A unique identifier for the publication of which the column is a part.
table_id	INT	The table to which the column belongs.
column_id	INT	The column identifier, from the SYSCOLUMN system table.

  sr_marker table

Function 

To ensure that messages received by the Message Agent are sent to remote databases in the same session

Columns 

Column	Data type	Description
marker	DATETIME	A time value indicating when the latest messages were applied.

Description 

When a consolidated database uses two Message Agents, one to populate the stable queue (-i) and one to receive and send messages (-r -s), the single row of the sr_marker table is used to ensure that messages received and applied to the database are sent before the Message Agent closes down.

  sr_object table

Function 

Holds a list of SQL Remote objects. The extraction utility needs to know not to extract the SQL Remote system objects. The sp_populate_sql_anywhere procedure that creates a set of Adaptive Server Anywhere system tables in TEMPDB gets a list of SQL Remote objects from the sr_object table.

Columns 

Column	Data type	Description
name	VARCHAR(128)	The name of the object.
type	CHAR(1)	One of the following: U     User-defined table V     View P     Procedure

  sr_option table

Function 

Each row describes a replication option used by SQL Remote.

Columns 

Column	Data type	Description
option	VARCHAR(128)	The name of the option.
value	VARCHAR(128)	The setting for the option.

Description 

     For information about available options, see SQL Remote options.

  sr_passthrough table

Function 

Each row describes a passthrough operation being sent to a user or to subscribers to a publication.

Columns 

Column	Data type	Description
operation	VARCHAR(20)	A passthrough operation, or piece of a passthrough operation, entered using sp_passthrough or sp_passthrough_piece.
value	VARCHAR(255)	A subscription column value indicating which users are to receive the operation.
id	INT	A user who is to receive the operation.

  sr_publication table

Function 

Each row describes a SQL Remote publication.

Columns 

Column	Data type	Description
publication_id	INT	An identifier for the publication
publication_name	VARCHAR(128)	The name of the publication.

  sr_publisher table

Function 

The row holds the user ID of the publisher.

Columns 

Column	Data type	Description
user_id	INT	The user ID of the publisher.

  sr_remotetable table

Function 

Each row describes a table that is marked for replication using SQL Remote.

Columns 

Column	Data type	Description
table_id	INT	The id of the table.
resolve_name	VARCHAR(128)	The name of the stored procedure to be executed in the case of conflicts.
old_row_name	VARCHAR(128)	The table that holds the old row name.
remote_row_name	VARCHAR(128)	The table that holds the remote row name.

  sr_remotetype table

Function 

Each row describes one of the SQL Remote message types, including the publisher address.

Columns 

Column	Data type	Description
type_id	INT	An identification number for the message type.
type_name	VARCHAR(128)	The message type. There is a separate row for each of the following: FILE MAPI VIM SMTP
publisher_address	VARCHAR(128)	The publisher's address for the message type type_name.

  sr_remoteuser table

Function 

Each row describes a user ID with REMOTE permissions (a subscriber), together with the status of SQL Remote messages sent to and from that user.

Columns 

Column	Data type	Description
user_id	INT	The user ID of the user with REMOTE permissions.
operation	VARCHAR(10)
consolidate	CHAR(1)	The column contains either an N to indicate a user granted REMOTE permissions, or a Y to indicate a user granted CONSOLIDATE permissions.
type_id	INT	The ID of the message system used to send messages to this user.
address	VARCHAR(128)	The address to which SQL Remote messages are to be sent. The address must be appropriate for the address_type.
frequency	CHAR(1)	How frequently SQL Remote messages are to be sent.
send_time	DATETIME	The next time messages are to be sent to this user.
log_send	NUMERIC(20, 0)	Messages are sent only to subscribers for whom log_send is greater than log_sent.
time_sent	DATETIME	The time the most recent message was sent to this subscriber.
log_sent	NUMERIC(20, 0)	The log offset for the most recently sent operation.
confirm_sent	NUMERIC(20, 0)	The log offset for the most recently confirmed operation from this subscriber.
send_count	INT	How many SQL Remote messages have been sent.
resend_count	INT	Counter to ensure messages are applied only once at the subscriber database.
time_received	DATETIME	The time the most recent message was received from this subscriber.
log_received	NUMERIC(20, 0)	The log offset in the subscriber's database for the operation most recently received at the current database.
confirm_received	NUMERIC(20, 0)	The log offset in the subscriber's database for the most recent operation for which a confirmation message has been sent.
receive_count	INT	How many messages have been received.
rereceive_count	INT	Counter to ensure messages are applied only once at the current database.
filler1	CHAR(255)
filler2	CHAR(255)
filler3	CHAR(255)
filler4	CHAR(255)

  sr_subscription table

Function 

Each row describes a subscription from one user ID (which must have REMOTE permissions) to one publication.

Columns 

Column	Data type	Description
publication_id	INT	The identifier for the publication to which the user ID is subscribed.
user_id	INT	The user ID that is subscribed to the publication.
subscribe_by	VARCHAR(128)	For publications with a SUBSCRIBE BY expression, this column holds the matching value for this subscription.
created	NUMERIC(20, 0)	The offset in the transaction log at which the subscription was created.
started	NUMERIC(20, 0)	The offset in the transaction log at which the subscription was started.
operation	VARCHAR(20)