SQL Query to find Archived Projects with Active Workflows Still Running in iMarkup Workflow Server Database

The query below will allow you to see if there are any Projects that have been marked as Archived in ProjectDox which might still have a running workflow in the iMarkup Workflow Server.  The reason why a user would want to know this information is because once a Project is marked as Archived in ProjectDox it is no longer listed on a User's Project List so it is unaccessible to them, however, if there is still a running workflow in the Project that was running under the iMarkup Workflow Server, there still could be a Task on the User's Task List which they have no way to complete since the Project is unaccessible.  This query will show you which Projects fit this description. 

To resolve the issue, an SA user would need to change the status of the Project by removing the checkmark for Archived to make the Project Active again.  Next they would open the iMarkup Admin window within that Project and find the workflow and cancel it.  This will cancel any active tasks that might be on User's Task List which they were unable to complete while the Project was in an Archived state.

To find the Archived Projects with active workflows, Open the SQL Management Studio and right click the iMarkup Workgroup Server database and select "New Query" and then paste the contents of the query below and run the sql script below to find the active workflows.

select Projects.Name, tblGroup.sGroupID, Projects.Status, tblWorkflow.SDesc, tblWorkflow.nStatus, Projects.Permit_Type

from tblWorkflow left outer join tblGroup on tblWorkflow.gid = tblGroup.gid
join Projectdox..Projects on Projects.name=tblGroup.sGroupID
Where tblWorkflow.nStatus = 1
AND Projects.Status = 'Archived'
group by Projects.Permit_Type, tblWorkflow.sDesc, Projects.Name, tblGroup.sGroupID, Projects.Status, tblWorkflow.nStatus