Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
)
share|improve this question
1  
Can you add real code please? Simplified code is almost always useless and it saves "I can't do that because...." later –  gbn Aug 24 '11 at 19:03
    
The real tables and SQL are equivalent, just with more columns. –  Nolan Aug 24 '11 at 19:14
3  
That doesn't help: What is the real INSERT table? Is there a WHERE? A GROUP BY to deal with the "more columns"? help us to help you. –  gbn Aug 24 '11 at 19:16
    
There are no clauses. This is the production statement, with less columns and different column names. –  Nolan Aug 24 '11 at 19:26
2  
I'm inclined to agree with @gbn. Something must be missing from the edited code. –  Mark Storey-Smith Aug 24 '11 at 22:47

4 Answers 4

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.

share|improve this answer
    
The documentation for coalesce says it returns a single value: msdn.microsoft.com/en-us/library/ms190349.aspx Thanks for the identity tip, but this is being run from a single update utility while the main application is offline. The error isn't a concurrent id problem, the statement won't execute at all on the client system, but works fine for me. –  Nolan Aug 24 '11 at 19:11
1  
@Noalan: I asked "Are you 100% the ... gives one row?". It is nothing to with the COALESCE: you are scanning a table which can have multiple rows and this isn't real code –  gbn Aug 24 '11 at 19:13
    
To extent I understand SQL, yes. Max aggregates into a single row with a single column when one or many rows are present in foo, and the outer coalesce selects 0 over the returned null when the table is empty. The only difference between this and the real code are the number of columns in the tables, and the column names. –  Nolan Aug 24 '11 at 19:24

Do they have the database compatibility mode set correctly? I'd check all the database options and compare them to what you expect from your systems.

share|improve this answer
    
It runs OK for me on 80, 90, 100 on an R2 install. Was my first thought. –  gbn Aug 24 '11 at 19:17
1  
Which compatibility mode setting are you referring to specifically? I don't have direct access to the system in question because of legal requirements on access control. –  Nolan Aug 24 '11 at 19:18
    
The database compatibility mode. Failing that I think we'll need more actual code to see what's going on. –  mrdenny Aug 24 '11 at 19:26

Wow, I have never used INSERT with a sub-SELECT in that way. This is what I would have done:

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

That is, after all, the exact purpose for which that statement was made!

Or, if you absolutely demand to use the VALUES clause, you could try:

DECLARE @MaxId INT
SELECT 
    @MaxId = coalesce(max(foo_id),0) + 1
FROM foo

INSERT INTO foo 
VALUES (
    @MaxId
    2,
    'foo two')

I've never seen the "subqueries are not allowed in t his context" error, but I always use one of those two formats above (primarily the first on).


Regarding why this error is happening: SQL Server thinks that the sub-select will return more than one row. Whether it will or not is beside the point. When SQL Server tries to parse the T-SQL for this statement, it is expecting multiple rows.

One more possibility is that you could add "TOP 1" to let SQL Server know that this will return only one row. Personally, though, I would rather have the strange statement fixed than see a hacky workaround put in place. But that's just my opinion.

share|improve this answer
    
thanks, we actually sent something like the latter as a manual workaround to the client yesterday. The question still at hand is why did the insert with subselect fail in the first place? It works fine for us, on the same version as the failing client server (SQL Server 2008 R2). –  Nolan Aug 25 '11 at 13:31

How about this:

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

This should also work:

insert into foo
select coalesce(max(foo_id),0) + 1, 2,'foo two' from foo
share|improve this answer
    
I'll ask the client to try that manually out of curiosity, but the max() should already keep the statement from returning more than one row. –  Nolan Aug 24 '11 at 21:18
    
You're right. I missed the max completely.--Edited. –  Kenneth Aug 24 '11 at 21:21
    
I can try using insert into..select instead, but I was hoping to understand why something that works on multiple installs here fails for our client with the same SQL Server version. –  Nolan Aug 24 '11 at 21:42

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.