Site Policies and FBA in SharePoint: Update

My apologies to Craig, who posted a comment to my earlier post about our FBA problems and I didn’t notice until today.

To update you all on the situation, the fault is still with Microsoft and I have not yet received a hotfix.

However, for anybody considering FBA in their deployment, I would not let this issue stop you. There are two reasons I say that:

  1. Normally with FBA you would extend the web application in question, having both FBA and Windows authentication available on the same content via different URLs. This makes your life easier with things like indexing and management.
  2. The workaround I detailed is a good temporary solution to the problem with only minimal impact on the user experience (in that certain options are offered which may not work too well in Office when using FBA).

Hopefully this answer’s Craig’s question and assuages any doubts about the wisdom of deploying FBA in your SharePoint solution.

Life with a Diamond: nearly two weeks on

I said I’d post again once I’d had the Diamond a little while. It’s now been two weeks and I can honestly say I’m completely happy with it. Battery life for me is fine – I charge it about every three days and it chugs along with exchange push pretty much constantly. I am quite comfortable with the soft keyboard and I can honestly say I haven’t noticed any issues with the speed of the device either.

I don’t usually go for silly little apps on my phone, but I couldn’t resist this one. It shows just what you can do with the accelerometer. The force is strong in this one!

Workflow and SQL Error: Update

I posted last week about a couple of issues we were experiencing with SharePoint. I made some traction on the Workflow History issue at the end of last week and the revelation was pretty far-reaching, so I’m posting again.

It turns out that the stuff I said about systemupdate was wrong… up to a point.

There is a bug with systemupdate and triggering events, but it’s not the one we thought it was! It turns out that the behaviour we are seeing is correct – systemupdate is supposed to trigger events, just not update things like the modified by and last updated columns. It’s actually the behaviour within a workflow which is at fault, in that events aren’t being triggered when they should be.

I had a chat with our developers about this and they told me that there are plenty of articles on the web suggesting that systemupdate is the way to update an item in a list without triggering events. Don’t do it! I was told by Microsoft that whilst the fault is not high on the list because there is a workaround (which I will list in a moment), it will be fixed. At that point, anybody who is using systemupdate expecting events not to fire will get a shock.

The MSDN documentation for system update is pretty clear:

When you implement the SystemUpdate method, events are triggered and the modifications are reported in the Change and Audit logs, but alerts are not sent and properties are not demoted into documents.

The explanation as to why events don’t fire is:

When you used in other places such as windows/console app, another workflow or webparts, you are not seeing the event trigger the workflow, this is due to the Workflow runs on separate threads from the main thread, so we cannot fire up the workflow and simply quit. Quitting an app before the async worker threads are finished causes those threads to simply abort, and in the case of workflow, nothing will appear to have happened.

And the fix:

Currently, all standalone applications must call spsite.workflowmanager.Dispose(). This call waits for the threads to complete and causes workflow to go into an orderly shutdown.

And the solution to the problem of wanting to not trigger events? Well, it looks like the method I described in my earlier post is the way to go.

Touch Diamond Battery Life

In my last post about the Diamond I said I would let you know what the battery life is. I therefore carefully refrained from giving it any charge over the weekend, even when connected to my PC.

That means that it received no power from 5.30pm on Friday until it died (which it did, eventually).

After that time, exchange push carried on notifying me of email immediately on arrival until 8pm, after which it downshifted to checking every hour and continued like that for the rest of the weekend.

Weather updates are whatever the phone does as default. I think that’s every four hours or so.

Over the weekend I received a few calls and made a few. None were particularly long – a few minutes each. I also played a few games of teeter, and spent a good hour or so ‘playing’ with Live Search and Diarist, writing a blog post whilst I was at it. I also spent a good thirty minutes with GPS enabled whilst I showed off Google Maps and Live Search, and browsed the web for maybe ten minutes in total.

All in all, it was more or less my usual pattern of use for the weekend. To give you a frame of reference, my TYTN would go on charge every night. It would make it through the weekend, just about, but only if I charged it on Friday night.

The Diamond died sometime overnight Sunday/Monday. When I picked it up this morning the battery was flat. It was alive at 11pm on Sunday, and I checked it at 8am this morning. Whilst that kind of life isn’t great, it’s not bad. It’s certainly on a par with my TYTN, which I have used happily for the past eighteen months. I’ve not yet spent a day away from the office, but I have two such days this week and I will let you know how I get on.

blogging on the move: redux

Richard’s little mobile blogging app is nice, but he isn’t that responsive to user requests (ie, mine!) so I thought I would see if anything else was out there.
Sadly, the answer appears to be ‘not really’
However, I did come across Diarist, from Kevin Daly. Perhaps not surprisingly I am using it now. I must say that two thumb typing on the diamond keyboard is not bad, although nowhere near as fast as the real keyboard on the tytn.
So is diarist better than blogwriter? Well, it’s easier to add links and images… Still no real formatting support though, which is a shame.
Try it for yourself.

My Orange Diamond is… err… black

That might sound like a strange thing to say, but when my shiny new Touch Diamond, which I picked up from Orange this week, has no Orange branding or customisation of the UI. I hadn’t really noticed that until I went into the manual, where the screenshots are totally different from my phone. I was beginning to wonder if something had gone wrong with the setup process, when I saw this thread at XDA-Developers.

Given that the phone shipped the day before the iPhone, I suspect the comments in that thread are close to the money – not enough time to test the customisations.

Personally, I’m much happier with a generic HTC phone. For one thing, I always turn off the Orange stuff anyway if I can, and for another, it should mean that I can apply HTC updates to my device without having to wait for Orange to release them (if ever!)

In related news, I was as surprised as Pocket PC Thoughts when I read the PCPro review of the Diamond. I’m not experiencing the speed issues of which they speak, and if you look around you will see a number of articles where they say that HTC acknowledged the speed problem in early devices and had made software updates to the retail devices to address them. My diamond spent yesterday connected to my PC and charging, but I will keep it away from the charger until it dies from this point forward to see how long I get. Remember that I hav exchange push chugging along all the time, so it’s never simply idle.

 Even if you are worried about battery life (which I must admit, I was), then Expansys will shortly have the answer in the form of the extended battery!

In short then, still impressed with my new gadget!

HTC Touch Diamond on Orange

Thanks to the advanced notice from Tracy and Matt’s blog, I picked up my HTC Touch Diamond yesterday from the Orange Store and it’s great!

‘But wait!’ I hear you cry, ‘didn’t he go on about the experia x1 and how he wanted one?’

Well, yes, I did. I admit it – I am fickle in my affections.Since I wrote that post, I have spent time considering what I want in a phone and size has become a significant factor. The Diamond is tiny, it really is. It’s not far from the size of my old Nokia 8910 and it’s lighter than that old warhorse.

I also did a fair amount of homework. My TYTN had a keyboard. Could I live without it? Almost as soon as the Diamond appeared, the soft keyboard turned up on the web, so I tried it. I found that I was happy with the turn of speed I could manage with the new soft keyboard and I was happy that I could live without the physical one.

I’m not going to write a review – there are plenty out there that go into more than enough detail. I will give you my opinions, though. I am a business user and the Diamond has to meet my needs as a connected business traveller. Does it do that?

Well, I’ve never had a phone that was so easy to get started with. Turn it on, run through the Windows Mobile setup and then configure my exchange server. In less than five minutes I had all my contacts, emails, calendar and tasks on the new phone. Smooth, easy, no issues whatsoever. Brilliant!

The new UI makes it easy to preview new mails and the design is so slick it’s a pleasurable experience. A few swipes of my thumb and I can spin through new mails before I tap on the one I want to read. So much faster and easier than the old Touch home screen which took me a good few taps just to get into my inbox.

The same is true for SMS messages – it’s so quick to view the new message. If I want to reply it drops me into the old-style Windows Mobile UI, but many of the messages I get don’t need a reply.

The screen is fabulous. The extra resolution is so nice to have – text is much smoother and more readable. Then you open Opera (which is fantastic) and browse to a web page. When pages open, Opera zooms the view out so you see the whole page. The experience is really good, but then you look again and realise that you can actually read the text!

I don’t have TomTom on the Diamond – I have a standalone TomTom and I don’t see the need to duplicate. However, the Diamond arrived with Google Maps installed so I did have a play. To be honest, the functionality offered will do what I need on my phone – where in the big city am I in relation to where I need to be.

One thing I find extremely useful – when I receive a call that’s not in my phone book, the Diamond asks me if I want to add the number to my contacts when I end the call. For somebody who gets regular calls from colleagues and customers whose numbers I want to quickly grab, that’s great!

Overall, I would have no hesitation in recommending this phone to anybody. The only area I would urge you to think about is the keyboard. If you absolutely, positively need a real keyboard, wait until September and pick up the Touch Pro. If you don’t, you really won’t regret picking up a Diamond.

Useful .Net Search and Replace tool

I need to update something like 300 web.config files today, with the same change in each. I turned to my old friend Google for his recommendations and up popped a magnificent free tool from Nodesoft.

Why magnificent? It’s advanced mode allows you to specify start and end tags and update everything in between. That’s fantastic for dealing with big chunks of web.config files! Better yet, give it a file mask and a path and it will run through the entire folder tree if you like. Update whole servers in seconds.

I just wish I’d found it sooner…

Workflow History and SQL Error

When trying to view an item in a list which has workflows run against it, you get an error:

Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries

Problem Background

Trying to explain the exact nature of our configuration in this case would break many people’s heads. This, therefore, is a bit of a simplification.

We have a custom webpart which allows users to log an enquiry. We create an item in a list with the enquiry details, and send an email to the account responsible for dealing with those enquiries. A copy of the list item is created in another list (we’ll leave out the why and wherefore of that for now). The two copies must be kept in sync. More details on that later.

Those enquiries must be closed within 30 minutes. If not, an escalation email is sent. An enquiry is closed if a particular column changes value. To ensure the two lists are kept in sync, when an item is changed a workflow is triggered. If the column we care about has changed we sync up the item in the other list.

The escalation process is a timer. It checks the items and sends emails. It updates a column with the time of the last email sent so we can repeat the process every 30 minutes.

What we found was that the enquiries weren’t being closed for a few days and in that time we could then not access the enquiry item at all via the web interface (although datagrid view still worked!). We saw the error at the top of this post.

The Root of the Matter

This fault is currently with our Microsoft Support team and they are working through it. I do, however, have enough knowledge and understanding of why the fault occurred to explain it, and a few dirty hacks to avoid it.

The reason we can’t access the items is because when SharePoint pulls up the item for edit/view it checks the Workflow History for that item. If there are more than about 200 entries for that item in the Workflow History list, we get the SQL query error and boom! That’s the long and short of it.

The deeper question is why? More importantly, why do we have over 200 workflows running on the item?

Workflow History first. The Workflow History list is a hidden list which does exacly what it says on the tin. Items are created each time a workflow runs. It turns out that items in the Workflow History list have a time-to-live and that time is 60 days. That means that any item in the list will automatically be deleted after 60 days. With roughly a 200 item limit before you hit trouble that means about 3 workflows per list item per day is your maximum.

Personally, I think that is a scalability issue. I can envision a scenario where we might want to run that many workflows by design, perhaps more.

Back to the plot. I suspect you’re sitting there thinking that in our case, having that many workflows run is bad design or a fault. Well, you’re not wrong, although you’re not quite right either.

We knew when we built the workflow that we had to avoid circular references and update the lists as little as possible. There is code to make sure that changes made by the workflow itself are not reflected back, and if the change is not the column we care about then the workflow exists cleanly.

We also knew that because the timer job updates a different column in the list item, that would trigger the change event on the list item, running the workflow. As a result the timer performs a system update on the list item which should not trigger events (and indeed does not when we have used the method elsewhere).

What this means is that we actually have two problems:

  1. The system update method when used in our timer is not working correctly and events on the list item are being triggered. This means that the workflow is running too often.
  2. The issue with Workflow History means that we very quickly hit the 200 item limit and meet our end with the SQL query error.

A Legion of Dirty Hacks

As I write, these issues are with Microsoft Support who are ably working to resolve them. In the meantime, we have made the problem go away with two approaches, both of which I regard as dirty hacks.

The Workflow History Conundrum

Whilst investigating this problem I came across a discussion on the TechNet support forums. Ironically this was coming at the same problem but from a wholly opposite angle, whereby people wanted to keep items in the Workflow History list for longer!

What I found in that list was a post by Fred Morrison containing a PowerShell script. I am re-posting it here for completeness in case the forum disappears, but all credit to Fred for this – I didn’t write it!

   1: # SPAdjustAutoCleanupDays.ps1
   2: # Author: Fred Morrison, Senior Software Engineer, Exostar, LLC
   3: #
   4: # Purpose: Adjust SharePoint Workflow Association AutoCleanupDays value, where necessary
   5: # on all workflow associations for a specified List.
   6: #
   7: # Parameters:
   8: # siteName - The SharePoint Site to look at
   9: # listName - The SharePoint List to look at
  10: # newCleanupDays - The number of days to set the workflow association AutoCleanupDays value to, if not already set.
  11: #
  12: # Example call: SPAdjustAutoCleanupDays http://workflow2/FredsWfTestSite FredsNewTestList 180
  13: #
  14: # following makes it easier to work with SharePoint and also means you have to run this script on the SharePoint server
  15: [void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") | Out-Null
  16: # capture command line arguments
  17: $siteName = $args[0] # ex: http://workflow2/FredsWfTestSite/
  18: $listName = $args[1] # ex: FredsNewTestList
  19: [int] $newCleanupDays = [System.Convert]::ToInt32($args[2]) # ex: 1096
  20: Write-Host $siteName
  21: Write-Host $listName
  22: Write-Host $newCleanupDays
  23: # get a reference to the SPSite object
  24: $wfSite = New-Object -TypeName Microsoft.SharePoint.SPSite $siteName
  25: [Microsoft.SharePoint.SPWeb] $wfWeb = $wfSite.OpenWeb()
  26: Write-Host $wfWeb.ToString()
  27: # get a reference to the SharePoint list we wish to examine
  28: [Microsoft.SharePoint.SPList] $wfList = $wfWeb.Lists[$listName];
  29: Write-Host $wfList.Title
  30: [Microsoft.SharePoint.Workflow.SPWorkflowAssociation] $wfAssociation = $null
  31: [Microsoft.SharePoint.Workflow.SPWorkflowAssociation] $a = $null
  32: [int] $assoCounter = 0
  33: [string] $message = ''
  34: # Look at every workflow association on the SPList and make sure the AutoCleanupDays value is correctly set to the desired value
  35: for( $i=0; $i -lt $wfList.WorkflowAssociations.Count; $i++)
  36: {
  37: $a = $wfList.WorkflowAssociations[$i]
  38: [string] $assocName = $a.Name
  39: Write-Host $a.Name
  40: if ( $a.AutoCleanupDays -ne $newCleanupDays )
  41: { 
  42: $oldValue = $a.AutoCleanupDays 
  43: $a.AutoCleanupDays = $newCleanupDays
  44: # save the changes 
  45: $wfList.UpdateWorkflowAssociation($a) 
  46: $message = "Workflow association $assocName AutoCleanupDays was changed from $oldValue to $newCleanupDays"
  47: }
  48: else
  49: {
  50: $message = "Workflow association $assocName AutoCleanupDays is already set to $newCleanupDays - no change needed"
  51: }
  52: Write-Host $message
  53: }
  54: Write-Host 'Done'

I simply ran that script on our system, setting the value for newCleanUpDays to 1. I waited a day and voila! All the list items were now accessible. Note that, as repeated in the forum discussion time and again, messing about with this is not a good idea. I simply have no choice right now.

The Timer Incident

It was all very well fixing the Workflow History list, but we really shouldn’t be seeing all those workflows in the first place. For some reason, our method of updating the list item from the timer, whilst being the official approach, triggered the workflow anyway.

To the rescue, a method we found on the blog of Paul Kotlyar. In that post, Paul talks about disabling event firing for the list item to ensure that no events get triggered. Why do I think this is a hack? Because the functionality is not normally found in workflows and timers – the method is part of SPEventReceiverBase.

Where Do We Go From Here?

Right now, I have support cases logged with Microsoft and engineers are working on the matter. We’ve already been via the SQL team, who looked at the original query that triggered the whole shebang, and they have returned an updated query for the SharePoint guys to look at. We also need to get to the bottom of a ‘correct’ way of updating list items without triggering events. As soon as I get a resolution from Microsoft, I will let you know.