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.

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.