Programming the SQL Merge ActiveX Control
The SQL Merge control is implemented as a Microsoft® ActiveX® in-process component. It provides a way to synchronize data in merge subscriptions. It is not used in snapshot replication or transactional replication. Its primary class, the SQLMerge object, provides the functionality of the Merge Agent and supports synchronization of push, pull, or anonymous subscriptions to a merge publication. It also includes the options to:
- Add, drop, copy, and register subscriptions for synchronization using Windows Synchronization Manager. For more information, see Common SQL Distribution Control and SQL Merge Control Functionality.
- Apply the initial snapshots to the Subscriber.
- Merge incremental changes that occurred at the Publisher or Subscribers after the initial snapshot was created.
- Reconcile conflicts according to the rules configured, through a COM component custom conflict resolver, or interactively by setting the UseInteractiveResolver property.
- Specify the direction of the synchronization so that it executes only the upload phase, the download phase, or both the upload and download phases.
- Specify an alternate snapshot folder from which the snapshot for a subscription can be applied.
- Specify a client-side working folder to which snapshot files can be copied using FTP.
- Copy a subscription database by generating a specially formatted subscription file in a specified location. These files can be attached to create a synchronized subscription, registered at the Publisher as part of an attachable subscription database.
- Synchronize with a Publisher other than the one at which its subscription originated. This alternate synchronization partner must contain the same schema and data set as the original Publisher.
- Attach a subscription database by specifying Subscriber-side properties only. The Publisher, PublisherDatabase, Distributor, and Publication properties do not need to be set while adding attachable subscription databases.
Instantiating the SQL Merge Control
The SQL Distribution and SQL Merge controls can be used to create a subscription database and a pull subscription, as well as synchronize with the publication data.
This code segment demonstrates how a Microsoft Visual Basic® program can configure the SQLMerge object to create a Subscriber database and subscription using the AddSubscription method, and then synchronizes data with the SQL Server Publisher.
In the example, Microsoft SQL Server™ 2000 generates the subscription name. Replace the fields in italic with appropriate values.
'SQLMerge control declaration.
Private mobjMerge As SQLMERGXLib.SQLMerge
Set mobjMerge = New SQLMERGXLib.SQLMerge
With mobjMerge
'Set up the Publisher.
.Publisher = "PublisherServer"
.PublisherDatabase = "PublisherDatabase"
.Publication = "PublicationName"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Distributor.
.Distributor = "DistributorServer"
.DistributorSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "SubscriberServer"
.SubscriberDatabase = "SubscriberDatabase"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = DB_AUTHENTICATION
.SubscriberLogin = "SubscriberUserID"
.SubscriberPassword = "SubscriberPassword"
'Set up the subscription.
.SubscriptionType = PULL
.SynchronizationType = AUTOMATIC
'Create the database and subscription.
.AddSubscription CREATE_DATABASE, NONE
'Synchronize the subscription.
.Initialize
.Run
.Terminate
End With
Note Subscriptions other than anonymous subscriptions that are added using the SQLDistribution or SQLMerge objects must be added by explicitly calling the AddSubscription method before attempting to initialize and synchronize the subscription for the first time.
Both SQLDistribution and SQLMerge objects can be used to create a subscription database and a pull subscription, as well as synchronize with the publication data. When using a transactional publication, the previous sample code can be used for creating a Subscriber database and pull subscriptions with the SQLDistribution control. Replace references to the SQLMerge object with references to the SQLDistribution object. For more information about the merge object, see SQLMerge Object.
Creating Anonymous Internet Subscriptions Sample
ActiveX replication controls can be programmed to synchronize data over the Internet. After the Publisher and Distributor are configured for publishing over the Internet and a publication enabled for anonymous subscriptions is created, an application using an ActiveX replication control can synchronize with the publication data. FTP is used for the snapshot download, both during the initial application and when the subscription is re-initialized. All other synchronizations use SQL packets over TCP/IP to transfer data between Publisher and Subscriber.
This example demonstrates how a Visual Basic program configures the SQL Merge Control to synchronize data for an anonymous subscription over the Internet. The Distributor and Publisher are reached using a Uniform Resource Locator (URL).
The SubscriberDatabase must already exist when this code is executed. Because the FTP information has not been provided, the specification of FILETRANSFERFTP for the FileTransferType property causes the control to request the FTP information from the Distributor.
A handler for the Status event is included. It displays the most recent status message in a label. Replace the fields in italic with appropriate values.
'SQLMerge control declaration.
Private WithEvents mobjMerge As SQLMERGXLib.SQLMerge
. . .
Private Sub RunReplMerge()
'Create SQLMerge control.
Set mobjMerge = New SQLMERGXLib.SQLMerge
With mobjMerge
'Set up the Publisher.
.Publisher = "PublisherServer"
.PublisherAddress = "publisher.company.com"
.PublisherNetwork = TCPIP_SOCKETS
.PublisherDatabase = "PublisherDatabase"
.Publication = "PublicationName"
.PublisherSecurityMode = DB_AUTHENTICATION
.PublisherLogin = "PublisherUserID"
.PublisherPassword = "PublisherPassword"
'Set up FTP.
.FileTransferType = FILETRANSFERFTP
'Set up the Distributor.
.Distributor = "DistributorServer"
.DistributorAddress = "distributor.company.com"
.DistributorNetwork = TCPIP_SOCKETS
.DistributorSecurityMode = DB_AUTHENTICATION
.DistributorLogin = "DistributorUserID"
.DistributorPassword = "DistributorPassword"
'Set up the Subscriber.
.Subscriber = "SubscriberServer"
.SubscriberDatabase = "SubscriberDatabase"
.SubscriberDatasourceType = SQL_SERVER
.SubscriberSecurityMode = NT_AUTHENTICATION
'Set up the subscription.
.SubscriptionType = ANONYMOUS
.SynchronizationType = AUTOMATIC
'Synchronize the Subscriber.
.Initialize
.Run
.Terminate
End With
Exit Sub
End Sub
Private Function mobjMerge_Status(ByVal Message As String, ByVal Percent As Long) _
As SQLMERGXLib.STATUS_RETURN_CODE
'Display most recent status message.
Label1 = Message
DoEvents
End Function
Note The URLs publisher.company.com and distributor.company.com need to be resolvable by an external DNS server. If a listening port other than the default port 1433 is used, it must be explicitly coded. For example, if port 1430 is to be used:
.PublisherAddress = "publisher.company.com,1430"
When using a transactional publication, the same example can be used for synchronizing a Subscriber database with the SQL Distribution control. Replace references to the SQLMerge object with references to the SQLDistribution object.
Creating and Synchronization Subscriptions to a Jet 4.0 Database
ActiveX replication controls can be programmed to synchronize data with a Microsoft Jet 4.0 database. This enables applications to synchronize subscriptions to Jet 4.0 databases without having to create an OLE DB data source. The publication must be configured to accept heterogeneous data source Subscribers. The Subscriber does not need to be configured as a linked server.
This code segment demonstrates how a Visual Basic program configures the SQL Merge control to create a Jet 4.0 database and synchronizes data with it. The AddSubscription method need not be used if the database already exists.
Note If the publication has not been enabled for heterogeneous Subscribers, the Jet database will be created and the Publisher schema applied, but no data will be copied. No error message or warning is given.
'SQLMerge control declaration.
Private WithEvents mobjMerge As SQLMERGXLib.SQLMerge
. . .
'Configure the control for a Jet 4.0 database subscription.
Set mobjMerge = New SQLMERGXLib.SQLMerge
With mobjMerge
'Set up the Publisher.
.Publisher = "PublisherServer"
.PublisherDatabase = "PublisherDatabase"
.Publication = "PublicationName"
.PublisherSecurityMode = NT_AUTHENTICATION
'Set up the Subscriber.
.Subscriber = "SubscriberServer"
' MDBFileSpecification would be something like C:\ReplDBs\JetPubs.mdb
.SubscriberDatabasePath = "MDBFileSpecification"
.SubscriberDatasourceType = JET4_DATABASE
.SubscriberSecurityMode = DB_AUTHENTICATION
' JetDatabaseUserID would be something like "Admin"
.SubscriberLogin = "JetDatabaseUserID"
.SubscriberPassword = "JetDatabasePassword"
'Set up the subscription.
.SubscriptionType = ANONYMOUS
.SynchronizationType = AUTOMATIC
'Synchronize the subscription.
.Initialize
.Run
.Terminate
End With
When using a transactional publication, the same example can be used for synchronizing a Subscriber database with the SQL Distribution control. Replace references to the SQLMerge object with references to the SQLDistribution object.
Providing Status and Handling Cancel Requests
ActiveX replication controls provide a Status event that provides status messages and percent complete during Initialize, Run, and other replication control methods. These messages can be displayed in the user interface of the application (for example, a label and a progress bar). The event also supports the ability to cancel the control process.
Assume the application hosts a SQL Merge control, and its visible user interface includes these controls.
| Control Type | Control Name |
|---|---|
| Label | lblStatus |
| Progress Bar | prgStatus |
| Command Button | cmdCancel |
The Status event handler updates lblStatus and prgStatus with the status information. When cmdCancel is clicked, a Boolean variable is set. The handler returns a CANCEL notification when it finds the variable set. The DoEvents call should be included to update the visible controls.
If you use a mechanism similar to this, be sure to inhibit subsequent calls to control methods after the cancel request is received.
This code segment demonstrates how a Visual Basic program displays status information and handles a cancel request. Note that the Status callback might be called again after being cancelled. Some operations cannot be cancelled immediately.
Private WithEvents mobjMerge As SQLMERGXLib.SQLMerge
Private mblnCancel As Boolean
. . .
Private Sub cmdCancel_Click()
'Set flag when the Cancel button is clicked.
mblnCancel = True
End Sub
Private Function mobjMerge_Status(ByVal Message As String, _
ByVal Percent As Long) As SQLMERGXLib.STATUS_RETURN_CODE
'Display progress and status message.
lblStatus.Caption = Message
prgStatus.Value = Percent
'Cancel if the button was clicked.
If mblnCancel Then mobjMerge_Status = CANCEL
'Allow screen to update.
DoEvents
End Function
Using Error Handling Sample
ActiveX replication controls provide detailed information about method failures through these mechanisms:
- Returning an error code to the caller of the method. In Microsoft Visual Basic, this is done by raising a runtime error that you can trap in an error handler. In Microsoft Visual C++®, the error code is an HRESULT function return value.
- Referencing a SQLReplErrors collection. Each control has an ErrorRecords property that references a SQLReplErrors collection. To retrieve error information, you can enumerate through each SQLReplError object in the collection.
Usually, any error that is returned to the caller is added to the collection. Collection elements that have an error code equal to 0 are supplemental information such as a stored procedure reference with actual arguments substituted, which applies to the previous collection element. The collection is cleared at each new call to a replication control method to ensure that all elements apply to the most recent call.
This example demonstrates how a Visual Basic program reports detailed error information that might be generated by an ActiveX replication control. Most of the code to set up the control is omitted. The error handler displays the error code and description for the raised error and for each error in the errors collection. When the raised error description is the same as the description of one of the collection elements, it is not duplicated in the display. Both the raised error codes and the duplicate collection element error code are displayed.
Private WithEvents mobjMerge As SQLMERGXLib.SQLMerge
Private mobjReplErr As REPLERRXLib.SQLReplError
. . .
Private Sub RunReplMerge()
Dim strPhase As String 'setup/initialize/run/terminate
On Error GoTo ErrorHandler
Set mobjMerge = New SQLMERGXLib.SQLMerge
With mobjMerge
'Set up the SQL Merge control.
strPhase = "Setup"
.Publisher = "PublisherServer"
.PublisherDatabase = "PublisherDatabase"
.PublisherSecurityMode = NT_AUTHENTICATION
.SubscriberSecurityMode = NT_AUTHENTICATION
'<Remainder of properties set here.>
'Synchronize the subscription.
strPhase = "Initialize"
.Initialize
strPhase = "Run"
.Run
strPhase = "Terminate"
.Terminate
End With
Exit Sub
ErrorHandler:
Dim blnMsgDupl As Boolean 'True: duplicate found in collection
Dim strErrMsg As String 'Message buffer
'Iterate through errors collection.
For Each mobjReplErr In mobjMerge.ErrorRecords
'The raised error is the same as the collection element; add error code.
If Err.Description = mobjReplErr.Description Then
strErrMsg = strErrMsg & vbCrLf & vbCrLf & _
mobjReplErr.Description & vbCrLf & _
"Error " & strErrorNumConv(Err.Number)
blnMsgDupl = True
'The raised error is not the same as the collection element; add 'description.
Else
strErrMsg = strErrMsg & vbCrLf & vbCrLf & _
mobjReplErr.Description
End If
'Append error code from collection.
strErrMsg = strErrMsg & vbCrLf & "Coll. " & _
strErrorNumConv(mobjReplErr.ErrorNumber)
Next mobjReplErr
'Format message, include raised error if duplicate not found.
strErrMsg = "Error during Merge control " & _
strPhase & " phase:" & _
IIf(blnMsgDupl, "", _
vbCrLf & vbCrLf & Err.Description & vbCrLf & _
"Error " & strErrorNumConv(Err.Number)) & _
strErrMsg
MsgBox strErrMsg, vbExclamation
End Sub
Private Function strErrorNumConv(ByVal lngErrNum As Long) As String
'Convert error number into readable forms,
' hex, and decimal for the low-order word.
Dim strErrNums As String
If lngErrNum < 16 And lngErrNum > -16 Then
strErrNums = CStr(lngErrNum)
ElseIf lngErrNum < 65536 And lngErrNum > -65536 Then
strErrNums = "x" & Hex(lngErrNum) & _
" = " & CStr(lngErrNum)
Else
strErrNums = "x" & Hex(lngErrNum) & _
" = x" & Hex(lngErrNum And -65536) & _
" + " & CStr(lngErrNum And 65535)
End If
strErrorNumConv = "Code: " & strErrNums
End Function
If this example is run as shown, with part of the control setup missing, the following error is displayed.
Error during Merge control Initialize phase:
'The property 'Publication' must be set before initializing the SQL Merge 'ActiveX Control.
Error Code: x80004005 = x80000000 + 16389
Coll. Code: x7918 = 31000
'The property 'Subscriber' must be set before initializing the SQL Merge 'ActiveX Control.
Coll. Code: x7918 = 31000