SQL Server error messages do not always lead to errors in R

[jordivandooren]

Issue Description and Expected Result

dbExecute() and similar commands (like dbGetQuery(), dbWriteTable()) do not always raise an error when a throw or raiserror occurs in SQL Server.

Database

Tested with SQL Server 2019, ODBC Driver 17. The reprex that follows uses SQL Server Express, but I also tested using a localdb instance and a production SQL Server database at work.

Reproducible Example

Run a SQL Server Express server locally using docker:

docker run -e "ACCEPT_EULA=Y" -e "MSSQL_PID=EXPRESS" -e "MSSQL_SA_PASSWORD=abCD,.()" -p 1433:1433 -d mcr.microsoft.com/mssql/server:2019-latest

con <- DBI::dbConnect(odbc::odbc(), driver = "odbc driver 17 for sql server", server = "localhost", uid = "sa", pwd = "abCD,.()", trustservercertificate = 'yes')

# example 1: returns 0 instead of raising an error
DBI::dbExecute(con, "select 1; throw 60000, 'something went wrong', 1") 

# example 2: silently inserts only twice (again, no error)
DBI::dbExecute(con, "create table tmp (col1 bit)")
DBI::dbExecute(con, "insert into tmp values (0); insert into tmp values (1); throw 6000, 'hello', 1; insert into tmp values (0)")

[hadley]

I think the complicated thing here is that the SQL is actually multiple SQL statements and it looks like while we're processing each statement in turn we (a) don't stop after the first error and (b) don't report any error unless it's the first.

[simonpcouch]

Ah, I'm wrong here. Thanks.

[jordivandooren]

I think it does stop after the first error. The last insert is not executed:

> DBI::dbExecute(con, "create table tmp (col1 bit)")
DBI::dbExecute(con, "insert into tmp values (0); insert into tmp values (1); throw 6000, 'hello', 1; insert into tmp values (0)")
[1] 0
[1] 1
> DBI::dbReadTable(con "tmp")
Error: unexpected string constant in "DBI::dbReadTable(con "tmp""
> DBI::dbReadTable(con, "tmp")
   col1
1 FALSE
2  TRUE

We first noticed this issue (of errors not being raised from R) with calls to dbAppendTable() to write to a table having triggers to validate inserts/updates/deletes against other tables. The throw (or raiserror) message from these triggers would not reach our app.

[hadley]

Ah, so the problem is just that the error isn't rethrown. That's at least something.

[jordivandooren]

Slightly more realistic example, in which a trigger contains multiple statements. The throw does lead to inserts being blocked/rolled back (as it should) but, again, the message is not rethrown.

On the database (using sqlcmd -S localhost -U sa -P 'abCD,.()' -C):

create database db
use db
create table tbl (col1 int)
create trigger trg on tbl after insert as 
begin select 3; if 4 < (select count(*) from tbl) throw 60000, 'error from trigger', 1 end

In R:

con <- DBI::dbConnect(odbc::odbc(), driver = "odbc driver 17 for sql server", server = "localhost", uid = "sa", pwd = "abCD,.()", trustservercertificate = 'yes')
DBI::dbExecute(con, "use db")
DBI::dbAppendTable(con, "tbl", data.frame(col1 = 1:10))  # no error is raised
DBI::dbReadTable(con, "tbl") 
# [1] col1
# <0 rows> (or 0-length row.names)

[jordivandooren]

The same problem occurs when using ODBC Driver 18.

con18 <- DBI::dbConnect(odbc::odbc(), driver = "odbc driver 18 for sql server", server = "localhost", uid = "sa", pwd = "abCD,.()", trustservercertificate = 'yes')
DBI::dbExecute(con18, "select 1; throw 60000, 'something went wrong', 1") 
# [1] 0