Replication Programming

Transact-SQL Custom Stored Procedure Resolver

The program in the C:\Microsoft SQL Server\80\Tools\Devtools\Samples\sqlrepl\deflt_sp directory is a sample application, which builds a custom stored procedure resolver that executes at the Publisher. The resolver uses the Northwind sample database, a distributed query to obtain information from the Subscriber, and then computes the average price if the values between the Publisher and Subscriber are different. The resolver then makes the average price the resolved value, and logs the conflict at the Publisher so it can be viewed, and if necessary, changed. Finally, the application sends an e-mail stating that the price was changed to an average due to a conflict. If any columns other than price have changed, the resolver uses the values from the Publisher.

The resolver is designed to run on the Products table in Northwind sample database. It is assumed that the sample code was saved to the default directory offered during installation, and that the files can be found in C:\Microsoft SQL Server\80\Tools\Devtools\Samples\sqlrepl\deflt_sp.

Note  To run this sample, two computers are required. For the distributed query to work, the two computers must both be running the Microsoft Windows NT 4.0 or Windows 2000 operating system, or the Publisher must be running on Windows 2000 with the linked server running on Windows NT 4.0.

To run the sample program

  1. On the computer that will be the Publisher, verify that the SQL Server Agent is running. If it is not running, start it.

  2. Using the Create Publication Wizard, create a merge publication based on the Northwind database, and then select the Products table as an article in the publication.

How to create publications and define articles

Enterprise Manager

Enterprise Manager
  1. On the Publisher, in SQL Query Analyzer, open \Samples\sqlrepl\deflt_sp\avgprice.sql. The script contains parameters that are required in a conflict resolver. For more information about the use of each field defined, see Custom Stored Procedure Conflict Resolver. In the script, modify the sendmail @recipients variable and uncomment all the comment blocks for the e-mail if the stored procedure is to send e-mail when executed.

  2. In SQL Query Analyzer, on the Query menu, click Execute to run the script and create a new stored procedure named sp_avgprice in the Northwind database. To view the results of the execution, in the Northwind database, click Stored Procedures in the left pane, and then double-click sp_avgprice in the right pane.

  3. Using SQL Server Enterprise Manager, set the publication to use the custom resolver sp_avgprice. Expand the Replication folder, expand the Publications folder, right-click the publication name in the left pane, and then select Properties.

  4. On the Articles tab, select the article properties button (...) for the Products table article. In the Table Article Properties dialog box, click the Resolver tab, and then click Use this custom resolver (registered at the Distributor). In the list box, click Microsoft SQL Server stored procedure resolver, and in Enter information needed by the resolver , enter sp_avgprice. You will receive a warning message that the Products table has references to other tables; close this message.

  5. Set up a linked server for the Subscriber and enable the server to allow distributed query access. To set up the linked server, use SQL Server Enterprise Manager at the Publisher. Expand the appropriate SQL Server group, expand the Publisher, and then expand Security folder. Right-click Linked Servers, and then click New Linked Server. On the General tab, type the name of the computer that will be the Subscriber, and then select the correct server type.

  6. In the Linked Server Properties dialog box, on the Security tab, select Be made using the login's current security context.

    -or-

  7. If there is a remote userID and password that the user at the Publisher can use to access the linked server, in the Local Login field, enter the Publisher userID, and then in the Remote User and Remote Password fields, enter the remote userID and password.

  8. Copy and paste the following Transact-SQL into SQL Query Analyzer, replace the subserver variable with the name of the Subscriber and execute it. The Linked Server Properties dialog box created the linked server with the name in uppercase letters, so enter the value for subserver in uppercase letters. 
    EXEC sp_serveroption 'SUBSERVER', 'data access', 'true'
  9. Ensure that the servers are linked and that a distributed query works. One way to test the connection is to copy the following Transact-SQL statement into SQL Query Analyzer at the Publisher. If this distributed query does not work, the sample resolver will not work. Replace the subserver variable with the name of the Subscriber.

    SELECT * FROM OPENQUERY ([SUBSERVER], 'SELECT * from Northwind.dbo.Products')

    This SELECT statement should return all columns in the Products table from the linked subserver.

  10. The custom stored procedure can send e-mail. Optionally, execute the following Transact-SQL statement in SQL Query Analyzer to start the mail service. If you do not want the e-mail to be sent or do not currently have an e-mail server installed, this step can be skipped. 
    EXEC master..xp_startmail
  11. Set up a subscription between the Publisher and the linked server. On the Publisher, expand the Replication folder, expand the Publications folder, and then on the Northwind publication, right-click and select Push New Subscription. The Subscriber will be the linked server, the subscription database will be Northwind, and the Merge Agent should update continuously. On the Initialize Subscription page, select No. The Subscriber already has the schema and data because the Northwind database and data already exists at the linked server.

    Note  If the linked server name does not appear in the list of possible Subscribers, exit the Push Subscription Wizard, right-click the Replication folder, and then select Configure Publishing, Subscribers, and Distribution. On the Publisher and Distributor Properties dialog box, on the Subscribers tab, select New, and then enter the requested data. After closing Publisher and Distributor Properties, and then start the Push Subscription Wizard again.

  12. To see if a price change has occurred, in SQL Query Analyzer on the Publisher in the Northwind database, run the following Transact-SQL statement: 
    UPDATE Products
SET UnitPrice = 18.95
WHERE ProductName = 'Chang'
  13. In SQL Server Enterprise Manager, expand Replication Monitor, expand the Agents folder, and then click the Merge Agents folder. In the right pane, right-click the agent for the publication, and then select Start Synchronizing.

For each product in the Products table at the Subscriber, with a price equivalent to the price in the Products table at the Publisher, there will be no change in price. If the prices are different, which will be the case for the 'Chang' product, the price will become the average of the two prices. An e-mail will be sent for notification that the price has changed, if e-mail was enabled, and the Conflict Viewer will show the log of the conflict.