Welcome back to another blog post on the saga of BizTalk Server errors and warnings, causes, and solutions. This morning started off excitingly as I had to go investigate an issue in one of our current BizTalk Server projects. The curious thing about this error, and I encounter many types of errors using the SQL adapter, is that I had never encountered it.
System.Data.SqlClient.SqlException (0x80131904): Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
Server stack trace:
at System.Runtime.AsyncResult.End[TAsyncResult](IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.SendAsyncResult.End(SendAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndCall(String action, Object[] outs, IAsyncResult result)
at System.ServiceModel.Channels.ServiceChannel.EndRequest(IAsyncResult result)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
at System.ServiceModel.Channels.IRequestChannel.EndRequest(IAsyncResult result)
at Microsoft.BizTalk.Adapter.Wcf.Runtime.WcfClient`2.RequestCallback(IAsyncResult
At first glance, this does not appear to be a BizTalk Server issue but rather a SQL-side issue.
📝 One-Minute Brief
When working with the BizTalk Server WCF‑SQL Adapter, you may encounter the error “Subquery returned more than 1 value,” which can block message processing and cause SQL execution failures. This post explains why this error occurs, how it relates to incorrect SQL queries or adapter configurations, and how to fix it to ensure reliable database interactions in BizTalk integrations.
Cause
This is indeed an SQL Server issue, and you cannot do anything from the BizTalk Server side.
If you get the error message “Subquery returned more than 1 value” in SQL Server, it’s because you’re using a subquery that returns more than one value in a context where this isn’t allowed. And, of course, the solution to this will depend on what you’re trying to do in the query.
Solution
As a BizTalk Server developer or administrator, you need to speak with the SQL team to fix the SQL query.
As an example, if you have a query that faces this issue, like this demo sample:
SELECT * FROM MyTable
WHERE myColumn = (SELECT myOtherColumn FROM MyOtherTable);
Again, the solution to this will depend on what you’re trying to do in the query, but these are some options for resolving this issue:
Using the IN operator
SELECT * FROM MyTable
WHERE myColumn IN (SELECT myOtherColumn FROM MyOtherTable)
Using the EXIST operator
SELECT * FROM MyTable t
WHERE EXISTS IN (SELECT * FROM MyOtherTable ot WHERE ot.myOtherColumn = t.myColumn);
Keeping the = operator and changing the subquery
SELECT * FROM MyTable
WHERE myColumn = (SELECT myOtherColumn FROM MyOtherTable WHERE column = something);
I hope you find this helpful! If you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!
