Bookmark : Custom Alert Scripts for Transactional Replication

There is such a wealth of great material coming out of the SQL community now.

This from Kun Lee on replication is excellent...

Customised Alerts for Transactional Replication

replication : sp_get_distributor

Determining Replication setup using sp_get_distributor -

results on publisher
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
1	SQL_DIST01	0	0	0
results on distributor
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
1	SQL_DIST01	1	1	1
results on subscriber
installed	distribution server	distribution db installed	is distribution publisher	has remote distribution publisher
0	NULL	0	0	0

SQL 2000 ; Replication Clearup Script

Written by a colleague, this removes unnecessary constraints and columns when recovering a database previously replicated -

DECLARE @TabName varchar(255)
DECLARE @ConstName varchar(255)
DECLARE @SQL nvarchar(255)
DECLARE @Cnt as int
DECLARE @DBNAME as varchar(255)

SET @DBNAME = ''

use (databasename)

DECLARE Const_cursor CURSOR FOR



SELECT O_Table.[Name],
 O_Const.[Name]

FROM sysConstraints C

INNER JOIN sysObjects O_Table ON O_Table.[ID] = C.[ID]
INNER JOIN sysobjects O_Const ON O_Const.[ID] = C.ConstID

WHERE O_Const.[Name] LIKE 'DF__' + LEFT(O_Table.Name,9) + '__msrep__%'

ORDER BY 1,2

SET @Cnt = 0

OPEN Const_cursor

FETCH NEXT FROM Const_cursor INTO @TabName, @ConstName

WHILE @@FETCH_STATUS = 0
  BEGIN
PRINT 'Processing ' + @TabName
SET @Cnt = @Cnt + 1

--First we need to drop the constraint
SELECT @SQL = 'ALTER TABLE ' + @DBNAME + @TabName + ' DROP CONSTRAINT ' + @ConstName
exec sp_executesql @SQL
PRINT @SQL

--Now drop the unneeded column
SELECT @SQL = 'ALTER TABLE ' + @DBNAME + @TabName + ' DROP COLUMN msrepl_tran_version'
exec sp_executesql @SQL
PRINT @SQL

FETCH NEXT FROM Const_cursor INTO @TabName, @ConstName
  END

PRINT ''
PRINT cast(@Cnt as varchar(3)) + ' Tables Processed'

CLOSE Const_cursor
DEALLOCATE Const_cursor

GO