Stopping a Long Running Query

[Kory]

How do you stop a long running query with a SQLConnection or SQLCommand?

Calling Close on either just waits until the query is done. Is there a way
to stop it?
The following on a long query
SqlConnection mySqlConnection = new
SqlConnection("server=(local)\\NetSDK;Trusted_Conn ection=yes;database=northw
ind");
mySqlConnection.Open();
SqlCommand mySqlCommand = new SqlCommand("select * from employees order by
EmployeeID", mySqlConnection);

SqlDataReader myReader = mySqlCommand.ExecuteReader();

myReader.Close();

mySqlConnection.Close();

[William Ryan]

If you know the duration of time that defines 'long running' you cna set the
CommandTimeout property to that number + 1.

Before you do this though, make sure it's not the connection that's causing
the delay or the sheer number of records.

HTH,

Bill
"Kory" <ko**@removeme-mlsc.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...

How do you stop a long running query with a SQLConnection or SQLCommand?

Calling Close on either just waits until the query is done. Is there a way
to stop it?
The following on a long query
SqlConnection mySqlConnection = new
SqlConnection("server=(local)\\NetSDK;Trusted_Conn ection=yes;database=northw ind");
mySqlConnection.Open();
SqlCommand mySqlCommand = new SqlCommand("select * from employees order by
EmployeeID", mySqlConnection);

SqlDataReader myReader = mySqlCommand.ExecuteReader();

myReader.Close();

mySqlConnection.Close();

[Kory]

The timeout isn't adequate because the record set can vary dramatically from
2-3 million records to 70-80 million, so it could run either for a minute or
3 hours...

Think of it as the ability to cancel a query. Right now, if you close it,
It waits there until the query is done.

There has to be (or sure should be ) a way to cancel a running query..
"William Ryan" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

If you know the duration of time that defines 'long running' you cna set the CommandTimeout property to that number + 1.

Before you do this though, make sure it's not the connection that's causing the delay or the sheer number of records.

HTH,

Bill
"Kory" <ko**@removeme-mlsc.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...

How do you stop a long running query with a SQLConnection or SQLCommand?

Calling Close on either just waits until the query is done. Is there a way to stop it?
The following on a long query
SqlConnection mySqlConnection = new

SqlConnection("server=(local)\\NetSDK;Trusted_Conn ection=yes;database=northw

ind");
mySqlConnection.Open();
SqlCommand mySqlCommand = new SqlCommand("select * from employees order by EmployeeID", mySqlConnection);

SqlDataReader myReader = mySqlCommand.ExecuteReader();

myReader.Close();

mySqlConnection.Close();

[Kory]

Noone has ever had to cancel a running query?

"Kory" <ko**@removeme-mlsc.com> wrote in message
news:uL**************@TK2MSFTNGP12.phx.gbl...

The timeout isn't adequate because the record set can vary dramatically from 2-3 million records to 70-80 million, so it could run either for a minute or 3 hours...

Think of it as the ability to cancel a query. Right now, if you close it,
It waits there until the query is done.

There has to be (or sure should be ) a way to cancel a running query..
"William Ryan" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl...

If you know the duration of time that defines 'long running' you cna set the

CommandTimeout property to that number + 1.

Before you do this though, make sure it's not the connection that's

causing

the delay or the sheer number of records.

HTH,

Bill
"Kory" <ko**@removeme-mlsc.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...

How do you stop a long running query with a SQLConnection or SQLCommand?
Calling Close on either just waits until the query is done. Is there a way to stop it?
The following on a long query
SqlConnection mySqlConnection = new

SqlConnection("server=(local)\\NetSDK;Trusted_Conn ection=yes;database=northw

ind");
mySqlConnection.Open();
SqlCommand mySqlCommand = new SqlCommand("select * from employees

order by EmployeeID", mySqlConnection);

SqlDataReader myReader = mySqlCommand.ExecuteReader();

myReader.Close();

mySqlConnection.Close();

[Girish Bharadwaj]

Kory wrote:

Noone has ever had to cancel a running query?

"Kory" <ko**@removeme-mlsc.com> wrote in message
news:uL**************@TK2MSFTNGP12.phx.gbl...

The timeout isn't adequate because the record set can vary dramatically

from

2-3 million records to 70-80 million, so it could run either for a minute

or

3 hours...

Think of it as the ability to cancel a query. Right now, if you close it,
It waits there until the query is done.

There has to be (or sure should be ) a way to cancel a running query..
"William Ryan" <do********@comcast.nospam.net> wrote in message
news:%2****************@TK2MSFTNGP12.phx.gbl.. .

If you know the duration of time that defines 'long running' you cna set

the

CommandTimeout property to that number + 1.

Before you do this though, make sure it's not the connection that's

causing

the delay or the sheer number of records.

HTH,

Bill
"Kory" <ko**@removeme-mlsc.com> wrote in message
news:eE**************@tk2msftngp13.phx.gbl...

How do you stop a long running query with a SQLConnection or
SQLCommand?
Calling Close on either just waits until the query is done. Is there a

way

to stop it?
The following on a long query
SqlConnection mySqlConnection = new

SqlConnection("server=(local)\\NetSDK;Trusted_Conn ection=yes;database=northw
ind");
mySqlConnection.Open();
SqlCommand mySqlCommand = new SqlCommand("select * from employees

order

by

EmployeeID", mySqlConnection);

SqlDataReader myReader = mySqlCommand.ExecuteReader();

myReader.Close();

mySqlConnection.Close();

I am probably confused. but, SQLCommand.Cancel() does not suffice? ...
--
Girish Bharadwaj