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