Running SP.UI.Status.addStatus on page load

This post is about a specific issue when running the SP.UI command ‘addStatus’ on page load as well as short discussion on the JavaScript page life cycle.

Initial Problem
When running the SP.UI.Status.addStatus command upon page load, the status message was being hidden almost immediately in Chrome but worked as expected in IE.

Scenario
We have a concept of ‘archived’ sites. A control is embedded on the page layout for site home pages (default.aspx) which renders javascript which should display a ‘this site is archived’ message. The message should be displayed using SP.UI.Status.addStatus and was initially implemented as follows (the script is being rendered dynamically using an ASP literal control, I’ll just be considering the final output):

// This is an example of what NOT to do
ExecuteOrDelayUntilScriptLoaded(function () {
    SP.UI.Status.addStatus("This is an archived site", "removed for brevity");
}, 'sp.js'););

This worked as expected in IE, however the status message would appear for a split second and then disappear in Chrome.

Investigation

The first piece of the the puzzle: What is happening?
After getting deep with Chrome DevTools I found the script responsible for hiding the status message. SharePoint utilises the document.onreadystatechange handle to run a function called fnRemoveAllStatus. I think you can guess what it achieves. Why this is being run at this point is beyond me. Importantly, I don’t want to prevent it running in case it serves a purpose that I’m unaware of.

The second piece of the the puzzle: How’s that work?
If a function is assigned to document.onreadystatechange it will be run as many as four times (depending on when in the cycle the assignment occurs), once for each transition between the following sequence of states:

  1. ‘uninitialized’
  2. ‘loading’
  3. ‘interactive’
  4. ‘complete’

Good practice would have the function check for the current state and only act once, when in the correct state. Naturally this logic is absent from the fnRemoveAllStatus function.

The third piece of the the puzzle: What is running when?
$(document).ready vs $(window).load vs ExecuteOrDelayUntilScriptLoaded
The difference between these options in regards to what we have just discussed is that $(document).ready and ExecuteOrDelayUntilScriptLoaded run when document.readyState is ‘interactive’ whereas $(window).load runs when document.readyState is ‘complete’.

Laying the final puzzle piece: Why’s it working in IE but not Chrome?
When running the code in IE, rather than executing the script block during the ‘interactive’ readyState is was being executed after transitioning to the ‘complete’ readyState and this meant that is was running after the fnRemoveAllStatus call as we desire. I believe this happens because the sp.js file is being added via a script link control with ‘LoadAfterUI’ set to true which is only understood by IE. I haven’t investigated this last comment, if I am wrong please leave a comment about it below.

Solution
So, the solution is rather simple once you have this understanding. Wrap the command in $(window).load to ensure it occurs after the fnRemoveAllStatus method is called during the transition into the ‘complete’ state. Like this:

$(window).load(function() {
    ExecuteOrDelayUntilScriptLoaded(function () {
        SP.UI.Status.addStatus("This is an archived site", "removed for brevity");
    }, 'sp.js'););
});

NB: ExecuteOrDelayUntilScriptLoaded will also load scripts which are marked to load on-demand. If you are testing in Chrome you may begin to believe it unnecessary to use ExecuteOrDelayUntilScriptLoaded when using $(window).load as all scripts have loaded by then. This is true for browsers other than IE. In IE we must use this function to ensure that the script is loaded at all.

As a final note I’d like to add that apart from this specific case I would suggest using $(document).ready rather than $(window).load as it will mean that the page loads faster (unless of course your script requires all resources to be loaded before acting. e.g. you are working with images of undefined sizes).

PerformancePoint Dashboard Designer fails to create data connection

The following error occurs when attempting to make a data connection using the PerformancePoint Dashboard Designer and the SQL Server Table data source template:

An unexpected error has occured. Additional details have been logged for your administrator.
An unexpected error has occured. Additional details have been logged for your administrator.
The last thing you see before the error
The last thing you see before the error

Disclaimer
Before I continue I want to make it clear that if you encounter this issue then you have not performed initial configuration/installation correctly for your needs and you should revisit installations guides. In my situation I’m just hacking together a dev environment which requires the use of PerformancePoint. To reiterate, better solutions to this issue exist in the form of installation guides. I wrote this because when I googled for a dirty resolution to this issue I couldn’t find any references to the error message.

Resolution
Back to the issue at hand – of course this is a configuration issue. The issue for me was that the PerformancePoint service application was configured to run in a general ‘SPServices’ application pool, where the application pool account does not have access to the PerformancePoint database (correctly). To get around this issue I configured the service application to run in its own application pool, where the application pool account is a new domain account configured to have access to the PerformancePoint database. This approach meets the least privileges approach to security which we must all strive to uphold! (I actually just reused the ‘SPFarm’ (god) domain account as the application pool account to get it working in a dev environment, but that’s the theory…)
You will most likely want to use a different account when you configure the Unattended Service Account.

Note on Analysis Services
By following the above steps I managed to get the SQL Server Table data source working, but the Analysis Services data source was still throwing up the same error dialog. Upon setting the PerformancePoint service application properties, a dialog prompts you to install the PowerPivot for SharePoint installation package. After doing this not only was I still getting the same error dialog when attempting to create an Analysis Services data source but I could no longer create a SQL Services Table data source either. Running the PowerPivot for SharePoint 2013 Configuration resolved this issue, obvious really.

ULS Log entries (included for SEO purposes)

  • Log categories: PerformancePoint Services, Database
  • System.Data.SqlClient.SqlException (0x80131904): Cannot open database "WSS_Content" requested by the login. The login failed. Login failed for user 'DOMAIN\apppoolaccount'.
  • SQL Database 'WSS_Content' on SQL Server instance 'SERVER' not found. Additional error information from SQL Server is included below. Cannot open database "WSS_Content" requested by the login. The login failed. Login failed for user 'DOMAIN\apppoolaccount'.
  • An unexpected error occurred. Error 7451.
  • No windows identity for DOMAIN\apppoolaccount.
  • Unable to load custom data source provider type: Microsoft.PerformancePoint.Scorecards.DataSourceProviders.AdomdDataSourceProvider, Microsoft.PerformancePoint.Scorecards.DataSourceProviders.Standard, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.AnalysisServices.AdomdClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. File name: 'Microsoft.AnalysisServices.AdomdClient, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'

HTH

Cancel all workflows in site collection efficiently

Here you will find a script to cancel all list workflows (not site workflows) running in a given site collection in the most efficient manner possible (that I can think of) while still only using the SharePoint API. A better approach still would be to query the SharePoint workflows SQL table to identify the running workflows so as to avoid iterating all site collection webs and lists. Unfortunately there is no ‘GetAllRunningWorkflows(SPSite)’ method available via the API. I imagine that this approach should be satisfactory in the majority of cases though.
cancelbutton
There are a number of posts on the web with code somewhat similar to this, at least with code that aims to achieve the same outcome. Of all the posts which I found they all performed this function in a very inefficiency manner, iterating the SPListItemCollection for every list in the site collection. This may be fine in many circumstances but I wanted something that would run faster with less strain on the server.

I have achieved this by checking for workflow associations on a list before iterating the items as well querying the list items and where a workflow column exists checking to see if the list item needs to be returned at all. When returning the list items, I am querying with ViewFieldsOnly so that less data is returned.

This script also accepts an optional parameter that specifies which workflow associations should be canceled if you are not looking to cancel all of the workflow associations but just those of a specific name.

NB: The script contains a reference to a help function GetNestedCaml which I have defined in a separate post which can be found here.

As the script sample is quite large I suggest clicking the ‘view raw’ link at the bottom of the sample to view it.

Dynamically nesting CAML query statements

Here is a short PowerShell function that can be used when you need to dynamically generate CAML queries with many logically joined statements. I actually adapted it from a C# implementation I wrote (which is probably more useful…) but as you can rewrite it in C# very easily I won’t bother bother posting it twice.

As CAML logical join operators (And, Or) can only compare two statements, when many statements need to be compared you must nest them which is what this function achieves. The $join parameter should be passed as “And” or “Or” and the $fragments parameter should be passed as an array of CAML statement strings such as:
@("<Eq><FieldRef Name='Title' /><Value Type='Text'>title</Value></Eq>", "<Eq><FieldRef Name='FileLeafRef' /><Value Type='Text'>name.docx</Value></Eq>")

# Define method for nesting caml query statements
function GetNestedCaml([array]$fragments, [string]$join)
{
    if ($fragments.Length -lt 1)
    {
        return [string]::Empty
    }
    elseif ($fragments.length -eq 1)
    {
        return $fragments[0]
    }
    elseif ($fragments.length -eq 2)
    {
        return "<$join>" + $fragments[0] + $fragments[1] + "</$join>"
    }
    $joinFrags = @()
     $baseJoinCount = [int][Math]::Floor($fragments.length / 2)
    for ($i = 0; $i -lt $baseJoinCount; $i++)
    {
        $baseIndex = (2 * $i)
        $fragsToJoin = @($fragments[$baseIndex], $fragments[$baseIndex + 1])
        $joinFrag = GetNestedCaml $fragsToJoin $join
        $joinFrags += $joinFrag
    }
    if ($fragments.length % 2 -ne 0)
    {
        $joinFrags += $fragments[$fragments.length - 1]
    }
    return GetNestedCaml $joinFrags $join
}