Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Michael 125 posts 409 karma points
    May 20, 2015 @ 13:39
    Michael
    1

    Contour db cleanup

    I need to clean all records from contour db which older than some date (2 month).

     

    Who can help me to re-factor this

     

    IF (OBJECT_ID('ContourRemoveRecords') IS NOT NULL)

      DROP PROCEDURE ContourRemoveRecords

    GO

    Create PROCEDURE [ContourRemoveRecords] 

        @date datetime = NULL

    AS

    BEGIN

           create table #RecordsFormsGuids (

        RecordId nvarchar(36)

    )

    create table #RecordsKeys (

        RecordId nvarchar(36)

    )

    INSERT INTO #RecordsFormsGuids

    Select Id  FROM [dbo].[UFRecords] where Created < Convert(datetime, @date)

     

    DELETE FROM  [dbo].[UFRecords] where Created < Convert(datetime, @date)

     

    DELETE FROM [dbo].[UFRecordsXml] where Created < Convert(datetime, @date)

     

    Insert INTo #RecordsKeys

    SELECT [Key]

    FROM [dbo].[UFRecordFields] Where Record in

    (

    Select * from #RecordsFormsGuids

    )

     

    DELETE FROM [dbo].[UFRecordFields] Where Record in

    (

    Select * from #RecordsFormsGuids

    )

     

    DELETE

      FROM [dbo].[UFRecordDataString] Where [Key] in 

      (

    Select * from #RecordsKeys

      )

     

    Drop Table #RecordsFormsGuids

     

    Drop table #RecordsKeys

    END

  • Chris Wilson 100 posts 377 karma points
    May 20, 2015 @ 15:47
    Chris Wilson
    0

    It would be much cleaner if you could perform this through the Contour library singleton or a RecordStore instance, that way the library code will clean all linked records for you.

    Do this overnight of course :)

  • Michael 125 posts 409 karma points
    May 21, 2015 @ 09:57
    Michael
    0

    :-)

Please Sign in or register to post replies

Write your reply to:

Draft