As mentioned by Nick and Martin, the eventual status of your query depends on whether SQL Server knows about your network cable pull before the query completed. From Books Online (though I find it interesting that there are equivalent topics for this in 2000, 2005, 2008, and 2008 R2, but not 2012 or 2014):
If an error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to an instance of the Database Engine is broken, any outstanding transactions for the connection are rolled back when the network notifies the instance of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and the instance of the Database Engine rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
(As an aside, that word connections in the 2nd last sentence was probably meant to be transactions. I don't know how one rolls back a connection.)
In a similar way, SQL Server may undo or redo transactions during recovery after the server is shut down unexpectedly, and this will depend on the state of the transaction at the moment of shutdown. I have seen people use this tactic to achieve what you were trying to do (cancel the transaction(s)) and when the server started up again much of the work was simply redone (so net effect of their knee-jerk reaction was much closer to zero than they expected).
So rather than be subject to this, instead of doing drastic things in a panic, like yanking a network cable or shutting off the machine, I suggest in the future you have better discipline about running ad hoc queries against important systems. For example, instead of:
UPDATE dbo.sometable
-- where *oops* I forgot this part
Have this:
BEGIN TRANSACTION;
UPDATE dbo.sometable
-- where *oops* I forgot this part
-- COMMIT TRANSACTION;
-- ROLLBACK TRANSACTION;
Then, if the update was indeed correct, you can highlight the COMMIT
part and run it. If it wasn't, you can calmly highlight the ROLLBACK
part and run that. You can even use add-ins like SSMS Tools Pack to edit your New Query
template to include that boilerplate.
Now it could still get you in trouble in the event that you run the query and then don't either commit or rollback, because now your transaction is blocking other users. But this is better then irrevocably modifying data.
And of course, as always, have a backup you can rely on.
So one potential way of doing this would be to write your SQL Server query to output an XML string of the data you want to send by using FOR XML
.
You could then create an SSIS package and use a Data Flow Task to save the XML to an XML file, meaning you're logging all the exports made.
A script task, following the success of the Data Flow Task, can then do the API call. You'd write standard C#, grabbing the file and calling the API method. You would then use the Task result variables to return Success/Failure based upon the response from the API.
However, you've said you're more of a C# Dev, so why not just write a stand alone App that does all this? It would be much more robust than SSIS!
Update
There is actually a Webservice task in SSIS that can apparently do what you'd require. However, I believe that if you're more comforatble developing in C#, that is the approach you should take.
Best Answer
As the answer referenced in your question indicates, SQL Server will cancel the executing query and rollback the transaction when it detects the network connection has terminated. A SQL Server session needs a connected client to query return results and messages (informational, warning, and error messages).
You have a few options to maintain the connection/session for a long-running query, with and without an interactive client applciaton.
RDP into the SQL Server machine or jump box and execute the query from there. This will allow you to disconnect from the RDP session while the client application (e.g. SSMS, SQLCMD, ADS, etc.) continues to run. You can later reconnect to the same Remote Desktop session to review the results.
Create a SQL Server Agent job with a T-SQL, PowerShell, CmdExec, or SSIS job step to execute the query without an interactive client.
Create a Windows Task Scheduler task to execute the query, using SQLCMD, PowerShell, or other command-line tool.
Encapsulate the query in a stored procedure and execute it as a Service Broker activated proc. This requires a Service Broker enabled database and the proc code will need to capture results. See Asynchronous procedure execution for code examples of this technique and other considerations.
Without an interactive client, results and messages that won't be visible so make sure these are logged/captured according to your needs. Also ensure command-line applications return a non-zero exit code after errors so it's reported as an error by SQL Server Agent or Windows Task Scheduler.