Delete SharePoint Versions on list item due to workflow in loop

Introduction

Workflows in SharePoint to say the least can be extremely tricky beyond the basic alert me or 3state workflows. Once you get into designer or visual studio workflows, at times you are better off wrapping your head around an axel of a car. With that said how about a little demystified action on workflows and SQL tables.

Oh, the reason I had the pleasure of learning this madness is because one of my staff members created some workflow loops some lasted like 2 hours until the version limit of 9999 versions for an item was hit and the SharePoint workflow said, that’s all I can do and quite. Detail, details, details, always pay attention to details when working with workflows.

Using what’s there

So to save myself a bunch of pain, I just ran profiler and capture the stored procedure which runs the delete version, in addition,  once I found the stored procedure I looked up of course, I wanted to know what the parameters where supposed to do. I go lucky and found a MSDN site which breaks down all the stored procedures SharePoint uses, man what a gold mine.  Here is the link.

http://msdn.microsoft.com/en-us/library/dd358229(PROT.13).aspx

I then created a cursor set and placed a select statement which grabbed the parameters which the sp needs to remove versions. Made sure the deleted items did not go to the recycle bin (less records, didn’t want to just move them), then ran the proc.

Walla, I could get to my records, the users where happy again, and I made some changes to control of workflow management.

— =============================================

— Declare and using a READ_ONLY cursor

— =============================================

Declare @ItemID as int, @ItemVersion as int, @DeleteVersion as int

SET @ItemID = 61

DECLARE MyCursor CURSOR

READ_ONLY

FOR

–The Following list items except the first 17 versions (most common legit ones)

SELECT TOP 45000

tp_UIVersion as DeleteVersion

FROM dbo.AllUserData

WHERE tp_id = @ItemID

AND tp_ListID = ‘C335EB4E-2EFE-4A1E-863E-E8DA16A42909’

AND tp_deleteTransactionID = 0x

AND tp_IsCurrent = 0

AND NOT tp_UIVersionString IN (‘1.0′,’2.0′,’3.0′,’4.0′,’5.0′,’6.0′,’7.0′,’8.0′,’9.0′,’10.0′,’11.0′,’12.0′,’13.0′,’14.0′,’15.0′,’16.0′,’17.0’)

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @DeleteVersion

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

—          PRINT ‘add user defined code here’

—          eg.

DECLARE @message varchar(100)

SELECT @message = ‘Version To Delete is: ‘ + CAST(@DeleteVersion as varchar(255))

PRINT @message

exec proc_DeleteItemVersion

‘31357C52-7B34-47BF-B2DB-7ED4F4EA7620′  –@SiteId      uniqueidentifier,

,’8BAB5131-65F0-428B-B410-794B193088B4′ –@WebId        uniqueidentifier,

,’C335EB4E-2EFE-4A1E-863E-E8DA16A42909’ –@ListId      uniqueidentifier,

,@ItemID          –@ItemId      int,

,@DeleteVersion   –@ItemVersion      int,

,1                      — @UserId int,

,1                      –UseNvarchar1ItemName  bit = 1,

,3                      –@DeleteOp      int = 3 no recyclebin

END

FETCH NEXT FROM MyCursor INTO @DeleteVersion

END

CLOSE MyCursor

DEALLOCATE MyCursor

GO

Summary, SharePoint is a love hate, with power comes responsibility type application which can be a solution or a problem. Governance is key and really exposes a lack of governance if you don’t have any or have flaws in it. Sooner or later if you don’t have governance controls you will have a mess, unless you have very expense skilled communicators as end users which is the very rare circumstance.

Hope this helps

Jay