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

Symptoms

In Microsoft Content Management Server 2002, when you try to clear the revision history in Site Manager, the operation does not succeed. When this problem occurs, you receive the following error message:
Error: 4005

Description: The XML parser could not parse the tag it was given.

Severity: 5

Source: P:\NR\Shared\GrammarParserC\AEGrammarParser.cpp 313 Debug info: Extended error: [What='Offending SQL: {? = call PurgeRevisionsByDate(?)}; {call UpdateChangeTable };Timeout expired. '] [LCID='1024'] [SourceFile=''] [SourceFileLine='-1'] [DebugInfo='

Extra info:

Client Source: OEFFolders::timerPostMessage_Timer:PurgeRevisions

Recommended Action: The ODBC data source may be incorrectly configured, the database may not be running, or there may be incorrect data in the database.

Cause

When you click Clear Revision History on the Tools menu in Site Manager, the PurgeRevisionsByDate stored procedure is not completed, and a time-out condition occurs.

Resolution

To resolve this problem, you must run a Transact-SQL script to replace the PurgeRevisionsByDate stored procedure with a new PurgeRevisionsByDate stored procedure. To do this, follow these steps:
  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.

Status

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
Properties

Article ID: 899027 - Last Review: Jun 1, 2006 - Revision: 1

Feedback