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!
