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' )