sp_hook_dbmlsync_schema

sp_hook_dbmlsync_schema_upgrade

Use this stored procedure to run a SQL script that revises your schema.

Rows in #hook_dict table

Name	Value	Description
publication_n (in)	publication	Deprecated. Use subscription_n instead. The publications being synchronized, where n is an integer. There is one publication_n entry for each publication being synchronized. The numbering of n starts at zero.
MobiLink user (in)	MobiLink user name	The MobiLink user for which you are synchronizing.
script version (in)	name of script version	The script version used for the synchronization.
drop hook (out)	never \| always \| on success	The values can be: never - (the default) Do not drop the sp_hook_dbmlsync_schema_upgrade hook from the database. always - After attempting to run the hook, drop the sp_hook_dbmlsync_schema_upgrade hook from the database. on success - If the hook runs successfully, drop the sp_hook_dbmlsync_schema_upgrade hook from the database. On success is identical to always if the dbmlsync -eh option is used, or the dbmlsync extended option IgnoreHookErrors is set to true, or the IgnoreHookErrors synchronization profile option is set to on.
subscription_n (in)	subscription name(s)	The names of subscriptions being synchronized where n is an integer. This is one subscription_n entry for each subscription being synchronized. The numbering of n starts at zero.

This hook is primarily provided for backward compatibility purposes. Unless you are using the ScriptVersion extended option you can safely perform schema changes without using this hook by using the START SYNCHRONIZATION SCHEMA CHANGE statement.

When this hook is implemented, dbmlsync locks the tables being synchronized by default.

This stored procedure is intended for making schema changes to deployed remote databases. Using this hook for schema upgrades ensures that all changes on the remote database are synchronized before the schema is upgraded, which ensures that the database continues to synchronize. When this hook is being used you should not set the dbmlsync extended option LockTables to off.

During any synchronization where the upload was applied successfully and acknowledged by MobiLink, this hook is called after the sp_hook_dbmlsync_download_end hook and before the sp_hook_dbmlsync_end hook. This hook is not called during download-only synchronization or when a file-based download is being created or applied.

Actions performed in this hook are committed immediately after the hook completes. It is safe to commit or rollback in this hook.

Privileges

Hook procedures can be created by any user with the MANAGE REPLICATION system privilege. However, to ensure that the hook can access the #hook_dict table, which is used to pass information in and out of hooks, hooks must meet one of the following requirements:

Be owned by a user with the SELECT ANY TABLE and UPDATE ANY TABLE system privileges.
Be defined using the SQL SECURITY INVOKER clause of the CREATE PROCEDURE statement.

Examples

The following example uses the sp_hook_dbmlsync_schema_upgrade procedure to add a column to the Dealer table on the remote database. If the upgrade is successful the sp_hook_dbmlsync_schema_upgrade hook is dropped.

CREATE PROCEDURE sp_hook_dbmlsync_schema_upgrade()
BEGIN
 -- Upgrade the schema of the Dealer table. Add a column:
 ALTER TABLE Dealer
  ADD dealer_description VARCHAR(128);

 -- Change the script version used to synchronize
 ALTER SYNCHRONIZATION SUBSCRIPTION sub1
  SET SCRIPT VERSION='v2';


 -- If the schema upgrade is successful, drop this hook:
 UPDATE #hook_dict
  SET value = 'on success'
  WHERE name = 'drop hook';
END;

Discuss this page in DocCommentXchange.