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.

Shop

R is waiting for the result of the SQL query, you can only stop R between 2 lines of code basically. Even R functions written in C cannot be killed this way. An option for you is to wrap the SQL call in a R script you will run in a shell from your main script, setting the option to pursue without waiting for the output. Then in your main script you send every 10 second or so a new query that will check if the main query is over, by checking number of lines or table existence for example. On this code the stop button will work. Then run another CMD line from R to kill the SQL query. – Borst

Seems like you won't have permission for the last step. Then email your admin that you screwed up again :). It would be better if you could split your query in several parts, then in R run them through a loop, then you can monitor your progress and kill the process completely at any step. – Borst

Thanks, that's indeed a possible way. A similar approach I tried was using the argument rows_at_time=1 in the ODBC connection (for here Hadley says you can stop RPostgres at any time because of the fact that it fetches a row at a time). But it did not change anything. – Shop

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

Shop

I also use odcb. The command can be stopped, but the query apparently still runs on the server. If I attempt another query, I get the following error: nanodbc/nanodbc.cpp:1587: HY000: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt – Fluvial

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.

Stomachache

Thanks Benjamin, but as noted in my question I don't have permission to use kill. – Shop

Then except for stopping the session there is nothing you can do. – Stomachache

Ok... But then how do you explain that Query ExPlus can do it? – Shop

Its probably a better product and somehow kills the transaction and/or session. I don't know these products at all and the great majority of SQL Server developers use SQL Server Management Studio and or Visual Studio to interact with SQL Server so the amount of support out there for other products will be rather meager... – Stomachache

Answer 3

Try to close your "tab query" on SQL Server Management Studio Then it will appear pop-up,

This Query is currently executing. Do you want to cancel this query ?

Cancel anyway, choose "yes".

Pashm

My query is sent from RStudio, not from SSMS. – Shop

Answer 4

try to set your connection prior to query:

sql = odbcConnect('Database name')

Then use same line to run your query:

mydf <- sqlQuery(sql, " myquery ")

Note: The running time is dependant on both database and R server but setting up the connection this way should resolve termination problem.

Falls

Thanks but I obviously set a connection before sending the query. – Shop