Contents Index GRANT statement GRANT PUBLISH statement [SQL Remote]

ASA SQL Reference
  SQL Statements

GRANT CONSOLIDATE statement [SQL Remote]


Description 

Use this statement to identify the database immediately above the current database in a SQL Remote hierarchy, who will receive messages from the current database.

Syntax 

GRANT CONSOLIDATE
TO userid
TYPE message-system, ...
ADDRESS address-string, ...
SEND { EVERY | AT }'hh:mm:ss' ]

message-systemFILE | FTP | MAPI | SMTP | VIM

addressstring

Parameters 

userid    The user ID for the user to be granted the permission

message-system    One of the message systems supported by SQL Remote.

address    The address for the specified message system.

Usage 

In a SQL Remote installation, the database immediately above the current database in a SQL Remote hierarchy must be granted CONSOLIDATE permissions. GRANT CONSOLIDATE is issued at a remote database to identify its consolidated database. Each database can have only one user ID with CONSOLIDATE permissions: you cannot have a database that is a remote database for more than one consolidated database.

The consolidated user is identified by a message system, identifying the method by which messages are sent to and received from the consolidated user. The address-name must be a valid address for the message-system, enclosed in single quotes. There can be only one consolidated user per remote database.

For the FILE message type, the address is a subdirectory of the directory pointed to by the SQLREMOTE environment variable.

The GRANT CONSOLIDATE statement is required for the consolidated database to receive messages, but does not by itself subscribe the consolidated database to any data. To subscribe to data, a subscription must be created for the consolidated user ID to one of the publications in the current database. Running the database extraction utility at a consolidated database creates a remote database with the proper GRANT CONSOLIDATE statement already issued.

The optional SEND EVERY and SEND AT clauses specify a frequency at which messages are sent. The string contains a time that is a length of time between messages (for SEND EVERY) or a time of day at which messages are sent (for SEND AT). With SEND AT, messages are sent once per day.

If a user has been granted remote permissions without a SEND EVERY or SEND AT clause, the Message Agent processes messages, and then stops. In order to run the Message Agent continuously, you must ensure that every user with REMOTE permission has either a SEND AT or SEND EVERY frequency specified.

It is anticipated that at many remote databases, the Message Agent will be run periodically, and that the consolidated database will have no SEND clause specified.

Permissions 

Must have DBA authority.

Side effects 

Automatic commit.

See also 

GRANT PUBLISH statement [SQL Remote]

GRANT REMOTE statement [SQL Remote]

REVOKE CONSOLIDATE statement [SQL Remote]

sp_grant_consolidate procedure

Example 
GRANT CONSOLIDATE TO con_db
TYPE mapi
ADDRESS 'Consolidated Database'

Contents Index GRANT statement GRANT PUBLISH statement [SQL Remote]