Lesson Learned #229: Hands-On-Labs - Connection is busy with results for another command

We used to have cases where our customer faced the following error messages like following: "ERROR [HY000] [Microsoft][ODBC Driver 17 for SQL Server]Connection is busy with results for another command"} System.Data.Odbc.OdbcException In this video below we going to provide us some insights about it.

Why?

• Happens when you have an active process (DataReader) and you try to run another process (SELECT).
• Example:

/*
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

using System.Collections;
using System.Data;
using C = System.Data.SqlClient;
using O = System.Data.Odbc;
using System.Diagnostics;
using System.Threading;
using System.Configuration;
using System.Transactions;
*/


O.OdbcConnection oConn = new O.OdbcConnection();
oConn.ConnectionString = "YourConnectionString";
oConn.Open();
O.OdbcCommand commandReader1 = new O.OdbcCommand("SELECT TOP 10000 'Reader 1',* FROM PerformanceVarcharNVarchar", oConn);
                    commandReader1.CommandTimeout = 1200;

                    O.OdbcCommand commandReader2 = new O.OdbcCommand("SELECT TOP 10000 'Reader 2',* FROM PerformanceVarcharNVarchar", oConn);
                    commandReader2.CommandTimeout = 1200;

                    Random rnd = new Random();
                    O.OdbcDataReader SqlReader1;
                    O.OdbcDataReader SqlReader2;

                    for (int tries = 1; tries <= nRows; tries++)
                    {
                        Console.WriteLine("Execution Nr.: " + tries.ToString());

                        try
                        {
                            if (bIFYouwantToReproduceTheIssue)
                            {
                                SqlReader1 = commandReader1.ExecuteReader();
                                while (SqlReader1.Read())
                                {
                                    Console.WriteLine("{0} - {1}", SqlReader1.GetValue(0), SqlReader1.GetValue(2));
                                    SqlReader2 = commandReader2.ExecuteReader();
                                    while (SqlReader2.Read())
                                    {
                                        Console.WriteLine("{0} - {1}", SqlReader2.GetValue(0), SqlReader2.GetValue(2));
                                    }
                                    SqlReader2.Close();
                                }
                                SqlReader1.Close();
                            }
                            else
                            {
                                SqlReader1 = commandReader1.ExecuteReader();
                                while (SqlReader1.Read())
                                {
                                    Console.WriteLine("{0} - {1}", SqlReader1.GetValue(0), SqlReader1.GetValue(2));
                                }
                                SqlReader1.Close();
                                SqlReader2 = commandReader2.ExecuteReader();
                                while (SqlReader2.Read())
                                {
                                    Console.WriteLine("{0} - {1}", SqlReader2.GetValue(0), SqlReader2.GetValue(2));
                                }
                                SqlReader2.Close();

                            }
                        }
                        catch (Exception e)
                        {
                            Console.WriteLine("Ups!! " + e.Message);
                            continue;
                        }

                    }
                }
                oConn.Close();

How to find?

• Application Error messages
• SQL Diagnostics

How to Fix?

• Enable MARS.
• Implements a mechanism to protect this issue.
• Wait/Cancel the previous execution.

Query used in the demo:

SELECT
 substring(REPLACE(REPLACE(SUBSTRING(ST.text, (req.statement_start_offset/2) + 1, (
(CASE statement_end_offset WHEN -1 THEN DATALENGTH(ST.text) ELSE req.statement_end_offset END
- req.statement_start_offset)/2) + 1) , CHAR(10), ' '), CHAR(13), ' '), 1, 512) AS statement_text
--,dbs.name
,req.database_id
,program_name
,req.session_id
, req.cpu_time 'cpu_time_ms'
, req.status
, wait_time
, wait_resource
, wait_type
, last_wait_type
, req.total_elapsed_time
, total_scheduled_time
, req.row_count as [Row Count]
, command
, scheduler_id
, memory_usage
, req.writes
, req.reads
, req.logical_reads, blocking_session_id 
, req.request_id
FROM sys.dm_exec_requests AS req
inner join sys.dm_exec_sessions as sess on sess.session_id = req.session_id
CROSS APPLY sys.dm_exec_sql_text(req.sql_handle) as ST
where req.session_id <> @@SPID

sp_who2

Video with a demo:

Enjoy!