ServerConnection.Cancel method

Question

The SqlCommand class has a Cancel method that, according to the documentation, allows one to cancel a command.

I'm looking for an equivalent method for commands executed through the smo Server class. The ServerConnection class (connectioncontext field of the server class) has a Cancel method but the documentation is not clear on what it does.

Wildawildcat

I recommend avoiding SMO. It has a whole host of problems including not being even remotely thread safe. I ended up throwing it away and did everything with sys. views. – Gregor

Maybe you want to disconnect the serverconnection? msdn.microsoft.com/en-us/library/… – Parkin

SQL Profiler doesn't seem to be clear about what it does either. Whatever it does, doesn't show up in SQL Profiler as a logout event, or anything else I can discern. I can cancel the connection and then continue what I was doing without throwing any errors. I'm with Jonathan Allen. Do it without SMOs. – Lupe

Cancel is only useful when you have defined a SqlBatch for the command to be executed within. This is generally done with the SqlConnection object but can be between the SqlConnect and SqlCommand, in the event you need to execute multiple Batches with each Batch containing multiple Commands within a single Connection timeline. – Psychotechnics

Answer 1

The SeverConnection.Cancel() Method works similarly to the SqlCommand.Cancel() method and the Documentation states:

If there is nothing to cancel, nothing occurs. However, if there is a command in process, and the attempt to cancel fails, no exception is generated.

In some, rare, cases, if you call ExecuteReader then call Close (implicitily or explicitly) before calling Cancel, and then call Cancel, the cancel command will not be sent to SQL Server and the result set can continue to stream after you call Close. To avoid this, make sure that you call Cancel before closing the reader or connection.

Meaning that the cancel method will work, If you call it before the call to the close() method.

If you call it after, the command will not be sent to the sql resulting in continues stream to the sql after to call the close() method.

Moreover, if there is a command in process and the attempt to cancel fails, you will not receive an error as no exception is generated in such case.

Creech