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…

Accepted Answer

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

How to stop a running query?

Solution

Problem

Related problems