Deadlock tracing

To trace deadlocks and report it to sql server ERRORLOG:
DBCC TRACEON (3604) -- report to log file
DBCC TRACEON (1204) -- trace deadlock

To switch it off:
DBCC TRACEOFF (3604)
DBCC TRACEOFF (1204)

To check which flags are on:
dbcc tracestatus(-1)

To cycle sql error log:
sp_cycle_errorlog

Change type of a replicated column in SQL Server

I've created stored procedure to siplify type change of the column which is being replicated.

CREATE PROCEDURE dbo.uspChangeReplColType
@tblName SYSNAME,
@colName SYSNAME,
@newType VARCHAR(20),
@repName VARCHAR(20)
AS

DECLARE
@colNameTmp SYSNAME,
@ret INT

--SET @tblName = 'tbMessages'
--SET @colName = 'To'
--SET @newType = 'VARCHAR(20)'
--SET @repName = 'SomeRepl'
SET @colNameTmp = @colName + right(cast(rand() as varchar(30)),5)
SET @ret =0

BEGIN TRAN

PRINT('1/6 Tmp column adding ...')
EXEC('alter table [' + @tblName +'] add [' + @colNameTmp + '] '+ @newType + ' NULL')
IF @@error <> 0 OR @ret <> 0 goto FAILURE
PRINT('1/6 Tmp column added')

PRINT('2/6 Tmp column populating ...')
EXEC('update [' + @tblName + '] set [' + @colNameTmp + '] = [' + @colName +']')
IF @@error <> 0 OR @ret <> 0 goto FAILURE
PRINT('2/6 Tmp column populated')

PRINT('3/6 Old column dropping ...')
EXEC @ret = dbo.sp_repldropcolumn
@source_object = @tblName, @column = @colName;
IF @@error <> 0 OR @ret <> 0 goto FAILURE
PRINT('3/6 Old column droped')

PRINT('4/6 New column adding ...')
EXEC @ret = sp_repladdcolumn @source_object = @tblName
, @column = @colName
, @typetext = @newType
, @publication_to_add = @repName
IF @@error <> 0 OR @ret <> 0 goto FAILURE
PRINT('4/6 New column added')

PRINT('5/6 New column updating ...')
EXEC('update [' + @tblName + '] set [' + @colName + '] = [' + @colNameTmp + ']')
IF @@error <> 0 OR @ret <> 0 goto FAILURE
PRINT('5/6 New column updated')

PRINT('6/6 Tmp column dropping ...')
EXEC('alter table [' + @tblName + '] drop COLUMN [' + @colNameTmp + ']')
IF @@error <> 0 OR @ret <> 0 goto FAILURE
PRINT('6/6 Tmp column droped')

COMMIT
return(0)

FAILURE:
if @@trancount = 1 ROLLBACK
return (1)
GO