Stored procedures and functions used in replication

This topic lists and briefly describes some of the most important stored procedures and functions used in replication.

BuildWhereClauseSp

This stored procedure takes an input set of column names and values and builds a WHERE clause to return to the caller. If no @TableAlias is provided, no table alias is used. All values are wrapped in quotes to simplify the logic, allowing implicit type conversions to occur. All values except the first can be NULL in the WHERE clause.

DeleteRemoteNotesSp

This stored procedure is meant to be run at a remote site. For the notes pertaining to the single row of the input @TableName specified by the @ToRowPointer or @ToWhereClause if @ToRowPointer is null, this stored procedure performs these actions:

Deletes any specific notes attached to that record
Deletes any ObjectNotes records attached to that record that point to specific notes, user notes, or system notes
Deletes any NotesSiteMap records that connect from the @FromSite to any of the specific notes deleted

This stored procedure does not remove reusable notes (user or system).

InitRemoteServerSp

This stored procedure sets up a remote procedure operation, INSERT operation, UPDATE operation, DELETE operation, or stored procedure call. The replicating flag indicates whether this stored procedure instructs the triggers to fire in the remote server.

MakeRemoteObjectNotesSp

This stored procedure is used to create an ObjectNotes record. It is called at the remote site and assumes the system, user, or specific notes records have already been created before it is called. The NotesSiteMap table records which map in which notes were last copied from a particular site, and it must also be up-to-date before this routine is called. If @ToRowPointer is null, then @ToWhereClause is used to get the rowpointer.

RemoteInfobarSaveSp

This stored procedure saves the passed-in infobar into a session variable that is retrieved by the ResetRemoteServerSp stored procedure. This stored procedure should be called by any procedures that are called remotely and that have an @Infobar output parameter.

If the Infobar contains a message, RemoteInfobarSaveSp generates an exception from the database. This is how the error message gets back to the IDO layer without it knowing in what parameter of a method call was the error message return.

RemoteMethodCallSp

This stored procedure is used to call another stored procedure at a remote site. The name of the site, the name of the stored procedure, and the parameter values must be passed.

For delayed replication, a "Success" status merely indicates that the call was successfully saved for later processing.

For transactional replication, the stored procedure makes a call directly to the remote server. The remotely called stored procedure is expected to use the RemoteInfobarSaveSp stored procedure to return any error message. If the remotely called stored procedure is not found, RemoteMethodCallSp returns an error message that indicates an improper setup of replication rules.

If the To Site is the same as the local site, this stored procedure executes the stored procedure locally. If the To Site is transactionally linked to the local site, the stored procedure is called on that server. If the To Site is not transactionally linked to the local site, data is stored in the ReplicatedRows3_mst and ShadowValues tables to save the method and parameters for later replication through XML documents.

Caution:

Multi-site processing uses a SQL Server construct to indicate what the current site is at any given time on a SQL connection. This construct (CONTEXT_INFO) is not part of transactional logic. So, if something causes processing to be skipped in the code—for example, if a constraint is thrown—then the current site information can be incorrect. Therefore, if you use try/catch blocks in any code that might change the current site (which RemoteMethodCallSp and transactional replication code do), that can leave the current site with an incorrect value. If processing returns to Mongoose, this is corrected at the next connection initialization. However, calling a remote method and catching a failure that continues processing in the same SQL connection can lead to invalid data or failure if the Site setting in the connection is incorrect.

RemoteMethodForReplicationTargetsSp

This stored procedure calls the RemoteMethodCallSp stored procedure for the input method for every target site currently defined in the replication rules.

RemoteReplSpCreateCodeSp

This function generates the SQL code that creates a remote server stored procedure for pulling data to that site from this site.

ResetRemoteServerSp

When this stored procedure runs, the session information on this (the remote server for an operation) is wiped out. It also retrieves the infobar session variable value, which might or might not have been set.

TransferNotesToSiteSp

This stored procedure allows all the notes for a particular row of a table to be copied to a remote site. The RowPointer of the local site table is used to see what notes are tied to the record locally. Additionally, a “to where” clause which will retrieve that same record at the remote system is also needed. The equivalent record at a remote site might not have the same RowPointer value as the one on the local site, which is why a WHERE clause is used.

If the ToRowPointer is known, you can use that instead of the ToWhereClause. While the local record could be retrieved using this same where clause, the RowPointer is more efficient; hence, both are required by this stored procedure. The BuildWhereClauseSp stored procedure can be used to build a WHERE clause. The @DeleteFirst flag indicates whether the existing notes for the remote record should be removed before copying the notes from the current site.

Here is an example call:

EXEC @Severity = BuildWhereClauseSp 
@WhereClause = @WhereClause OUTPUT 
, @Key1Name = 'item' 
, @Key1Value = @ItemItem 
EXEC @Severity = TransferNotesToSiteSp 
 @ToSite = @RemoteSite 
, @TableName = 'item' 
, @RowPointer = @ItemRowPointer 
, @ToWhereClause = @WhereClause 
, @ToRowPointer = NULL 
, @DeleteFirst = 1 
, @Infobar OUTPUT

If you are at the remote site and have the RowPointer for the record at both sites, you can just call TransferNotesToSiteSp from the remote site back to the original site using RemoteMethodCallSp.