Y
Yuriy Solodkyy
Hi,
the following problem was raised several times in newsgroups, but I still
cannot find suitable solution.
Let say we have the following stored procedure which returns many records.
(this is only a sample, it does not do
anything meaningful)
-- ================================
create procedure a as begin
declare @a table (a int)
declare @i int set @i = 0
while @i < 11 begin
insert into @a(a) values (@i)
insert into @a select a from @a
set @i = @i + 1
end
select a.a from @a a , @a b
end
-- ================================
I have created it in pubs DB. If you run it in query analyzer you will get
results soon.
If you run the follwing C# program you also get it completed soon.
(You may have to change connection string.)
////////////////////////////////
using System;
using System.Data.SqlClient;
namespace TestApp {
class TestApp {
[STAThread]
static void Main(string[] args) {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;
{
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
}
}
///////////////////////////
However, if you start two instances of this code in two simultaniously
running threads you randomly get exceptions
with message: "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is
not responding."
The code below illustrates this. It starts 2 threads. It is not enough to
get error on some computers, so increase
thread count to 3 or 4. If you have hypethreaded computer ot multiprocessor
system a chance of getting error is
higher.
"*" in application output means catching this exception.
////////////////////////
using System;
using System.Data.SqlClient;
namespace TestApp {
class TestApp {
[STAThread]
static void Main(string[] args) {
System.Threading.Thread t;
for(int j = 0; j < 2; j++) {
t = new System.Threading.Thread(new
System.Threading.ThreadStart(ThreadProc));
t.Start();
}
}
public static void ThreadProc() {
// repeats reading..
while (true) {
DoTest();
}
}
public static void DoTest() {
try {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (; {
while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
catch (Exception e){
if (e.Message == "Timeout expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.") {
Console.Write("*");
}
else {
Console.WriteLine("*************" + e.Message);
Console.WriteLine(e.StackTrace);
}
}
}
}
}
//////////////////////////////////
Usual output is:
................................**...........................................
.....
......................................................................*.*....
.....
.............................................................................
.....
............................................................*.....*..........
.....
.............................................................................
.....
.............................................................................
.....
...........................................*.................................
.....
...................completed!
.....................................................*.......................
.....
.............................*...............................................
.....
.............................................................................
.....
......................................................
Here "completed!" means that thread was able to read all data without
exception. I haven't seen this message on
hyperthread systems.
Does anybody know how to avoid getting this exception?
Thank you in advance,
Yuriy
the following problem was raised several times in newsgroups, but I still
cannot find suitable solution.
Let say we have the following stored procedure which returns many records.
(this is only a sample, it does not do
anything meaningful)
-- ================================
create procedure a as begin
declare @a table (a int)
declare @i int set @i = 0
while @i < 11 begin
insert into @a(a) values (@i)
insert into @a select a from @a
set @i = @i + 1
end
select a.a from @a a , @a b
end
-- ================================
I have created it in pubs DB. If you run it in query analyzer you will get
results soon.
If you run the follwing C# program you also get it completed soon.
(You may have to change connection string.)
////////////////////////////////
using System;
using System.Data.SqlClient;
namespace TestApp {
class TestApp {
[STAThread]
static void Main(string[] args) {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;
{while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
}
}
///////////////////////////
However, if you start two instances of this code in two simultaniously
running threads you randomly get exceptions
with message: "Timeout expired. The timeout period elapsed prior to
completion of the operation or the server is
not responding."
The code below illustrates this. It starts 2 threads. It is not enough to
get error on some computers, so increase
thread count to 3 or 4. If you have hypethreaded computer ot multiprocessor
system a chance of getting error is
higher.
"*" in application output means catching this exception.
////////////////////////
using System;
using System.Data.SqlClient;
namespace TestApp {
class TestApp {
[STAThread]
static void Main(string[] args) {
System.Threading.Thread t;
for(int j = 0; j < 2; j++) {
t = new System.Threading.Thread(new
System.Threading.ThreadStart(ThreadProc));
t.Start();
}
}
public static void ThreadProc() {
// repeats reading..
while (true) {
DoTest();
}
}
public static void DoTest() {
try {
using (SqlConnection c = new SqlConnection(@"Integrated
Security=SSPI;Initial Catalog=pubs;Data Source=localhost;")) {
c.Open();
using (SqlCommand m = new SqlCommand(@"execute a", c)) {
m.CommandTimeout = 0;
using (SqlDataReader r =
m.ExecuteReader(System.Data.CommandBehavior.SequentialAccess)) {
int i = 0;
for (;
{while (r.Read()) {
i++;
if ((i % 123456) == 0) Console.Write(".");
}
if (!r.NextResult()) break;
};
}
}
}
Console.WriteLine("completed!");
}
catch (Exception e){
if (e.Message == "Timeout expired. The timeout period elapsed prior
to completion of the operation or the server is not responding.") {
Console.Write("*");
}
else {
Console.WriteLine("*************" + e.Message);
Console.WriteLine(e.StackTrace);
}
}
}
}
}
//////////////////////////////////
Usual output is:
................................**...........................................
.....
......................................................................*.*....
.....
.............................................................................
.....
............................................................*.....*..........
.....
.............................................................................
.....
.............................................................................
.....
...........................................*.................................
.....
...................completed!
.....................................................*.......................
.....
.............................*...............................................
.....
.............................................................................
.....
......................................................
Here "completed!" means that thread was able to read all data without
exception. I haven't seen this message on
hyperthread systems.
Does anybody know how to avoid getting this exception?
Thank you in advance,
Yuriy