Like this article? We recommend
Like this article? We recommend
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
1Note 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
2When 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.