Data Replication with SQL Remote
PART 3. SQL Remote Administration
CHAPTER 12. Administering SQL Remote for Adaptive Server Enterprise
The publisher of the consolidated database can directly intervene at remote sites using a passthrough mode, which enables standard SQL statements to be passed through to a remote site.
Passthrough destinations are determined by sp_passthrough_user and sp_passthrough_subscription. Executing either of these procedures determines a set of recipients for any subsequent passthrough statements.
Executing either sp_passthrough_user and sp_passthrough_subscription adds to the current list of recipients. The sp_passthrough_stop procedure resets passthrough (that is, resets the list of recipients to be empty).
In Adaptive Server Enterprise, sp_passthrough never executes statements in the Adaptive Server Enterprise server.
To cause passthrough SQL statements to replicate, you call sp_passthrough.
Due to the VARCHAR(255) limitation in Adaptive Server Enterprise, you should build a long SQL statement up in pieces. Calls to sp_passthrough_piece will build up a single SQL statement. Calling sp_passthrough with the last piece will cause the built up statement to replicate.
You should always test your passthrough operations on a test database with a remote database subscribed. You should never run untested passthrough scripts against a production database.
You should always qualify object names with the owner name. PASSTHROUGH statements are not executed at remote databases from the same user ID. Consequently, object names without the owner name qualifier may not be resolved correctly.
Schema modificationsThe Adaptive Server Enterprise log transfer interface does not contain information about the number of columns and data types of the columns in a table. SSREMOTE gets this information directly from the Adaptive Server Enterprise system tables. For this reason, altering a table and then scanning operations that happened before the ALTER TABLE will lead to errors. SSREMOTE must set the "truncation point" beyond all operations on replicated tables before schema changes can be made. Operations on replicated tables need to be prevented between SSREMOTE running and the schema changes being made.