How to stop a running query?

Question

I use RODBC to send queries to an SQL-Server. Sometimes they take too much time to run, so I need to cancel them.

Clicking the red "stop" button in RStudio yields this error message:

R is not responding to your request to interrupt processing so to stop the current operation you may need to terminate R entirely.

Terminating R will cause your R session to immediately abort. Active computations will be interrupted and unsaved source file changes and workspace objects will be discarded.

Do you want to terminate R now?

And if I click yes my session is indeed terminated. (note: using Rgui instead of RStudio doesn't make things better)

However:

• when I use another software (named "Query ExPlus") to connect to this same SQL-Server, I have a similar stop button, and clicking it instantly interrupts the query, without any crash.

• when I connect to a PostgreSQL database using the RPostgres package I can also interrupt the query at any time.

These two points lead me to think that there should be a way to solve my problem. What can I do?

So far my workaround is:

library(RODBC)
library(R.utils)

withTimeout(mydf <- sqlQuery(myconnection, myquery), timeout=120)

Note: I don't have permission to kill queries from the database side.

Answer 1

I've just stumbled upon the odbc package. It allows to interrupt a query at any time.

Basic usage goes like this:

library(DBI)

myconnection <- dbConnect(odbc::odbc(),
                          driver = "SQL Server",
                          server = "my_server_IP_address",
                          database = "my_DB_name",
                          uid = "my_user_id",
                          pwd = "my_password")

dbGetQuery(myconnection, myquery)

I don't have a deep understanding of what happens behind the scenes, but for what I've seen so far in my personal use this package has other advantages over RODBC:

• really faster
• get the column types from the DB instead of guessing them (see here)
• no stringsAsFactors and as.is arguments necessary

Answer 2

Most SQL Server users use SQL Server Management Studio (which is free and can be downloaded from Microsoft) to connect to SQL Server or execute commands from the command line via a tool called SQLCMD.

If you can determine the session id that the SQL Command is being run in you can kill the session which would stop any executing command(s). SQL Server will still need time (could be a 'long' time) to rollback any changes made during the execution of the command.

Terminating a session (depending on the software) can take a while to communicate to SQL Server that the session has been terminated. When I connected to DB2 from SQL Server using linked servers DB2 would buffer the terminate command and it would frequently take up to an hour for DB2 to realize the session had been terminated.

To determine what the session you are running in you can try:

select @@spid;

once you have the spid (lets say 86) you can then issue (depending on if you have permission to do so)

kill 86;

but as Microsoft notes: Terminates a user process that is based on the session ID or unit of work (UOW). If the specified session ID or UOW has a lot of work to undo, the KILL statement may take some time to complete, particularly when it involves rolling back a long transaction.