FIX: You receive an Error 4005 "Timeout expired" error message when you try to clear the revision history in Content Management Server 2002 (899027)

RESOLUTION

1. Click Start, click All Programs, click Microsoft SQL Server, and then click Query Analyzer.
2. In the Connect to SQL Server dialog box, enter your login credentials, and then click OK.
3. On the toolbar, select your Content Management Server database in the Change database list.
4. In the Query window, paste the following Transact-SQL code sample.

   IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PurgeRevisionsByDate')
   	BEGIN
   		PRINT 'Dropping Procedure PurgeRevisionsByDate'
   		DROP  Procedure  dbo.PurgeRevisionsByDate
   	END
   
   GO
   
   PRINT 'Creating Procedure PurgeRevisionsByDate'
   GO
   
   create procedure dbo.PurgeRevisionsByDate
   		(
   		@RevisionDate datetime
   		)
   	with encryption
   as
   /**
   	@exception - @RevisionDate value is earlier than the last revision purge
    */
   	declare @LastPurgeDate datetime
   	declare @SQL varchar(255)
   
   	set nocount on
   
   	/*Verify whether the RevisionDate value is earlier than the last revision purge.*/
   	select @LastPurgeDate = LastRevisionPurge from Utility
   
   	if @LastPurgeDate is not null and @LastPurgeDate >= @RevisionDate 
   		return 1	/*The RevisionDate value is earlier than the last revision purge.*/
   
   	
   	create table #Revisions
   	(
   		Id int not null
   	)
   
   	insert into #Revisions
   	select ID from Node where Type != 256 AND ArchivedWhen <= @RevisionDate 
   	UNION
   	Select ID From Node where Type = 256 AND NodeGUID not in 
   	(select NodeGUID from NodeResource where NodeGUID is not null) AND ArchivedWhen <= @RevisionDate 
   	
   
   	DELETE FROM NodeProperty WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM NodeRole WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM NodeResource WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM NodePlaceholderContent WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM NodePlaceholder WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM LayoutProperty WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM NodeLayout WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM UserRoleMember WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM NodeLock WHERE NodeId in (SELECT Id FROM #Revisions)
   	DELETE FROM Node WHERE Id in (SELECT Id FROM #Revisions)
   
   
   	DECLARE @dateGMT datetime, @dateCurrent datetime
   	select @dateCurrent = GETDATE()
   	exec GetGMTTime @dateCurrent, @dateGMT out
   	
   	if @RevisionDate > @dateGMT
   		set @RevisionDate = @dateGMT
   
   	if exists (select * from Utility )
   		update Utility set LastRevisionPurge = @RevisionDate
   	else
   		insert into Utility (LastRevisionPurge) values(@RevisionDate)
   
   	return 0			
   go
   
   
   GRANT EXEC ON dbo.PurgeRevisionsByDate TO CMSSystem
   
   GO
   
   --exec PurgeRevisionsByDateDemkal  '2005-07-06 02:00:18.273' 

5. On the Query menu, click Execute.
6. On the File menu, click Exit.