Cannot reproduce client SQL 2008R2 error: subqueries are not allowed

EDIT: My question is not "how do I work around the failure?", my question is "why is the failure occurring?"

We have a client that reports error "1046 subqueries are not allowed in this context. Only scalar expressions are allowed" for SQL Server 2008 R2. We cannot reproduce in house. Is there a server setting or database configuration option that can cause this?

-- Simplification of tables looks like this

create table foo_type(
    foo_type_id int not null,
    foo_type_name nvarchar(100) not null,
    primary key (foo_type_id)
)

insert into foo_type values (1, 'type one')
insert into foo_type values (2, 'type two')

create table foo(
    foo_id int not null,
    foo_type_id int not null,
    foo_name nvarchar(100) not null,
    primary key (foo_id),
    constraint fk_foo_to_foo_type
    foreign key (foo_type_id) references foo_type
)

insert into foo values (11, 1, 'foo one')

-- and the mysteriously failing insert

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

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.

StackExchange Database Administrators Q#5004, answer score: 3