Be the Next Microsoft Employee – Fourth Challenge: There is no I in Team - or the right answer, either

The Buckster

The fourth challenge is live! Watch it here: Read on to find out more about the technical side of the challenge, and how you can enter to win free books and be entered to win a new laptop!

This week I was joined this week by Dandy Weyn, aka “The Belgian” Sr. Technical Product Marketing Manager here at Microsoft. We were fortunate to have him along on this ride… this challenge was full of drama. And it shouldn’t have been – read on to find out more…

Challenge Background

I was actually afraid of the simplicity of this challenge. Here’s the text of what we gave the contestants:


We got an e-mail from a developer today. We need you to solve it - but this time, as a group. It reads:

"I'm using SQL Azure Data Sync to keep two tables in sync. I have to sync only one table, and the table on SQL Azure has a trigger, but the table on my local SQL Server doesn't have this trigger (because apart from the shared one table, the two databases have nothing in common).

When I try to sync it fails with this error:


Sync failed with the exception "GetStatus failed with exception:

Sync worker failed, checked by GetStatus method.

Failure details:

An unexpected error occurred when applying batch file C:\Resources\directory\61e9d741c80a47b4ae195f8.NTierSyncServiceWorkerRole.LS1\DSS_syncjobmxdkfaopaery\a0e8b11a-a08.batch.

See the inner exception for more details.

Inner exception:

Failed to execute the command 'BulkInsertCommand' for table 'dbo.tblUser';

the transaction was rolled back. Ensure that the command syntax is correct.

Inner exception:

SqlException Error Code: -2146232060 - SqlError Number:512,

Message: Subquery returned more than 1 value.

This is not permitted when the subquery follows =, !=, <, <= , >, >=

or when the subquery is used as an expression.

SqlError Number:3621,

Message: The statement has been terminated.

For more information, provide tracing id ‘bb8e3787-27c1-4b7e-9a26-6db2ff6724d3’ to customer support.


When I disable my trigger, the sync works! 

My trigger:

    CREATE TRIGGER [dbo].[UpdateUsersTable]

    ON [dbo].[tblUser]


     INSERT INTO  [dbo].[Users] ([userID], [PartnerOrganizationId])

    VALUES ((select [userID] from inserted), (select [country] from inserted))


What do you think is happening?"

Did you see it? (Post the answer as a comment if you think you do – and post the fix as well) Actually, the answer is right there in the question. But the twist was this – we had the contestants work on the challenge as a team. Interesting things happen when more than one person works on a problem….

Challenge Recap

The contestants have “day jobs” where they have real-live responsibilities. One of the contestants had a production issue to take care of and had worked late into the evening and even the morning of this challenge – and came out to the challenge as well. This is a really talented, dedicated group of folks. I’ve heard from lots of you who have told me “I can’t imagine the stress of an interview like this, with cameras rolling and multiple people picking apart my solution.” I agree – I designed these challenges, and I can tell you that they aren’t easy. They may not look to have rhyme or reason at first, but I hope in these explanations you find that we really thought them through to learn more about the candidates.

And it’s not just a technical challenge – we have a Human Resources judge (Tim) who’s been watching carefully to see how the contestants interacted. In the last challenge the contestants worked as a team even when we didn’t ask them to – which was a great breakthrough for them. Although they ended up designing their solutions individually, they were informed by the work they did as a group prior.

Today was different. We asked the team to work on the challenge together. I didn’t tell them what that meant – and they just jumped right in (mistake number 1). They should have decided who would do what, and how. Who knew the answer. Who *thought* they knew the answer. Who wasn’t sure at all. And….who was the best presenter. They had been presenting individually until now…they didn’t consider who would speak for the group (mistake number 2). The best presenter had won the previous challenge – but groupthink got in the way. More on that in a moment.

My fear was that the answer to this puzzle was so obvious it would be solved in seconds. In fact, we had another challenge ready for them to work on so we wouldn’t have a boring challenge!

But that concern wasn’t necessary. Although it isn’t shown in the clip, one of the contestants got the answer in under 30 seconds. But the group didn’t listen (mistake 3) and just kept going in different directions, even thinking that SQL Azure doesn’t support Triggers (it does, just fine).  The person with the right answer kept trying to get his point across – a little – but when he couldn’t make headway, he joined in the group and allowed his answer to be ignored (mistake 4).

Time ran out. No answer was clear. I asked for a leader to speak for the group (who hadn’t even decided a single answer – mistake 5) and the team used – get ready for it – “Rock, Paper Scissors” to decide! Big number 6! Bad Team! Bad! :)

And then they got the wrong answer (did you find the right answer yet?), presented it poorly, and didn’t even agree in the end on the solution. Fail, fail, fail. The other judges and I were NOT happy. Did that come through on the video? :)

Working in groups is a part of life here at Microsoft, and it's very important to me. You have to be able to do things on your own, of course, but the products and solutions we have span multiple versions, countries, laws, regulations, industries and so on. We need teams to make sure we do these things right. You have to be able to negotiate an idea – never attacking a person, but always attacking the logic of a decision.


In the end, we failed the entire team. There was no winner, since they got it wrong, debated it wrong and presented it wrong. We did award a technical winner to one contestant, since he got the right answer, but I faulted him still for not being able to get his ideas across.

In life, and especially here at Microsoft, you run into people in groups who may not want to hear your answer. But if you’re right – and you *know* you’re right – you have to find a way. Argue, be nice, show, tell, dance, do something. You’ve got to be able to get yourself heard – in fact, the more convinced you are that you are right, the harder you have to try. Our customers deserve it.

Your Challenge and Last Week’s Winners

Agree? Disagree? It’s your turn to have a say. Design a challenge that has a group of people create a solution for something. Don’t make it too vague, and don’t make it too easy – after all, we want to see the group form together, come up with ideas, and pick a winner. Describe how your challenge would do that. Describe possible stress points, and remember, there doesn’t have to be a single “right” answer like we had in this challenge. We had only 30 minutes for them, so we had to give them a way to “win” in that time.

Last week’s winners: Joseph Hagan, Martin Cox and Ryan Roper - Congratulations! Just one more submission, and you - along with anyone else that submits - are well on your way to that laptop…

  • RyanRoper
    | |

    The answer is simple, the problem is when the insert triggering the trigger has multiple rows. The subqueries are returning more than one value. All you have to do is turn the two subqueries into a single subquery returning two columns and then the insert should work fine for both one row or multiple rows.

  • RyanRoper
    | |

    To clarify:

       CREATE TRIGGER [dbo].[UpdateUsersTable]

       ON [dbo].[tblUser]


        INSERT INTO  [dbo].[Users] ([userID], [PartnerOrganizationId])

       select [userID], [country]  from inserted


  • The Buckster
    | |

    Ryan- Correct! And to put a fine point on it, it will *sometimes* work (if for instance it does only return one row) which makes it even worse. The bigger issue is that this trigger wouldn't work anywhere - on-premises or in the cloud. the rest of the error was all noise

    What's interesting is that one contestant saw that right away - but in the pressure of the moment, with a strange environment, judges walking around, and the teammates talking, it was easy to get it wrong. Isn't that the way the real world works, too? :)

  • Mercwrought
    | |

    Haven’t had the chance to watch the video yet but, Buck you bring up something I have been questioning about all of these challenges is that the pressure is the main difficulty. How hectic are the contestants primary work environment? I don’t consider the conditions where I work as anything extraordinary but dealing with live systems and having to be up as close to 100% of the time is normal. Having a room of people stare at you or watch over your shoulder while you type queries is not uncommon (not sure why they are watching it’s not like they understand what they are reading, I can only assume the train wreck principle).

  • The Buckster
    | |

    Mercwrought - to be sure, it's not a constant, minute by minute pressure, but it certainly is here. I'm sure other tech giants face this as well, and you might even at your location. If our systems are down, if we make a mistake, it hits the papers, and people scream as loudly as they can. So yes, pressure is a big part of what we deal with here at Microsoft.

    But I don't want to represent it as a over-stressed environment. Because we try and work at a high level, the systems are normally up, doing what they are supposed to, and helping our customers do what they do. It's what makes us go. Teams are there to help you do that - while you are expected to be an expert at what you do, you have a team with you that listens, participates, and helps. That's why this challenge is so important. We don't have a lot of room for "cowboy IT" where one person makes decisions in a vacuum. We work together to solve problems.

    The contestants have actually gelled together quite a bit during the short period they've been together. I would like to have seen them do a few things different in this challenge:

    - Set a timer

    - Appoint a speaker

    - Divide the problem to each person

    - Agree on an approach

    - Present the approach for solving the problem

    If they had, the pressure is actually reduced - which is the key. No one works well stressed all the time, so you put policies and systems into place that help you to avoid it as much as possible.

    30 minutes - that's really quick. Unless you think about some large web site you know and depend on being down for that long. Hence the need for planning, backups, and so on.

    Thanks for watching, and for that comment!

  • RyanRoper
    | |

    Yes, I should have been more clear. That's what I meant by " the problem is when the insert triggering the trigger has multiple rows". I meant, when the insert statement triggering the trigger contains multiple rows to be inserted into the target table is when it causes the trigger to fail, because of the subquery returning multiple rows.

    With complicated verbiage and multiple meanings it only stresses the need to be exact in what you are trying to get across :)

    Thanks again for this great program and contest!

  • Dancar
    | |

    It's a tough call sometimes - be assertive with your answer to the problem and risk your reputation if your answer turns out you're wrong, or keep quiet while your team marches down the wrong road if you're right.  It helps when the company culture allows one to be wrong once in a while.   Thanks for the interesting challege.  Not all challenges in real-life situations are technical.

  • The Buckster
    | |

    Dancar - you are correct. If you're wrong, being insistent can be a very bad thing. :) But we do allow for mistakes - just not with customer systems. Those have to be exact.

    Thanks for watching, and for posting!

  • Mercwrought
    | |

    Ok, watched the episode and I see that the challenge was so much about working as a team. Loved the use of red hearings, I try to include some in my entries because so many times the answer is simple but we get caught up on inconsequential details.

    It was clear that Alex just about had the answer but their team work faltered and took the prize just out of reach. Sometimes it is to convince others that the idea is theirs and give them that ownership that way they are behind it. I remember having to do that while coding software changes. Also good use of "cowboy IT" I think I just referred to myself mockingly that way the other day  and I know they are going to read  that and have a good laugh.

    Buck, if you could clarify for the play @home challenge is this the last week or will there be a week 5 challenge?

  • The Buckster
    | |

    Mecwrought - there will be a 5th week challenge, and then after that I'll announce the winner and then the overall winner of the laptop after. :)

  • RyanRoper
    | |

    Buckster, it doesn't say in the challenge paragraph but does our challenge need to also conform to a 30 minute time limit or are we given more reign? It's implied that we aren't but I wanted to make sure.

  • The Buckster
    | |

    No - time limit is up to you. :)

  • Stacy Luciani
    | |

    What hosed me up was the fact that you can't add a trigger it doesn't work.