Skip to content

Est. 2011·Microsoft Partner 7033487·Delivery under 3 min·Support 7 days a week

Your vault is empty.

Free Fix 512

Error 512: Subquery Returned More Than One Value, and Related Query Failures

11 min read Updated October 4, 2026 SQL Server

Fix it now

A subquery used where a single value was expected returned more than one row, so SQL Server terminated the statement rather than pick one. The lookup data almost always changed; the query did not.

Run this against the lookup table the subquery reads, with your own key column

SELECT CustomerId, COUNT(*) AS matches
FROM dbo.Lookup
GROUP BY CustomerId
HAVING COUNT(*) > 1;
  1. Find the scalar subquery. It is the one in the select list, or on the right of an =, < or >, wrapped in its own parentheses.
  2. Run that subquery alone with the correlation values filled in. Two or more rows back and you have found it.
  3. Decide what you actually want. If exactly one row should match, the lookup data is wrong and needs a unique constraint. If several legitimately match, the query needs rewriting.
  4. Rewrite it as a join or an OUTER APPLY so the extra rows are visible, or collapse them deliberately with MAX, MIN or STRING_AGG.
  5. Only reach for TOP 1 with an ORDER BY that makes the choice deterministic.

SET @x = (SELECT ...) raises 512. SELECT @x = col FROM ... assigns from every row in turn and leaves the variable holding whichever row the plan produced last, with no error at all.

If the subquery now returns one row the statement runs. If you are looking at 2812, 217, 6522, 9420 or 1934 instead, those are module failures rather than expression failures and the next section separates them.

Why it happens

The published text of 512 is precise about the shape it objects to: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. A scalar subquery is an expression, and an expression has to produce exactly one value. The optimiser builds a plan containing an assertion on the row count, and when the assertion fails at run time the statement is terminated. That is the correct behaviour. Silently taking the first row would make the result depend on the plan, the indexes and the physical order of the data, and it would change without warning.

This is also why the error appears out of nowhere on code that has run for months. The plan was always capable of failing; it simply never had to, because until now every correlated lookup matched exactly one row. One duplicate in a reference table is enough.

The other five codes are module failures. 2812 is Could not find stored procedure, nearly always database context or a missing schema prefix. 217 is the nesting level limit, documented as 32. 6522 is a .NET exception escaping a CLR routine. 9420 is narrower than it looks – the published message is XML parsing: line, character, illegal xml character, so it is one character the parser will not accept, at a position it gives you. 1934 is the session’s SET options being wrong for an object the statement touches.

The lookup table gained a duplicate

You have this one if The subquery on its own returns two rows for one correlation value, and the reference data was loaded or edited recently.

  1. Confirm it with the grouping query above, using the correlation key.
  2. If the key should be unique, clean the rows and then add a unique constraint so the duplicate cannot come back.
  3. If duplicates are legitimate, the query is wrong: aggregate them, or return multiple rows through a join and let the caller deal with them.

The subquery lost its correlation predicate

You have this one if Run alone, the subquery returns the whole lookup table, because nothing ties it to the outer row.

  1. Add the correlation: the inner query must reference the outer alias, for example WHERE l.CustomerId = c.CustomerId.
  2. Alias every table in both queries and qualify every column.
  3. Consider OUTER APPLY, which makes the relationship explicit and lets you take several columns from the lookup without repeating the subquery.

An unqualified inner column name that also exists in the outer query resolves to the outer one silently. Qualifying names is the only reliable defence, and it is the reason this fault survives code review.

The procedure cannot be resolved

You have this one if 2812 rather than 512, usually straight after a deployment into a different environment.

  1. Qualify the call and check the database context: EXEC dbo.YourProc rather than EXEC YourProc.
  2. Confirm the caller can see the object. Metadata is filtered by permission, so a caller with no rights on it gets a not-found error rather than a permission error.
  3. Do not name your own procedures with an sp_ prefix; those are looked for in master first.

Nesting hit the 32-level limit

You have this one if 217, in a procedure that calls itself or a trigger that updates the table that fired it.

  1. Read @@NESTLEVEL at the top of the module to see how deep you already are. The documented maximum is 32 and exceeding it terminates the transaction.
  2. For triggers, look for a cycle: A updates B, whose trigger updates A. That is a design fault, not a setting to change.
  3. A recursive common table expression has its own separate MAXRECURSION limit and reports a different error. Do not conflate the two.

The session’s SET options are wrong for the object

You have this one if 1934 on a write to a table carrying an indexed view, a filtered index, an index on a computed column, or an XML or spatial index.

  1. Read what the session is using: SELECT quoted_identifier, arithabort, ansi_nulls, ansi_warnings, ansi_padding, concat_null_yields_null FROM sys.dm_exec_sessions WHERE session_id = @@SPID;
  2. Set the documented six to ON – ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER – and NUMERIC_ROUNDABORT to OFF.
  3. Fix it at the connection. An application, driver or command line tool connecting with different defaults is the usual source, and scattering SET statements through the code only hides it.

The same wrong options do something quieter to reads. A SELECT is processed as if the indexes on computed columns and the indexed views did not exist, so nothing fails and the query simply gets slower.

Full reference

Four ways to make a multi-row lookup safe

Rewrite When it is right What it costs
Join to the lookup Several matches are legitimate and the caller should see them all The outer row count changes, so check what the caller expects
OUTER APPLY with TOP 1 and ORDER BY You want one specific row and can say which Nothing, provided the ORDER BY is deterministic
Aggregate with MAX, MIN or STRING_AGG Any of the matches will do, or you want them combined Loses the ability to say which row it came from
Unique constraint on the lookup key Exactly one row should ever match A load that used to succeed will now fail at the point the duplicate arrives, which is the point

SET and SELECT do not fail the same way

DECLARE @x int;

-- raises 512 when more than one row matches
SET @x = (SELECT Amount FROM dbo.Lookup WHERE CustomerId = 7);

-- no error; @x holds whichever row the plan produced last
SELECT @x = Amount FROM dbo.Lookup WHERE CustomerId = 7;

The second form is the more dangerous of the two, because nothing goes wrong. Prefer SET wherever exactly one row is expected, and treat any SELECT @var = against a set that could return several rows as a defect waiting for a duplicate.

The module errors, side by side

Error Published message First thing to check
512 Subquery returned more than 1 value… Run the subquery alone with the correlation filled in
2812 Could not find stored procedure Database context, then the schema prefix, then permissions
217 Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32) @@NESTLEVEL, then trigger cycles
6522 A .NET Framework error occurred during execution of user-defined routine or aggregate The nested .NET exception text, which is the real error
9420 XML parsing: line, character, illegal xml character The character at that position in the XML, usually a control character from an upstream export
1934 …failed because the following SET options have incorrect settings sys.dm_exec_sessions for the connection that failed, not your query window

Why 1934 only affects some connections

The required SET options are not a server setting; they belong to the session, and every driver and tool has its own defaults. SQL Server Management Studio sets ARITHABORT ON, which is why the same statement runs in a query window and fails from the application. When the options are wrong, Microsoft documents that INSERT, UPDATE, DELETE, DBCC CHECKDB and DBCC CHECKTABLE all fail against an indexed view or a table with an index on a computed column. Fix the connection string or the driver’s defaults once, rather than patching each module.

Preventing the next one

  • Put unique constraints on the keys your scalar subqueries correlate on. A constraint turns a future 512 into a failure at load time, where it belongs.
  • Qualify every column with a table alias, in inner and outer queries alike.
  • Prefer OUTER APPLY to a repeated scalar subquery when you need more than one column from the lookup – it also stops the optimiser evaluating the same subquery several times.
  • In deployment scripts, schema-qualify every EXEC so the same script cannot resolve differently in another database.
  • Where CLR routines are involved, log the inner .NET exception; 6522 on its own tells you nothing.

Every code this article covers

Code What it points at Source
512 Subquery returned more than 1 value. Not permitted after a comparison operator or where the subquery is used as an expression Microsoft Learn
2812 Could not find stored procedure. The name could not be resolved in the current context Microsoft Learn
217 Maximum stored procedure, function, trigger, or view nesting level exceeded. The documented limit is 32 Microsoft Learn
6522 A .NET Framework error occurred during execution of a user-defined routine or aggregate; the nested exception carries the detail Microsoft Learn
9420 XML parsing: line, character, illegal xml character. A character the XML parser will not accept, at the position given Microsoft Learn
1934 The statement failed because the listed SET options have incorrect settings for an object it touches Microsoft Learn

Confirm the fix worked

  1. The previously failing statement completes against the current data.
  2. The rewritten subquery, run alone for a correlation value that used to break it, returns exactly one row.
  3. Any unique constraint you added is enabled and trusted.
  4. For a 1934 fix, the application reconnects and sys.dm_exec_sessions reports the six required options ON and NUMERIC_ROUNDABORT OFF for that session.
  5. A deliberate duplicate inserted into a test copy is now rejected by the constraint rather than reappearing as 512.

Questions people ask about this

Is TOP 1 an acceptable fix?

Only with an ORDER BY that makes the choice deterministic and documented. Without one you have swapped an error for a result that changes when the plan changes, which is far harder to notice.

Why did this start failing today?

Because the data changed. The query always assumed one matching row, and the assumption held until a duplicate arrived. Look at what was loaded or edited in the lookup table.

Does it cost anything to fix?

No. These are query, data and connection settings. There is no edition in which a scalar subquery is allowed to return two rows.

What is the difference between SET and SELECT for assigning a variable?

SET enforces the single-value rule and raises 512 when it is broken. SELECT @x = col assigns from every row in turn and leaves the variable holding the last one, with no error. Prefer SET when exactly one row is expected.

Can I raise the 32-level nesting limit?

No. Microsoft documents 32 as the maximum and says the transaction is terminated when it is exceeded. If you are close to it, the shape of the call chain is the problem.

Related error codes

Was this article helpful?

Your feedback helps us improve our documentation.

Related articles

Free Fix Error 18452 and SSPI Handshake Failures: Kerberos, SPNs and Trust License Error Error 18752: Only One Log Reader Agent Can Connect to the Database Free Fix Error 605: Page Belongs to the Wrong Allocation Unit – Corruption or Dirty Read Free Fix Error 14274: Jobs Break After a Server Rename or an MSX Move
← Back to Knowledge Base