Cannot reproduce client SQL 2008R2 error: subqueries are not allowed
Are you 100% sure the (select coalesce(max(foo_id),0) + 1 from foo) gives one row? You said it was simplified code so this may not be the case. 

This should be an IDENTITY column because 2 processes can insert the same row and you don't require an aggregate over the current data.

Anyway, how to fix it. Change to this (MAX without GROUP BY always gives one row)

insert into foo values (
select
    coalesce(max(foo_id),0) + 1,
    2,
    'foo two'
from
    foo WITH (ROWLOCK, UPDLOCK, HOLDLOCK)


To test on the client, add this immediately after

SELECT @@ROWCOUNT


Note: the hints reduce (not remove) the chances of the same value being inserted.