Welcome back to another blog post on the saga of BizTalk Serve 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.
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.
Cause
This is indeed an SQL Server issue, and you cannot do anything from the BizTalk Server side.
If you get this 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 scenario where this is not 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!
