Home > Articles > Data > SQL Server

  • Print
  • + Share This
Like this article? We recommend

Setup

Setting up replication of views, UDFs, and stored procedures is very similar to setting up transactional replication for table articles. Please refer to my earlier articles for a detailed overview of transactional replication setup.

On the Specify Articles tab of the Create Publication Wizard you can select views, stored procedures, or UDFs instead of tables, as shown in the following figure.

Figure 1Figure 1

Note that I added a stored procedure populate_discounts and a UDF called udf_check_business_day to the pubs database specifically for this article. If you want to follow along with the examples shown here, run the following script in your pubs sample database:

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'p' AND name = 'populate_discounts')
BEGIN 
    DROP PROCEDURE populate_discounts
END
GO
CREATE PROC populate_discounts ( 
    @discounttype VARCHAR(45), 
    @stor_id INT, 
    @lowqty INT, 
    @highqty INT, 
    @discount INT) 
AS 
SET NOCOUNT ON 
BEGIN TRAN 
INSERT discounts ( 
    discounttype, 
    stor_id, 
    lowqty, 
    highqty, 
    discount) 
SELECT 
    @discounttype, 
    @stor_id , 
    @lowqty , 
    @highqty , 
    @discount 
IF @@ERROR <> 0 
    BEGIN 
        RAISERROR('did not work, please try again', 16, 1) 
        ROLLBACK 
        RETURN 
    END 
UPDATE sales 
SET payterms = 'Net 120' 
WHERE stor_id = @stor_id 
IF @@ERROR <> 0 
    BEGIN 
        RAISERROR('did not work, please try again', 16, 1) 
        ROLLBACK 
        RETURN 
    END 
COMMIT TRAN 
GO


IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'fn'
AND name = 'udf_check_business_day')
BEGIN
    DROP FUNCTION udf_check_business_day
END
GO

CREATE FUNCTION udf_check_business_day (@ord_num VARCHAR(15)) 
RETURNS BIT 
AS 
BEGIN 
DECLARE @business_day INT 
SELECT @business_day = CASE WHEN DATEPART(WEEKDAY, ord_date) NOT IN (1, 7) THEN 1 ELSE 0 END 
FROM sales WHERE ord_num = @ord_num 
RETURN @business_day 
END

Each type of code module has different replication options that you can choose during setup. All objects allow you to include extended properties along with the article's schema. If you're not familiar with extended properties, they're a neat way to track application-specific metadata within the database. When replicating views, you can also replicate triggers defined on the views. Note that although triggers are essentially a special case of stored procedures, you cannot explicitly replicate the execution or schema of the triggers. Stored procedures allow you to replicate each execution of the procedure—or only those executions included within a serializable transaction. All these replication options are configured using the Other tab of the articles. The following figure shows the Other tab of Stored Procedure Article Properties.

Figure 2Figure 2

When you publish code modules, the Create Publication Wizard warns you that your application might require changes in order to work as expected. To create views, UDFs, and stored procedures on the subscriber, you must first have all tables, other views, or UDFs referenced by these code modules. After you ensure that all necessary objects exist on the subscriber(s), you can complete the wizard.

Setting up the subscription for publications, including nontable articles, is identical to that for subscribing to table publications.

NOTE

Please refer to my InformIT article called "Setting Up Transactional Replication with SQL Server" for details.

Although you could replicate the execution of procedures that only read data, you should replicate only the execution of your stored procedures that modify data. If a procedure doesn't change any data, it really makes no sense to replicate its execution unless you want to duplicate the load of the production server on a non-production computer to undertake some sort of performance testing. If so, you should consider using the replay functionality available with SQL Profiler instead of replicating stored procedures.

  • + Share This
  • 🔖 Save To Your Account