How to: View and Modify Publisher and Distributor Properties (Replication Transact-SQL Programming)

Publisher and Distributor properties can be viewed programmatically using replication stored procedures.

To view Distributor and distribution database properties

1. Execute sp_helpdistributor to return information about the Distributor, distribution database, and working directory.

2. Execute sp_helpdistributiondb to return properties of a specified distribution database.

To change Distributor and distribution database properties

1. At the Distributor, execute sp_changedistributor_property to modify Distributor properties.

2. At the Distributor, execute sp_changedistributiondb to modify distribution database properties.

3. At the Distributor, execute sp_changedistributor_password to change the Distributor password.

   Security Note:
   When possible, prompt users to enter their credentials at run time. Avoid storing credentials in a script file.

4. At the Distributor, execute sp_changedistpublisher to change the properties of a Publisher using the Distributor.

Example

The following example Transact-SQL script returns information about the Distributor and distribution database.

-- View information about the Distributor, distribution database, 
-- working directory, and SQL Server Agent user account. 
USE master
EXEC sp_helpdistributor;
GO

-- View information about the specified distribution database. 
USE distribution
EXEC sp_helpdistributiondb;
GO

This example changes retention periods for the Distributor, the password used when connecting to the Distributor, and the interval at which the Distributor checks the status of various replication agents (also known as the heartbeat interval).

Security Note:
When possible, prompt users to enter security credentials at runtime. If you must store credentials in a script file, you must secure the file to prevent unauthorized access.

-- Change the heartbeat interval at the Distributor to 5 minutes. 
USE master 
exec sp_changedistributor_property 
    @property = N'heartbeat_interval', 
    @value = 5;
GO

DECLARE @distributionDB AS sysname;
SET @distributionDB = N'distribution';

-- Change the history retention period to 24 hours and the
-- maximum retention period to 48 hours.  
USE distribution
EXEC sp_changedistributiondb @distributionDB, N'history_retention', 24
EXEC sp_changedistributiondb @distributionDB, N'max_distretention', 48
GO 

-- Change the password on the Distributor. 
-- To avoid storing the password in the script file, the value is passed 
-- into SQLCMD as a scripting variable. For information about how to use 
-- scripting variables on the command line and in SQL Server Management
-- Studio, see the "Executing Replication Scripts" section in the topic
-- "Programming Replication Using System Stored Procedures".
USE master
EXEC sp_changedistributor_password $(Password)
GO

See Also

Tasks

How to: View and Modify Publisher and Distributor Properties (RMO Programming)

Concepts

Programming Replication Using System Stored Procedures

Other Resources

Distributor and Publisher Information Script

Help and Information

Getting SQL Server 2005 Assistance