Wednesday, 27 February 2013

Purge Old Nintex 2010 Workflows

Nintex workflow can be a very helpful tool, but there is currently no automated way to remove orphan workflows. The workflow engine will still try and hydrate - run - dehydrate all 'running' workflows, even if they are attached to orphan lists.

I use the following code to remove all my workflows. Please note that this will remove EVERYTHING INCLUDING CURRENT WORKFLOWS. Its very helpful to speed up workflow response time and remove the dreaded 'Nintex is very busy. Please be patient' message.

The logic of the code is quite simple. First, generate a list of all the items in the nintex database (based on webId, siteId and listId) that do not exist in the specified content database. Then loop through all the records calling the OOTB PurgeWorkflow stored procedure.

You will need to change the variable at the top of the code to match your environment.
I have added a 'DATEADD' portion to the code, so that workflows for the last X days are saved.

Enough banter, show me the code.

declare @nintexDB varchar(255) = 'NW2010DB;

declare @wssDB varchar(255) = 'WSS_Content';

declare @sql varchar(max);

declare @id int = 0;

declare @workflowName varchar(255);

declare @workflowInstanceId uniqueidentifier;

declare @itemId int;

declare @state int;

if object_id('tempdb..#orphans') is not null

drop table #orphans;



create table #orphans

(

ID int not null identity,

WorkflowName varchar(255),

WorkflowInstanceID uniqueidentifier,

ItemID int,

[State] int,

StartTime datetime

)

create unique clustered index UCI on #orphans (ID);

select @sql =

'

insert into #orphans(WorkflowName, WorkflowInstanceID, ItemID, State, StartTime)

select A.WorkflowName, A.WorkflowInstanceID, A.ItemID, A.State, A.StartTime

from '
+ @nintexDb + '.dbo.WorkflowInstance A

join

(

select SiteID, WebID, ListID, ItemID

from '
+ @nintexDB + '.dbo.WorkflowInstance

EXCEPT

Select SiteID, WebID, ListID, doclibrowid

From '
+ @wssDB + '.dbo.AllDocs

) B

on A.SiteID = B.SiteID and A.WebID =B.WebID and A.ListId = B.ListID and A.ItemID = B.ItemID

and StartTime <= DATEADD(dd, -0, GetDate())'

EXEC (@sql);

while (@id is not null)

begin

select @id = min(ID) from #orphans where ID > @id;



if (@id is not null)

begin

select @workflowName = WorkflowName,

@workflowInstanceId = WorkflowInstanceID,

@itemId = ItemID,

@state = [State]

from #orphans

where ID = @id



set @sql = 'EXEC ' + @nintexDB + '.dbo.PurgeWorkflowData @instanceId = ' + '''' + convert(varchar(255), @workflowInstanceId) + '''';

EXEC (@sql);

end

end

Show all fields with JQuery

My current project uses jquery extensively to manipulate the data in our SharePoint forms. Fields are hidden and renamed at will. We recently encountered an error that was traced back to a field being changed to nullable in the column definition.

The key to finding the problem was 'unhiding' all the fields that our extensive jquery had changed. A very effective little piece of code:

$('*').css('display','');

Tuesday, 5 February 2013

SQL Server 2008 Scripts: Shrink a database

My current Sharepoint project has several search crawls and data migrations firing during deployments, which is creating some space concerns from my poor development VM. I have added the following script as a batch file on my desktop to free up space in my local SQL server by doing a dump and shrink.

Yes, I know that dumping and shrinking transaction logs is not best practice, but this a dev environment and that which does not kill it allows me to get my work done.

The script uses the great (undocument, but readily document on the internet) master..sp_msforeachdb. A simple one line piece of SQL does the trick:

osql.exe -S. -E -q "exec master..sp_msforeachdb 'ALTER DATABASE [?] SET RECOVERY SIMPLE; DBCC SHRINKDATABASE ([?], 1); ALTER DATABASE [?] SET RECOVERY FULL;'"