Get status while a select is running?

[David Thielen]

Hi;

Is there some way to get called every hundred rows or so when a select
is running, and have a way to cancel it? I noticed that SQL Server
Management Studio does this.

I need this for all vendors if possible. But if it's just some, better
that than none.

thanks - dave

david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Colbert Zhou [MSFT]]

Hi Dave,

There is a way to Cancel it. We just need to call SqlCommand.Cancel
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.can
cel.aspx

But for Select command, there is no way to get notified in the process of
the querying. Select querying is considered as a atom operation in ADO.NET.


Best regards,
Colbert Zhou

 

[sloan]

There are a few events you can tap into. But it will NOT help you with a
single select query (as Colbert has explained).

But I'll make you aware of these all the same.



SqlConnection typedConn = (SqlConnection)conn;

typedConn.InfoMessage += new
SqlInfoMessageEventHandler(RecordInfoMessages);

typedConn.StateChange += new
StateChangeEventHandler(HandleStateChange);

I don't have the Handle Event Code. But shows the connection events you can
tap into.

IIRC, InfoMessage is tied to Print Statements in the TSQL (?? maybe others).

Since I've gotten away from coding to the concrete implementation (
SqlConnection) and rely more on the IDbConnection interface to code against,
I don't use these specific events anymore.


...............

 

[David Thielen]

Hi Dave,

There is a way to Cancel it. We just need to call SqlCommand.Cancel
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.can
cel.aspx

But for Select command, there is no way to get notified in the process of
the querying. Select querying is considered as a atom operation in ADO.NET.

Bummer - ok, thanks


david@[email protected]
Windward Reports -- http://www.WindwardReports.com
me -- http://dave.thielen.com

Cubicle Wars - http://www.windwardreports.com/film.htm

 

[Gregory A. Beamer]

Hi;

Is there some way to get called every hundred rows or so when a select
is running, and have a way to cancel it? I noticed that SQL Server
Management Studio does this.

I need this for all vendors if possible. But if it's just some, better
that than none.

thanks - dave

One thing missed here is to cancel a command you generally have to fire it
off async.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA

Twitter: @gbworld
Blog: http://gregorybeamer.spaces.live.com

*******************************************
| Think outside the box! |
*******************************************

 

[William Vaughn \(MVP\)]

When executing a query against SQL Server, there are no "pings" (that you
can see) from SQL Server to return the status of the query. To do so would
slow down the processing of your query. It's even tough to break up your
stored procedure and "return" (PRINT) intermediate results as these are
stacked into the TDS stream behind the rowsets and won't be seen until long
after you need them.
Generally, I design queries that take so little time that the by the time
the user is wondering if it's done, it is. For longer queries or operations
I entertain the user with a progress bar or a movie on dog grooming.

--
__________________________________________________________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
http://betav.com http://betav.com/blog/billva
____________________________________________________________________________________________