Tuesday, November 23, 2010

Deleting an Email Account on the Windows Phone 7

I don’t think that it is completely obvious how to delete an email account on the Windows Phone 7, so I am writing this quick blog post to describe how to do it.

  1. Click on the Windows button to get to the start screen (with the tiles)
  2. Swipe right to get to the applications list
  3. Swipe down until you find the settings icon.
  4. Tap on Settings to enter settings.
  5. Choose email & accounts
  6. Find the email account that you want to delete.
  7. Press down on the email account you want to delete until the secondary menu appears.
  8. There will be an option to delete, tap it.


Saturday, November 20, 2010

A Smart Overloading of a Stored Procedure Call by Mining COALesce.

Often you have a table where different columns needs to be updated by different calls.  Traditionally, you wrote a stored procedure for every variation which can result in a mountain of code (increases your LOC/hr but drives maintenance cost up more!)


IF you can eliminate NULL as being a valid value (use ‘’ instead?) then you can reduce everything to a single stored procedure as illustrated below.


Create proc harvest.P_UpsertEvent @ID uniqueidentifier=null ,@ParentID uniqueidentifier=null ,@ConsolidationSequenceNo bigint=null ,@Level smallint=null ,@EventFulId varchar(50)=null ,@VenueID uniqueidentifier=null ,@Title nvarchar(120)=null ,@Text nvarchar(max)=null ,@Html nvarchar(max)=null ,@StartAt datetime=null ,@EndAt datetime=null ,@ExtendedXml xml=null AS If Exists(Select 1 from Events.Event Where ID=@ID) BEGIN Update [Events].[Event] SET [ConsolidationSequenceNo]=Coalesce(@ConsolidationSequenceNo,ConsolidationSequenceNo) ,[Level]=Coalesce(@Level,Level) ,[EventFulId]=Coalesce( @EventFulId,EventFulId) ,[VenueID]=Coalesce(@VenueID,VenueID) ,[Title]=Coalesce(@Title,Title) ,[Text]=Coalesce(@Text,Text) ,[Html]=Coalesce(@Html,Html) ,[StartAt]=Coalesce(@StartAt,StartAt) ,[EndAt]=Coalesce(@EndAt,EndAt) ,[ExtendedXml]=Coalesce(@ExtendedXml,ExtendedXml) Where ID=@ID END

In short, if a new (NOT NULL) value is supplied, it is used – otherwise the existing value is used.


If you are working with column level permissions, then you can just insert logic to set values from an unauthorized user (for the column but not the row) to a null. This actually makes the number of lines of code to implement column level permissions a fraction of other implementations.

Sql Upgrade Strategies

In my prior post I described how you can identify the different between a prior production database and a developer’s evolution of the database. You could make the developer responsible for developing the scripts but that is not always the best use of such a resource (do you make the developer responsible for writing all of the automated unit tests for their code and thus do not need testing resources? The appropriate best use of a developer issue is similar). Whether the developer’s does it, the database developer does it or a contracted resource does it – you end up with a batch of TSQL for each ‘upgrade’ (there may be dozens of developer’s upgrades in a product release cycle). This post looks at the management issue.


  1. We have the TSQL to go from Prod –> Dev
  2. We want to apply it to some other Prod system


The basic pattern is a series of update files and a console application that reads the TSQL in , configuration information and then apply the information. The files must be sequenced (manifest file or by file naming).


NOTE: For a release system, the TSQL may be in a password protected zip file that the console application has the password for. For development, the TSQL may be coming from files in the same folder.


Put a Version Table in the database

This table is essential for easy management of the database. It is also useful for well managed code, your C# code can check to see if the database supports an feature and thus provide backward compatibility. An example is shown below:

var spName = string.Empty(); if (DBVersion > 1783) { spName = "p_GetOrgaizationTree_3"; } else if (DBVersion > 234) { spName = "p_GetOrgaizationTree_2"; } else if (DBVersion > 83) { spName = "p_GetOrgaizationTree_1"; } else { spName = "p_GetOrgaizationTree"; }

The DBVersion is obtained and cached when the application starts so there is only one call to it. My preference is to use an Int and increment it by 10 each time (there’s are advantages to leaving space between them – see below).

Sequential Update

If you put each batch of TSQL into XML (instead of using a .sql file) you are able to do a better control update of databases. For example, if the root element is:


  <dbupdate fromversion=”230” toversion=”240”>


Then if the database version is not a match, the file is skipped. If it is a match, then the dbversion is incremented by 1 at the start (to 231) of executing the file content and then updated to 240 at the successful end. If any statement fails, the console utility exits – leaving a log file to be sent to support. The database is left in state 231 (so a repeat execution will not execute anything because there is no script for ‘231’). After supports figure out the problem, it sends a new file with a header of:


   <dbupdate fromversion=”231” toversion=”240”>


This gives support some 9 attempts at fixing any problems…


The use of XML allows better control of what SQL in an update actually gets implemented.  Often the TSQL is sufficient, for example:


In some cases, writing a pre-condition that is executed by the console application is more efficient or easier to understand.

<function check="Select count(1) From sys.objects where name='GetProfileTypeID'" id="function17" executed="10/30/2010 1:40:49 PM"> CREATE FUNCTION [Sync].[GetProfileTypeID] ( @ProductionID Int ) RETURNS Int AS BEGIN DECLARE @DevId Int DECLARE @ProfileTypeName nvarchar(max) SELECT @ProfileTypeName=ProfileTypeName FROM BoardDemos.dbo.ProfileType Where ProfileTypeID=@ProductionID SELECT @DevID=ProfileTypeID From BoardDemos_Dev.dbo.ProfileType WHERE @ProfileTypeName=ProfileTypeName RETURN @DevID END </function>

Remember that in some cases, items cannot be dropped and recreated because they are in use elsewhere.

Bail on first exception

The console application should bail on the first exception. In general, it should also do a backup of the database before any execution. The console application should also support doing an automatic rollback/restore if any exception occurs (this may be ON by default, and set to OFF when debugging issues).  You should not leave a customer installation in limbo.


In some cases, you may also wish to do a diagnostic backup at the point of failure before doing the restore.


Gotchas for Data

In the past I have encountered two situations where there’s been some pain in writing scripts.  When the primary key is Identity(1,1) and new data is being added that must match up with existing records; OR  when the primary key is a GUID and new data is being added that must match up with existing records AND there is no reliable alternative key (unique index) to use for match ups.  The easiest solution is often to create a SyncID column that is a GUID and explicitly set it (instead of relying on NewID()) and use this as an alternative key.  In some cases, I have had to infer the match by doing a:




to insure that there is an expected match.  In other cases, there may be rename of something, for example “Winslow” –>  “Bainbridge Island” in the production system (with the center in terms of longitude and latitude being updated too!). The development system did not have this change applied. If there was a SyncID() in the table (which used an Identity(1,1) for primary key) then life is much easier.  This actually facilitate a two way update of the Prod and Dev databases (Some data from prod can update the dev database).


Over Christmas I may make a drop of my version of the above console application available for any interested parties – so drop me a line (or leave your email here)…

Startups and Sql Server Databases

Often a startup tosses out a database to get a web site up or product out of the door. This is reasonable when there is neither the time(-to-market) nor the money (for SQL Data Modelers and developers).


For a web site there is usually one of two patterns of evolution:

  1. Use the production database for development.
    1. There’s no problem of matching database to next generation code base.
    2. Side-effects can be horrible from miscoding or security breaches.
  2. Use a clone of the production database and then evolve it with the next generation code.

For a product database, 2. above is the typical option. We will consider this type of option alone in this post. The second pattern we need to look at is a non-issue for a one-man development shop, but arises with there are multiple developers:

  1. A single shared development database
    1. Any problems impacts the entire development team
  2. Each developer has their own development database
    1. Each developer evolves the database to suit their own needs

Even with ‘get-out-of-jail (= SQL skill sets)’ free cards of dbml / Linq-to-Sql, problems arise.


The problem is simple:  What TSQL is needed to convert old DB to new DB while maintaining existing (customer) data. For the rest of this post we will call the databases ‘Prod’ and ‘Dev’.  In reality this could be Prod –> Dev1 –> Dev2 –> Dev3 –> Dev4—> NextRelease.


The process can be simplified by identifying the differences between Prod and Dev. This is actually simple to do via code. Below we identify changes of objects between the two. There are two statements (what is in Prod that is not in Dev, what is in Dev that is not in Prod, and what has changed (i.e. Table –> Updatable View).


Select name,type from dev.sys.objects EXCEPT Select name,type from prod.sys.objects Select name,type from prod.sys.objects EXCEPT Select name,type from dev.sys.objects

The above uses the EXCEPT to remove everything that is the same.  The same logic may be applied to other sys.tables, for example


Select name,type_desc,is_unique, ignore_dup_key, is_primary_key from dev.sys.indexes EXCEPT Select name,type_desc,is_unique, ignore_dup_key, is_primary_key from prod.sys.indexes Select name,type_desc,is_unique, ignore_dup_key, is_primary_key from prod.sys.indexes EXCEPT Select name,type_desc,is_unique, ignore_dup_key, is_primary_key from dev.sys.indexes

There compares can also be on done on other system objects and views:

Select Table_Name, Column_name, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length, Numeric_Precision, Numeric_Precision_Radix, Numeric_Scale, Collation_Name from prod.Information_Schema.Columns EXCEPT Select Table_Name, Column_name, Column_Default, Is_Nullable, Data_Type, Character_Maximum_Length, Numeric_Precision, Numeric_Precision_Radix, Numeric_Scale, Collation_Name FROM dev.Information_Schema.Columns


The one thing that is needs to be remembered is that some columns are expected to be different and thus should NOT be included in the compare, for example, db_id, object_id etc.


Once you know the difference, you know what needs to be altered in get the schema between prod and dev to match. 95% of the time this can be done by just finding the object in SSMS and right click,  with Drop and Create:


and a few ALTER Tables.


You still have the issue of data migration and the issue of not being able to immediately add a Not Null column or new Foreign Key constraint until after the data issues are resolved. You also may have sequencing issues (which can become very hairy when there are cyclical foreign keys)


In the next post, I will look at patterns for putting the above TSQL into a build or installation script.

Buy or Roll Your Own or Contract Out?

There are a variety of commercial tools available (see wikipedia). The problem with these tools is that there is often some art involved – some analysis that needs to be done by a human.  Rolling your own script depends on two items: do you have people with the skill set and experience to do it? and is this the best use of this resource? Contracting out means finding someone or some firm that has the experience and willing to take it on as an ad-hoc jobs with deadlines (when you need it, you need it promptly!). I should put a shameless plug in for myself here because I usually have enough spare hours each week to do this type of ad-hoc work – email me if you need such services. IMHO, the cheapest solution is the contract out – existing development resources can keep focus on more important things; there is no time lost on learning commercial tools and often better quality results because of a fresh-pair of eyes on the data and migration issues (i.e. less risk of assumptions that are false).

Friday, November 12, 2010

Finding out what changed between two versions of a SQL Server Database

This is just a quick note showing how you can compare the structure of two databases  easily.

These queries will spell out what (and their types)

-- One way Select name,type from MyDataBase_dev.sys.objects EXCEPT Select name,type from MyDataBase.sys.objects -- reverse direction Select name,type from MyDataBase.sys.objects EXCEPT Select name,type from MyDataBase_dev.sys.objects

The next item to check is usually columns, which follows a similar pattern:


Select Table_Name,Column_name from MyDataBase.Information_Schema.Columns EXCEPT Select Table_Name,Column_name from MyDataBase_Dev.Information_Schema.Columns Select Table_Name,Column_name from MyDataBase_Dev.Information_Schema.Columns EXCEPT Select Table_Name,Column_name from MyDataBase.Information_Schema.Columns

Many developers do not know about the very useful EXCEPT operator which excludes from one SELECT all matches in another SELECT. You can do this for the rest of the system catalogs (just do not include columns that are expected to be different between databases…)

Thursday, November 11, 2010

Writing Web Pages for a Client That You Can’t Test

Every time I get a C# exception on my ASP.NET web site running in Windows Azure, I have my web site code send me an email; this email includes the call stack, path information, and user agent string. I can use these email to find the problems with the code that caused the error. For a long time I have been ignoring the email (about 50 a day) and letting them stack up – being too busy with other projects. Lately, I have resolved to clean up all the code, so that I get less email and reduce the bugs in the web site. I thought this would lead to a better user experience for people visiting the site.


My typical process is to read the call stack, look at the path information and try to reproduce the issue to gain insight into how I have to fix it. Basically, a debugging process that I have in my toolkit no matter what the environment I am programming.

To get started I make sure that I am running the same browser that the user was using, be that Firefox, Internet Explorer or Google Chrome. I am limited in the number of browsers I can test, since I don’t have a full test lab, however just before deploying new code changes I test all the browsers I have to make sure the CSS renders the same and the JavaScript works across the board, and it all calls my server side code correctly.


As I worked through my stack of emails I started to notice an emerging trend – it was not my users that were having problems with my web pages it was the robots. Robots, Bots, or web crawlers are mainly search engine programs that traverse your web site and glean information to build results for searches on their sites.


The interesting thing about bots is that they don’t ever work as well as the browsers. Or it would appear, because I built my web site for browsers not bots – which means that the web site works best for users. The difference in thinking is that the bots aren’t working wrong; they are just not first class citizens on the web site because I coded the site for browsers. Think of it as a car that is built for adults, tested for adults that a child tries to drive. It sort of works, however it is hard to reach the car peddles and see over the dashboard at the same time if you are a kid. The same goes for the bots, they are trying to consume something that was tested for browsers.


The simple approach would be to ignore the errors from the bots, since they are not my target audience. In fact, I can restrict the bots from the web site altogether with a robots.txt file. However, my intent is to make a better user experience for my users – so does fixing the errors for the bots create a better user experience for people that are really human? The answer is yes – if the web crawlers can find the content on my site (without getting errors first) they can drive traffic to the site. This traffic driven from the search engines is real traffic from humans.


Now that I know I want to fix the errors from the bots, let’s take a look at my debugging technique. Key to process is simulating the environment by using the appropriate web browser; the client that made the request that caused the error. However, I have no access to the web crawlers (the client for the bots) and cannot simulate a request from that client. In fact I am not even sure how they handle the response (the outputted HTML), because a lot of how the web crawlers work is kept a secret; the interactions with the site are intellectual proprietary technology. All I have to go on is the HTTP standard, which dictates how the requests are made and some speculation about how the search engines works which falls within the black arts of Search Engine Optimization.

This leaves me in this limbo land of fixing the suspected bug without being able to reproduce. I have to deploying the fix live to see if it solves the web crawler’s problem all without breaking the human experience via the browsers. Sounds like it isn’t worth the effort right? No true, 97% of my human traffic comes from the search engines. So maybe I should be writing my web site for the bots.



Friday, November 5, 2010

The sales pitch to developers …

Last night I attended the local Linux Group meeting with a presentation on  a MS Access/OO.Base to Drupal presentation described as:


“Most people think of drupal as a website framework system. However, it can also serve effectively as a replacement to the forms/reports/tables system utilized by access and base. (as well as many other things, but we won't discuss that formally tonight) No prior knowledge of Drupal is required, and for those who do understand drupal, we will go through using cck and views, as well as a few other modules to develop a replacement for a small access database.”

Since this is a Windows Blog, it appears to be off target – however since it’s a emotionally-detached example to illustrate what is also seen with Windows stuff. As a FYI, I grew up on CP/M and ran Mark Williams Unix (Coherent) in the mid 1980’s, we have three Linux boxes in the house.


The first  thing that I struck me during the talk was that the speaker was laying smoke (as in a destroyer laying smoke to hide ships behind it) and did not know/research fundamentals. Some quotes:

  • “Base/Access are a flat file system” – wrong both are databases. A CSV file is a flat file system.
  • “Access can only handle 65K rows”,
    • Access can handle as many rows that will fit into 2GB of storage.
    • Excel 2007 can handle up to 1,000,000 rows

This always raised concerns because it means that the presenter is clearly not knowledgeable (and thus give one-sided perception and recitation of justification against the other product). Always ask detail questions and press for hard answers. If you present, do not wing it – do solid research with URLs from the officials authoritative sources to backup claims.


For example, I asked if Drupal supported RBAC. The prompt answer was that it does – fortunately we live in the age of the Internet, so I googled “Drupal RBAC” and the first hit was on the drupal.org site, it was still unanswered… The speaker appeared to be not informed (or did not know what RBAC is – often people think MAC or DAC is RBAC, it’s not. The National Institutes Of Standards and Technology [NIST.gov] has a clear statement of what RBAC is – I would love the speaker to point me to the RBAC implementation that Drupal has – as a FYI Linux has RBAC available at the operating system. See SELinux.


The speaker also claimed that it was “Secure” to the highest level. Again, the internet is there to verify information.  Well, the Drupal site has FIVE pages of Security Advisories. I then checked the authoritative source, the National Vulnerability Database [NVD], another Nist.Gov, site which listed 364 software flaws security advisories  for the search “Drupal”. Results from other searches:

  • “OpenOffice Base” – 24
  • “Excel” – 161
  • “Microsoft Access” -- 218

Now the speaker recommended doing an install from acquia which bundles it with a bunch of other software, two items I checked on NVD:

  • “PHP” – 18512 Software flaws known
  • “Apache” – 446

I always use NVD to get an objective evaluation of how secure a software product is. It’s your taxpayer dollars doing good work.  Bottom line, it is far less secure.


Part of the same sales pitch was that the Obama White House used it. I google “drupal whitehouse” and the first hit was a Feb 2009 announcement that pointed to http://recovery.gov Well, when I went to the site, I saw the site currently deliver pages with “.aspx” – AspNet, not drupal. Evidently Drupal was yanked.  I did find a Whitehouse announcement about Drupal from April 2010.  There was also claims that the DOD used Drupal in it’s line of business– I was unable to find any significant google items confirming this is happening, there is an announcement of  a pilot study for social networking.  The speaker hinted that it was being used for DOD secure information projects….


Bottom line, I would not port from Base or Access to Drupal: There is nothing clearly gained and a lot of clear losses:

  • Lost of a relational database structure (native Drupal storage is not a RDBMS)
  • Steeper learning curves – increased cost of business
    • Easier to find general (cheap) office staff that know how to use Access or Base – the two are very similar so if you know one, you know the other – this is not the case with Drupal.
  • You need a lot more IT expertise to be secure:
    • Drupal uses Apache – if the PC is connected to the internet and you don’t have all of the firewalls and gateways properly configured, you may be hacked.
      • Classic issue is not changing default passwords…  or using weak passwords
    • A lot of software flaws

To me, for most “alternative” approaches there are three dimensions of concern:

  • Security – protecting corporate data
  • Cost to maintain – ongoing expense
    • Learning curve
    • Availability of up-to-speed individual today and in 7 years  Business systems should last at least 7 years without needing a refactor.
    • Often magnitudes above original licensing cost (if any).
  • Lock-in Degree – upgrade path
    • How easy is it to move to upscale platforms? Access –> SQL Server Express; BASE –> MySQL
    • Products die – even very very good ones. What happens if this happens. Drupal could be hit with an IP infringement and the open source project ordered removed from the web. Microsoft kill stuff, I still have copies of Microsoft FORTRAN, Borland Turbo Prolog,  and Microsoft PASCAL around. What is the recovery plan?

Wednesday, November 3, 2010

Who should be in a startup team?

Often people approach a startup with a naive attitude that they can succeed by just doing what they want to do. The reality is that there are many roles that need to occur to have a reasonable chance for success. In this post, I will summarize a lean team and the roles that they may need to implement.

  • Idea person – who should function as
    • Domain Expert
    • Tester – does the product match their vision?
    • Handling Sales Calls  (because they speak the client language – hopefully)
  • Product Manager – the decision maker for the development team
    • Researcher
    • Business case writer
    • Specification writer
    • Project planner
    • Documentation writer
    • Tester
    • Code reviewer
    • Liaison
    • Handling Sales calls
  • Developer Team
    • A pair of developers
      • Team Programming'
      • Redundancy if one leaves


For a short period you may wish to involved a “shirt-sleeve architect”, an architect that is current on technology and likely a good developer with heavy experience. Their role would ideally be:

  • Overall design based on the specification
    • Modify it to match the skill sets of the developers
    • You are not is a situation where you can hired developers that can handle aggressive sophisticated designs
    • You need a foil for developers advocating technologies that they want to learn, instead of technologies that they are experienced with. This is risk management.
  • Design the database (if needed) – OOP and Database Design are two different cultures
    • Script the database (with triggers, indices and Referential Integrity)
    • Decide if some OOP data access can be used (simpler databases) or if stored procedures should be mandated.


The reason that I reduce the involvement of the Idea person (apart from testing) is that they will often not understand implementation issues. There are exceptions – but think of building a house, the owner may think that they know how to do wiring or plumbing … but it they do it, the odds are that it will fail inspection and the work will need to be redone (after ripping out the drywall). The owner of the idea tend to be possessive and ends up being too close emotionally on one side, and too far from best (or required) practices on the other side.


You need to give the Idea person a lot of pats on the back and make sure they feel useful (and ideally be useful) but the odds that they will with the best of intentions distract resources and obfuscate a good implementation. The product manager role is too keep him or her happy and be a buffer protecting the developers from randomization.


At some point you may need to add a tester – this person roles should include:

  • Testing
  • Support
  • Handling Support Calls
  • Some maintenance coding and tuning

The bottom line is that there is much work to be done. And the work needs to be done well. Odds of success goes down if  things are put off because suddenly everything is needed and there is no time…. so junk is produced and the customer is left with the impression of a sloppy company (a nice way to loose sales…).

So you want to do a startup – the “F” projects

“F” is for friendly. This type of startup addresses the frequent problem of insufficient domain knowledge (outside of coding and possible “day job”). “Day Job” domain knowledge often run risks of conflict of interest, or if your employment contract is hard-nose, the startup could become claimed (with no remuneration) by your day job employer.


The basis of the “F” project is to be friendly to the local business community. Via local business groups (for example TAG in Whatcom county), you indicate you (or you as a part of a group) are willing to do pro-bono work for smaller business in the area. This is a fine line, the work should not be doing for free what they would likely pay someone to do (like create a web site) – that is bad karma. What you want to do is something that fills a gap.


I will give an example from my experience, when I meet my wife she was production manager for a firm that reproduce by hand figurines, C.Alan Johnson.  There was a dozen employees – all skilled china painters etc. Their market was cruise ships to Alaska.  They had some business problems:

  1. Difficulty knowing what to produce (each figure took time to case, paint and finish)
  2. Not having stock on hand to ship lost sales because the sale season was short.
  3. At the end of year, they had to pay tax on finished inventory.

The ideal solution was to be able to forecast sales on each piece and end the year with almost no finished inventory (because there was a match between production and sales).


I ended-up writing three components that talked to their existing system:

  • An EOQ estimate for figurine batch size
    • factoring in the number of pieces that can be put into a kiln at a time
    • available storage and work space
    • profit per piece
  • A just in time sales forecasting system that
    • did cluster analysis of prior sales
    • generated a weekly (or daily): these are the pieces we should cast today
  • Who get what first when there was a shortage
    • Some customers paid promptly, some did not but had larger sales

The solutions above were cheap solutions with major financial impact.


There are some critical caveats with “F” project lest you end up with a “F*ck-*p”.  (Coding)Developers have this nasty trait of loving to reinvent the wheel.  The business owner should get a seasoned product manager involved – there are at least two people in Skagit/Whatcom county that are willing to do pro-bono or equity-stake work: myself and Andrea Williams.


The roles of the product manager are many, the critical ones are:

  1. Can the need be satisfied with an off-the-shelf product?
    1. Is it just a matter of paying $500 and training?
  2. Resolving what the customer actually needs --
    1. What they say may not be what they mean, or
    2. Difference of vocabulary between developers and business owner result in a major language problem
  3. Writing up a specification in “developer speak”
  4. Monitoring the work to make sure it’s on schedule and as intended
  5. Resolve issues raised by developer (often a lack of domain knowledge).
  6. Determine if there is a potential (at least niche) market for the product.
  7. Be the decision making knowledgeable-authority (you need one).

The Startup Model

The model is this:

  • The startup team works for free, a flat fee (on successful completion – satisfaction guaranteed) or greatly reduced hourly rate.
  • Regardless of the remuneration – the startup team retains all rights to the software produced.
  • The business receives a non-expiring, non-transferable  license to the software.

You have a user to give the product a recommendation, you have acquired domain knowledge and had an extended experience learning the “customer’s language”.


If you are an experienced product manager, a developer or whatever --- and are interested in this approach, please email me or post a comment indicating your interest. TAG is interested in building up a resource list. A team consisting of one product manager and two developers (think paired-coding as in Agile practices) is likely the critical mass to have a reasonable chance of success.  Ideally, the product manager should be reasonable coder so they can also serve as a code reviewer.