Very Sleepy SharePoint Workflow Pauses

Ahh, the dreaded sleepy 5 minute SharePoint workflow action pause that takes days even weeks to complete. Classic symptom of this problem in the workflow history logs reveals a quite unhelpfully  message such as 

[Workflow Name]failed to run

The one helpful thing the error in the workflow history logs does give is the time of the error. Using this error time I would suggest to go though the SharePoint event logs of each server and find out which server servers that this error occurs on. In my case this was occurring only on a front end server that we had set up to to only serve search index requests. it appeared that every workflow that tried to run on this server in OWSTimer was basically spewing out a error message then  putting the putting the workflow back to sleep. 

The errors it spews out are:

Workflow Compile Failed while loading AuthorizedTypes from config: Configuration system failed to initialize

and 

RunWorkflow: Microsoft.SharePoint.SPException: Configuration system failed to initialize     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.SubCompiler.DoCompile(WorkflowCompilerParameters parameters, String xomlSource, String assemblyName, CompilationPacket& packet, DirectoryInfo& tempDir)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.SubCompiler.DoCompile(WorkflowCompilerParameters parameters, String xomlSource, String assemblyName, CompilationPacket& packet, DirectoryInfo& tempDir)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.<>c__DisplayClasse.b__c()     at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.DoCompileNewAppDomain(WorkflowCompilerParameters parameters, String xomlSource, String assemblyName, SPWeb web, CompilationPacket& packet, DirectoryInfo& tempDir)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.CompileBytes(Byte[] xomlBytes, Byte[] rulesBytes, Boolean doTestCompilation, String assemblyName, SPWeb web, Boolean forceNewAppDomain)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.LoadXomlAssembly(SPWorkflowAssociation association, SPWeb web)     at Microsoft.SharePoint.Workflow.SPWinOeHostServices.LoadDeclarativeAssembly(SPWorkflowAssociation association)     at Microsoft.SharePoint.Workflow.SPWinOeHostServices.CreateInstance(SPWorkflow workflow)     at Microsoft.SharePoint.Workflow.SPWinOeEngine.RunWorkflow(SPWorkflowHostService host, SPWorkflow workflow, Collection`1 events, TimeSpan timeOut)     at Microsoft.SharePoint.Workflow.SPWorkflowManager.RunWorkflowElev(SPWorkflow workflow, Collection`1 events, SPWorkflowRunOptionsInternal runOptions)

and 

08/23/2012 08:51:01.55 OWSTIMER.EXE (0x0CBC) 0x0DB8 SharePoint Foundation Workflow Infrastructure 98d8 Unexpected Microsoft.SharePoint.SPException: Configuration system failed to initialize     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.SubCompiler.DoCompile(WorkflowCompilerParameters parameters, String xomlSource, String assemblyName, CompilationPacket& packet, DirectoryInfo& tempDir)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.SubCompiler.DoCompile(WorkflowCompilerParameters parameters, String xomlSource, String assemblyName, CompilationPacket& packet, DirectoryInfo& tempDir)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.<>c__DisplayClasse.b__c()     at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.DoCompileNewAppDomain(WorkflowCompilerParameters parameters, String xomlSource, String assemblyName, SPWeb web, CompilationPacket& packet, DirectoryInfo& tempDir)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.CompileBytes(Byte[] xomlBytes, Byte[] rulesBytes, Boolean doTestCompilation, String assemblyName, SPWeb web, Boolean forceNewAppDomain)     at Microsoft.SharePoint.Workflow.SPNoCodeXomlCompiler.LoadXomlAssembly(SPWorkflowAssociation association, SPWeb web)     at Microsoft.SharePoint.Workflow.SPWinOeHostServices.LoadDeclarativeAssembly(SPWorkflowAssociation association)     at Microsoft.SharePoint.Workflow.SPWinOeHostServices.CreateInstance(SPWorkflow workflow)     at Microsoft.SharePoint.Workflow.SPWinOeEngine.RunWorkflow(SPWorkflowHostService host, SPWorkflow workflow, Collection`1 events, TimeSpan timeOut)     at Microsoft.SharePoint.Workflow.SPWorkflowManager.RunWorkflowElev(SPWorkflow workflow, Collection`1 events, SPWorkflowRunOptionsInternal runOptions) 3010f8ef-7f1c-4adf-abba-7abfd48a28db

Now if the same workflow tried to use the same failing server to process the request the length of pause of our original 5 minute would grow experientially leading to the timer job  in some cases not even attempting to wake to workflow for days.

After reviewing the workflow counter in perfmon for this server it became apparent that no workflow has ever run properly on the this troubled server. This lead to step one of helping resolve this problem which was turning off the "Microsoft SharePoint Foundation Workflow Timer Service" on the troubled server. This stopped new occurrences [Workflow Name]failed to run in the workflow history. Happy me.

Step 2 how ever is how do we get the workflows to run on the troubled server? this still remains a mystery. Maybe ill find the answer for a different post.

Retrieving the COM class factory for component failed due to the following error: 80040154

Retrieving the COM class factory for component with CLSID {XXXXXXXX-XX...} failed due to the following error: 80040154

This error came out of nowhere today on one of our IIS boxes. There are appears to be several ways to resolve this issue:

1. Modify your project's platform from 'Any CPU' to 'X86' in Project Properties, Build/Platform's Target in Visual Studio
2. If your Application is a Web-Based Application, try setting IIS to run in 32-bit mode. You can do this by going to IIS Manager, selecting the Application Pool, then choosing "Advanced Settings". The second option from the top is "Enable 32-bit Applications".
3. If your Application is Web-Based, it could be that the com object was not configured to allow launch and access permissions for the aspnet user identity. Under administrative tools > Component services under the tree view, go to Component Services > Computers > My Computer > DCOM Config and find the registered com object. Right click for properties. Under the security tag, customize the Permissions to allow asp.net user
4. Check that the app pool is running on the correct version of .net. 

The problem for us was fix number 4, some how the app pool had switch from 4.0 to 2.0 .net. The suspect was a recent patch update.

Easy SQL split field

Here is an easy way to split a field into a left and right side use a particular splitting char (in this case a space)

SELECT     
    LEFT(TaskNo, charindex( ' ', TaskNo) ) as TheLeftSide, 
    RIGHT(TaskNo, len(TaskNo) - charindex( ' ', TaskNo) ) as  TheRightSide 
FROM         sometablename 

LINQ and Left Outer Joins

Left outer joins in LINQ are not so obvious in comparison to plain old SQL.
The following shows how an out join can be costucted in LINQ

var query = (from e in dc.GetTable<Employee>()

join ea in dc.GetTable<EmployeeAddress>()on e.Id equals ea.EmployeeId into tempAddresses

from addresses in tempAddresses.DefaultIfEmpty()

select new { e.FirstName, e.LastName, addresses.State, addresses.Town});

The SQL equivalent would be:

select [t0].[FirstName], [t0].[LastName], [t1].[State] as[State], [t1].[Town]as [Town]

from [dbo].[Employee] as  [t0

Left outer join  [dbo].[EmployeeAddress]as[t1] on[t0].[Id] = [t1].[PersonID]

Get Recently Ran Query - SQL SERVER 2005

Have you ever wanted to see what queries are running in SQL server on the fly? Yes? Well everyone’s favourite SQL server guru Pinalkumar Dave has once again provided the perfect solution with the follow simple SQL script.

SELECT deqs.last_execution_time AS [Time], dest.TEXT AS [Query]
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY deqs.last_execution_time DESC

One thing it doesn’t seem to do is recover last run quires where the database connection/session has finished. Now, Id like to see that as it whould have saved me from the silly mistake of not saving my work :(.

Pass DataContext to Silverlight IValueConverter

When passing DataContext to a Silverlight IValueConverter the following method is not supported:

<TextBox Text="{Binding Detail,
Mode=TwoWay, NotifyOnValidationError=True,  ValidatesOnExceptions=True}" 
IsEnabled={Binding ,Converter="{StaticResource contactDetailsColorConverter}} >

</TextBox>

Instead you need to translate the the above out to its expanded tag version

<TextBox Text="{Binding Detail, Mode=TwoWay, NotifyOnValidationError=True, ValidatesOnExceptions=True}"  >

<TextBox.IsEnabled>

<Binding Converter="{StaticResource contactDetailsColorConverter}"/>

</TextBox.IsEnabled>

</TextBox>

SSRS - Use URL to Render to Excel

So many times have I had to render a SSRS report to excel from an application? The easiest way to do this I have found is to use the URL of the report to set the output format.

This can be done by appending "&rs:Format=Excel" to the end of the reports URL string. For example:

http://servername/ReportServer?foldername/reportname&rs:Format=Excel

If however I needed to pass a parameter to the report from a calling application I can simply add the parameter as a url parameter as well. The following example shows who to pass a parameter called "eventID" to the SSRS report and render the output to Excel format:

http://servername/ReportServer?foldername/reportname&rs:Format=Excell&EventId=23