SET REMOTE OPTION statement [SQL Remote]

Sets a message control parameter for a SQL Remote message link.

Syntax

SET REMOTE message-system OPTION
[ userid.| PUBLIC.]option-name = option-value

message-system :
FILE 
| FTP 
| HTTP 
| SMTP

option-name :
common-options 
| file-options 
| ftp-options 
| smtp-options

common-options :
debug 
| encode_dll
| max_retries
| output_log_send_on_error
| output_log_send_limit 
| output_log_send_now
| pause_after_failure

file-options :
directory 
| invalid_extensions 
| unlink_delay

ftp-options :
active_mode 
| host 
| invalid_extensions
| password 
| port 
| root_directory 
| reconnect_retries 
| reconnect_pause
| suppress_dialogs 
| user

http-options :
| certificate 
| client_port 
| https
| password 
| proxy 
| reconnect_retries 
| reconnect_pause 
| root_directory 
| url 
| user

smtp-options :
 local_host 
| pop3_host 
| pop3_password 
| pop3_port 
| pop3_userid
| smtp_authenticate
| smtp_option
| smtp_password
| smtp_port
| smtp_userid
| suppress_dialogs 
| top_supported

option-value : string

Parameters

userid
If you do not specify a userid, then the current publisher is assumed.
common-options
These options are common to the FILE, FTP, HTTP, and SMTP message systems:
- debug
  This parameter is set either to YES or NO. The default is NO. When set to YES, debug output specific to the message system is displayed. This information can be used for troubleshooting problems in the message system.
- max_retries
  By default, when SQL Remote is running in continuous mode and an error occurs when accessing the message system, it shuts down after the send and/or received phases. Use this parameter to specify the number of times you want SQL Remote to retry the send and/or receive phases before it shuts down.
- output_log_send_on_error
  Sends log information when an error occurs.
- output_log_send_limit
  Limits the amount of information that is sent to the consolidated database. The output_log_send_limit option specifies the number of bytes at the end of the output log (that is, the most recent entries) that are sent to the consolidated database. The default is 5K.
- output_log_send_now
  When set to YES, sends output log information to the consolidated database. On the next poll, the remote database sends the output log information and then resets the output_log_send_now option to NO.
- pause_after_failure
  This parameter applies when the max_retries parameter is specified to a value other than zero and SQL Remote is running in continuous mode. When an error occurs in the message system, this parameter defines the number of seconds SQL Remote waits between retrying the send and/or receive phases.
- encode_dll
  If you have implemented a custom encoding scheme, you must set this to the full path of the custom encoding DLL that you created.
file-options
These options apply to the FILE message system only:
- directory
  The directory under which the messages are stored. This parameter is an alternative to the SQLREMOTE environment variable.
- invalid_extensions
  A comma-separated list of file extensions that you do not want the SQL Remote Message Agent (dbremote) to use when generating files in the messaging system.
- unlink_delay
  The number of seconds to wait before attempting to delete a file if the previous attempt to delete the file failed. If no value is defined for unlink_delay, then the default behavior is set to pause for 1 second after the first failed attempt, 2 seconds after the second failed attempt, 3 seconds after the third failed attempt, and 4 seconds after the fourth failed attempt.
ftp-options
These options apply to the FTP message system only:
- active_mode
  This parameter controls how SQL Remote establishes the server/client connection. This parameter is set either to YES or NO. The default is NO (passive mode). Passive mode is the preferred transfer mode and the default for the FTP message system. In passive mode, all data transfer connections are initiated by the client, in this case, the message system. In active mode, the FTP server initiates all data connections.
- host
  The host name of the computer where the FTP server is running. This parameter can be a host name (such as ftp.sap.com) or an IP address (such as 192.138.151.66).
- invalid_extensions
  A comma-separated list of file extensions that you do not want dbremote to use when generating files in the messaging system.
- password
  The password for accessing the FTP host.
- port
  The IP port number used for the FTP connection. This parameter is usually not required.
- reconnect_retries
  The number of times the message system should try to open a socket with the server before failing. The default value is 4. When you set this parameter, only reconnections are affected. The initial connection made by the FTP message system is not affected.
- reconnect_pause
  The time in seconds to pause between each connection attempt. The default setting is 30 seconds. When you set this parameter, only reconnections are affected. The initial connection made by the FTP message system is not affected.
- root_directory
  The root directory within the FTP host site that the messages are stored under.
- suppress_dialogs
  This parameter is set to TRUE or FALSE. If it is set to TRUE, the Connect window does not appear after failed attempts to connect to the FTP server. Instead, an error is generated.
- user
  The user name for accessing the FTP host.

http-options

These options apply to the HTTP message system only:

certificate

To make a secure (HTTPS) request, a client must have access to the certificate used by the HTTPS server. The necessary information is specified in a string of semicolon-separated keyword=value pairs. You can use the file keyword to specify the file name of the certificate. You cannot specify a file and certificate keyword together. The following keywords are available:

Keyword	Abbreviation	Description
file		The file name of the certificate
certificate	cert	The certificate itself
company	co	The company specified in the certificate
unit		The company unit specified in the certificate
name		The common name specified in the certificate

Certificates are required only for requests that are either directed to an HTTPS server or can be redirected from a non-secure to a secure server. Only PEM formatted certificates are supported (for example, certificate='file=filename'

To create a certificate name in a SQL Anywhere database:

CREATE OR REPLACE CERTIFICATE certificate_name FROM FILE 'certificate_file';

To use the certificate name for an HTTPS message type:

SET REMOTE HTTP OPTION user_name.certificate= 'cert_name=certificate_name';

client_port
Identifies the port number on which SQL Remote communicates using HTTP. It is provided for, and recommended only for, connections through firewalls that filter "outgoing" TCP/IP connections. You can specify a single port number, ranges of port numbers, or a combination of the two. Specifying a low number of client ports could result in SQL Remote being unable to send and receive messages if the operating system has not released the ports in a timely manner after SQL Remote closes the port on a previous run.
debug
When set to YES, all HTTP commands and responses are displayed in the output log. This information can be used for troubleshooting HTTP support problems. The default is NO.
https
Specify whether to use HTTPS (https=yes) or HTTP (https=no).
password
The message server database password. The password authenticates to third-party HTTP servers and gateways using RFC 2617 Basic authentication.
proxy_host
Specifies the URI of a proxy server. For use when SQL Remote must access the network through a proxy server. Indicates that SQL Remote is to connect to the proxy server and send the request to the message server through it.
reconnect_retries
The number of times the message system should try to open a socket with the server before failing. The default value is 4. When you set this parameter, only reconnections are affected. The initial connection made by the FTP message system is not affected.
reconnect_pause
The time in seconds to pause between each connection attempt. The default setting is 30 seconds. When you set this parameter, only reconnections are affected. The initial connection made by the FTP message system is not affected.
root_directory
This HTTP control parameter is ignored when specified at the client side. You define this control parameter in the message server prior to calling the sr_add_message_server or sr_update_message_server stored procedure. When using the HTTP message system, the address specified for a remote user or publisher can only contain a single subdirectory, and not multiple subdirectories.
url
Specify the server name or IP address and optionally the port number of the HTTP server being used, separated by a semicolon. If requests are being passed through the Relay Server, you can optionally add a URL extension to indicate which server farm the request should be passed to.
user
The message server database user ID. Authenticates to third-party HTTP servers and gateways using RFC 2617 Basic authentication.

smtp-options
These options apply to the SMTP message system only:
- local_host
  The name of the local computer. It is useful on computers where SQL Remote is unable to determine the local host name. The local host name is needed to initiate a session with any SMTP server. In most network environments, the local host name can be determined automatically and this entry is not needed.
- pop3_host
  The name of the computer on which the POP host is running. Typically, it is the same name as the SMTP host. It corresponds to the POP3 host field in the SMTP/POP3 login window.
- pop3_password
  The password used to retrieve mail. It corresponds to the password field in the SMTP/POP3 login window.
- pop3_port
  The number of the port on which the POP server is listening. The default is 110. This corresponds to the port field in the SMTP/POP3 login window.
- pop3_userid
  The user ID used to retrieve mail. The POP user ID corresponds to the user ID field in the SMTP/POP3 login window. You must obtain a user ID from your POP host administrator.
- smtp_host
  The name of the computer on which the SMTP server is running. It corresponds to the SMTP host field in the SMTP/POP3 login window
- top_supported
  SQL Remote uses a POP3 command called TOP when enumerating incoming messages. The TOP command may not be supported by all POP servers. When you set the top_supported parameter to NO, SQL Remote uses the RETR command, which is less efficient but works with all POP servers. The default is YES.
- smtp_authenticate
  Determines whether the SMTP message system authenticates the user. The default value is YES. Set this parameter to NO to turn off SMTP authentication.
- smtp_userid
  The user ID for SMTP authentication. By default, this parameter takes the same value as the pop3_userid parameter. The smtp_userid only needs to be set if the user ID is different from that of the POP server.
- smtp_password
  The password for SMTP authentication. By default, this parameter takes the same value as the pop3_password parameter. The smtp_password only needs to be set if the user ID is different from that of the POP server.
- smtp_port
  The number of the port on which the SMTP server is currently listening. The default is 25. This corresponds to the port field in the SMTP/POP3 login window.
- suppress_dialogs
  When this parameter is set to true, the Connect window does not appear after failed attempts to connect to the mail server. Instead, an error is generated.

Remarks

The SQL Remote (dbremote) Message Agent saves message system parameters when the user enters them in the message system window when the message system is first used. In this case, it is not necessary to use this statement explicitly. This statement is most useful when preparing a consolidated database for extracting many databases.

The option names are case sensitive. The case sensitivity of option values depends on the option: Boolean values are case insensitive, while the case sensitivity of passwords, directory names, and other strings depend on the case sensitivity of the file system (for directory names), or the database (for user IDs and passwords).

Privileges

Publishers can set their own options. Otherwise, you must have the SYS_REPLICATION_ADMIN_ROLE system role.

Side effects

Automatic commit.

Standards

ANSI/ISO SQL Standard
Not in the standard.

Example

The following statement sets the FTP host to ftp.mycompany.com for the FTP link for user Sam_Singer:

SET REMOTE FTP OPTION Sam_Singer.host = 'ftp.mycompany.com';

The following statement stops SQL Remote from using the specified file extensions for messages that are generated:

SET REMOTE FTP OPTION "PUBLIC"."invalid_extensions"='exe,pif,dll,bat,cmd,vbs';

The following statement sets the URL to point to the localhost for the HTTP link for user Sam_Singer:

SET REMOTE HTTP OPTION Sam_Singer.url='localhost:8033';

The following statement sets the HTTP URL to point to a Relay Server that forwards the request to the srhttp farm:

SET REMOTE HTTP OPTION PUBLIC.url='iis7.company.com:80/rs/client/rs.dll/srhttp';