Subquery in the VALUES clause
Experimentally, your code worked for me on both 10.50.1600 (2008 R2 RTM) Developer and 10.0.4000 (2008 SP2) Express. However, it failed against 9.0.5057 (2005 SP4 + hotfix). So, I dug into the documentation.

The 2005 version says this about expressions in the VALUES clause (emphasis mine):


  expression
  
  Is a constant, a variable, or an expression. The expression cannot contain a SELECT or EXECUTE statement.


while the 2008 and higher version looks like this:


  expression
  
  Is a constant, a variable, or an expression. The expression cannot contain an EXECUTE statement.




From this evidence, I can only assume that the server version where you're executing these queries is not what you think it is.