SQL Remote User's Guide
  Utilities and Options Reference

SQL Remote options

Function 

Replication options are database options included to provide control over replication behavior.

Adaptive Server Anywhere Syntax 

SET [ TEMPORARY ] OPTION
[ userid. | PUBLIC. ]option_name = [ option_value ]

Adaptive Server Enterprise syntax: 

exec sp_remote_option option-name, option-value

Parameters 

Description 

The following options are available.

These options are used by the Message Agent, and should be set for the user ID specified in the Message Agent command. They can also be set for general public use.

The options are as follows:

Blob_threshold option    Any value longer than the Blob_threshold option is replicated as a blob. That is, it is broken into pieces and replicated in chunks, before being reconstituted by using a SQL variable and concatenating the pieces at the recipient site.

If you are replicating blobs in an installation with Adaptive Server Enterprise, you must ensure that Blob_threshold is set to a value larger the largest blob being replicated.

For information on blob replication and Adaptive Server Enterprise, see Replication of blobs.

Compression option    Set the level of compression for messages. Values can be from -1 to 9, and have the following meanings:

• -1    Send messages in Version 5 format. Message Agents (both dbremote and ssremote) from previous versions of SQL Remote cannot read messages sent in Version 6 format. You should ensure that COMPRESSION is set to -1 until all Message Agents in your system are upgraded to Version 6.

• 0    No compression.

• 1 to 9    Increasing degrees of compression. Creating messages with high compression can take longer than creating messages with low compression.

Delete_old_logs option    This option is used by SQL Remote and by the Adaptive Server Anywhere Replication Agent. The default setting is OFF. When set to ON, the Message Agent (DBREMOTE) deletes each old transaction log when all the changes it contains have been sent and confirmed as received.

External_remote_options    This option is used by SQL Remote to indicate whether the message link parameters should be stored in the database (OFF) or externally (ON). By default, the setting is OFF.

Qualify_owners option    Controls whether SQL statements being replicated by SQL Remote should use qualified object names. The default in Adaptive Server Anywhere is ON and the default in Adaptive Server Enterprise is OFF.

Qualifying owners in Adaptive Server Enterprise setups is rarely needed because it is common for objects to be owned by dbo. When qualification is not needed in Adaptive Server Anywhere setups, messages will be slightly smaller with the option off.

Quote_all_identifiers option    Controls whether SQL statements being replicated by SQL Remote should use quoted identifiers. The default is OFF.

When this option is off, the dbremote quotes identifiers that require quotes by Adaptive Server Anywhere (as it has always done) and ssremote does not quote any identifiers. When the option is on, all identifiers are quoted.

Replication_error option     Specifies a stored procedure called by the Message Agent when a SQL error occurs. By default no procedure is called.

The replication error procedure must have a single argument of type CHAR, VARCHAR, or LONG VARCHAR. The procedure may be called once with the SQL error message and once with the SQL statement that causes the error.

While the option allows you to track and monitor SQL errors in replication, you must still design them out of your setup: this option is not intended to resolve such errors.

You can use a table with DEFAULT CURRENT REMOTE USER to record the remote site that caused the error.

Save_remote_passwords option    When a password is entered into the message link dialog box on first connection, the parameter values are saved. By default, Save_remote_passwords is ON and the password is saved. If you are storing the message link parameters externally, rather than in the database, you may wish not to save the passwords. You can prevent the passwords from being saved by setting this option to NO.

SR_Date_Format option    The Message Agent uses this option when replicating columns that store a date. The option is a string build from the following symbols:

Each symbol is substituted with the date being replicated.

If you set the mm format symbol in upper case, the corresponding characters are also upper case.

For the digit formats, the case of the option setting controls padding. If the symbols are the same case (such as DD), the number is padded with zeroes. If the symbols are mixed case (such as Mm), the number is not zero padded.

SR_Time_Format option    The Message Agent uses this option when replicating columns that store a time. The option is a string build from the following symbols:

Using mixed case in the formatting string suppresses leading zeroes.

SR_Timestamp_Format    The Message Agent replicates datetime information using this option. For Adaptive Server Anywhere this is the timestamp, datetime, and smalldatetime data types. For Adaptive Server Enterprise, this is the datetime and smalldatetime data types.

The format strings are taken from the SR_Date_Format and SR_Time_Format settings.

The default setting is the SR_Date_Format setting, followed by the SR_Time_Format setting.

Subscribe_by_remote option     When set to ON, operations from remote databases on rows with a subscribe by value that is NULL or an empty string assume the remote user is subscribed to the row. When set to OFF, the remote user is assumed not to be subscribed to the row.

The only limitation of this option is that it will lead to errors if a remote user really does want to INSERT (or UPDATE) a row with a NULL or empty subscription expression (for information held only at the consolidated database). This is reasonably obscure and can be worked around by assigning a subscription value in your installation that belongs to no remote user.

For more information about this option, see Using the Subscribe_by_remote option with many-to-many relationships, and Using the Subscribe_by_remote option with many-to-many relationships.

Verify_threshold option    If the data type of a column is longer than the threshold, old values for the column are not verified when an UPDATE is replicated. The default setting is 1000.

This option keeps the size of SQL Remote messages down, but has the disadvantage that conflicting updates of long values are not detected.

Verify_all_columns option    The default setting is OFF. When set to ON, messages containing updates published by the local database are sent with all column values included, and a conflict in any column triggers a RESOLVE UPDATE trigger at the subscriber database.

The extraction utility for Adaptive Server Enterprise sets the public option in remote Adaptive Server Anywhere databases to match the setting in the Adaptive Server Enterprise database.

Examples 

• The following statement sets the Verify_all_columns option to OFF in Adaptive Server Anywhere, for all users:

  SET OPTION PUBLIC.Verify_all_columns = 'OFF'

• The following statements set the Verify_all_columns option to OFF in Adaptive Server Enterprise:

  exec sp_remote_option Verify_all_columns, 'OFF'
  go

  In Adaptive Server Enterprise, replication options are used only by SQL Remote.