ASA SQL Reference
  SQL Statements

GRANT REMOTE statement [SQL Remote]

Description 

Use this statement to identify a database immediately below the current database in a SQL Remote hierarchy, who will receive messages from the current database. These are called remote users.

Syntax 

GRANT REMOTE TO userid, ...
TYPE message-system, ...
ADDRESS address-string, ...
[ SEND { EVERY | AT } send-time ]

Parameters 

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

message-system    One of the message systems supported by SQL Remote. It must be one of the following values:

• FILE

• FTP

• MAPI

• SMTP

• VIM

address-string    A string containing a valid address for the specified message system.

send-time    A string containing a time specification in the form hh:mm:ss.

Usage 

In a SQL Remote installation, each database receiving messages from the current database must be granted REMOTE permissions.

The single exception is the database immediately above the current database in a SQL Remote hierarchy, which must be granted CONSOLIDATE permissions.

The remote 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.

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

The GRANT REMOTE statement is required for the remote database to receive messages, but does not by itself subscribe the remote user to any data. To subscribe to data, a subscription must be created for the user ID to one of the publications in the current database, using the database extraction utility or the CREATE SUBSCRIPTION statement.

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 consolidated databases, the Message Agent will be run continuously, so that all remote databases would have a SEND clause specified. A typical setup may involve sending messages to laptop users daily (SEND AT) and to remote servers every hour or two (SEND EVERY). You should use as few different times as possible, for efficiency.

Permissions 

Must have DBA authority.

Side effects 

Automatic commit.

See also 

GRANT PUBLISH statement [SQL Remote]

REVOKE REMOTE statement [SQL Remote]

GRANT CONSOLIDATE statement [SQL Remote]

sp_grant_remote procedure

Granting and revoking REMOTE and CONSOLIDATE permissions

Standards and compatibility 

• SQL/92    Vendor extension.

• SQL/99    Vendor extension.

Example 

• The following statement grants remote permissions to user SamS, using a MAPI e-mail system, sending messages to the address Singer, Samuel once every two hours:

  GRANT REMOTE TO SamS
  TYPE mapi
  ADDRESS 'Singer, Samuel'
  SEND EVERY '02:00'