Connected Systems Chilled Out Blog

Sir David Varney headed a review into Transformational Government in 2006 . Among its recommendations was one particular one that had been driven by the amount of contact points a citizen had to make with government for a Change of Life event such as birth and death.

It gave one example of where a citizen had to make 44 separate contacts to government departments following the premature death of her husband.  At this emotional time, this kind of experience adds an unnecessary burden on the customer which is rightly seen as unacceptable.  The report's recommendations included developing a change of circumstances service starting with bereavement, birth and change of address by 2010, so that citizens didn't have to notify multiple public services. The Tell Us Once programme was setup shortly afterwards to investigate how to progress this recommendation.

‘Tell us Once’ is a major programme, being led by DWP on behalf Government as a whole, to transform the way in which people can tell Government (central and local) about changes to their circumstances. At its heart –as it says on the tin –is the proposition that people should only have to tell us things –like a birth or death –once and we will make sure that information reaches all the Government Departments and Agencies that need to know. That sounds an utterly obvious thing to do –and it is –but making it a reality is very challenging.” 

Sir Leigh Lewis, Permanent Secretary Department for Work and Pensions

Solidsoft have been involved with this project almost since the start so much so the work delivered in support of the Tell Us Once pilot has been recognised by Microsoft.  On the 23rd June it was announced Solidsoft had been selected as a finalist for the Microsoft Partner Awards in the Custom Development Solutions, Application Infrastructure Development Partner of the Year award category.

“It’s a pleasure and a privilege to receive this nomination, particularly in such a highly competitive field. The Solidsoft Tell Us Once Team has created a great Pathfinder Application for the United Kingdom, which both saves the taxpayer money and makes it easier for citizens to interact with their Government. Not only is the application a success in the eyes of Microsoft, the customer feedback on their experience has been outstanding. We look forward to continuing to deliver innovative solutions on the Microsoft Application Platform to both public and private sector customers.”

Solidsoft – www.solidsoft.com

Moving forward the Solidsoft Tell Us Once development team is delivering the full national solution to meet the Varney desire of having Tell Us Once in place for 2010.

This blog will track the progress of a Government IT project that delivers tangible results for the citizen.

An interesting ‘rant’ by my fellow Solidsoft buddy Russell Smith:

Thought you chaps would be interested in this after hearing my rantings yesterday about the WCF-SQL adapter and notification issues.

I got to the bottom of the issue, it wasn’t the adapter in the end it was the database and not the adapter at all.  This was a database supplied by PICT that was restored onto my image.  Apparently in order for the notification mechanism to work there are some strict requirements on the database SET options and in the select queries that can be used.  I finally found a decent article at: http://msdn.microsoft.com/en-us/library/ms181122.aspx

A quick summary is that the DB must have the following options set:

• ANSI_NULLS ON
• ANSI_PADDING ON
• ANSI_WARNINGS ON
• CONCAT_NULL_YIELDS_NULL ON
• QUOTED_IDENTIFIER ON
• NUMERIC_ROUNDABORT OFF
• ARITHABORT ON

And the select statement must conform to:

• The projected columns in the SELECT statement must be explicitly stated, and table names must be qualified with two-part names. Notice that this means that all tables referenced in the statement must be in the same database.
• The statement may not use the asterisk (*) or table_name.* syntax to specify columns.
• The statement may not use unnamed columns or duplicate column names.
• The statement must reference a base table.
• The statement must not reference tables with computed columns.
• The projected columns in the SELECT statement may not contain aggregate expressions unless the statement uses a GROUP BY expression. When a GROUP BY expression is provided, the select list may contain the aggregate functions COUNT_BIG() or SUM(). However, SUM() may not be specified for a nullable column. The statement may not specify HAVING, CUBE, or ROLLUP.
• A projected column in the SELECT statement that is used as a simple expression must not appear more than once.
• The statement must not include PIVOT or UNPIVOT operators.
• The statement must not include the UNION, INTERSECT, or EXCEPT operators.
• The statement must not reference a view.
• The statement must not contain any of the following: DISTINCT, COMPUTE or COMPUTE BY, or INTO.
• The statement must not reference server global variables (@@variable_name).
• The statement must not reference derived tables, temporary tables, or table variables.
• The statement must not reference tables or views from other databases or servers.
• The statement must not contain subqueries, outer joins, or self-joins.
• The statement must not reference the large object types: text, ntext, and image.
• The statement must not use the CONTAINS or FREETEXT full-text predicates.
• The statement must not use rowset functions, including OPENROWSET and OPENQUERY.
• The statement must not use any of the following aggregate functions: AVG, COUNT(*), MAX, MIN, STDEV, STDEVP, VAR, or VARP.
• The statement must not use any nondeterministic functions, including ranking and windowing functions.
• The statement must not contain user-defined aggregates.
• The statement must not reference system tables or views, including catalog views and dynamic management views.
• The statement must not include FOR BROWSE information.
• The statement must not reference a queue.
• The statement must not contain conditional statements that cannot change and cannot return results (for example, WHERE 1=0).
• The statement can not specify READPAST locking hint.
• The statement must not reference any Service Broker QUEUE.
• The statement must not reference synonyms.
• The statement must not have comparison or expression based on double/real data types.
• The statement must not use the TOP expression.

I think however the main reason this is likely to be a no go in a production environment is the following permissions and alter statements also have to be run:

ALTER DATABASE [Biztalk_saafmdb] SET NEW_BROKER with rollback immediate

ALTER DATABASE [Biztalk_saafmdb] SET ENABLE_BROKER

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password1#'

select is_broker_enabled from sys.databases where name = 'Biztalk_saafmdb'

use Biztalk_saafmdb

GRANT CREATE PROCEDURE TO [BTS2009-RUSSELL\BizTalk Application Users]

GRANT CREATE QUEUE TO [BTS2009-RUSSELL\BizTalk Application Users]

GRANT CREATE SERVICE TO [BTS2009-RUSSELL\BizTalk Application Users]

GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [BTS2009-RUSSELL\BizTalk Application Users]

GRANT SELECT ON OBJECT::Biztalk_saafmdb.dbo.bts_MembersRetired TO [BTS2009-RUSSELL\BizTalk Application Users]

GRANT RECEIVE ON QueryNotificationErrorsQueue TO [BTS2009-RUSSELL\BizTalk Application Users]

And yes these permissions do need to be maintained at run time, there is a generated stored procedure that gets created on various events, not got to the bottom of this completely but it appears to be when the receive location is started and stopped.