How to collect data from future dplyr query after one fails?

Often when I try to collect a lot of data from a SQL Server database, I get a cryptic error message from the nanodbc driver. For example,

library(dplyr)
res <- collect(tbl(con, "result"))

Error in result_fetch(res@ptr, n, ...) : nanodbc/nanodbc.cpp:2525: 08S01: [Microsoft][ODBC Driver 11 for SQL Server]SSL Provider: [err

Result already cleared

From then on, I get the error

Error: 'SELECT * FROM "result" AS "zzz13" WHERE (0 = 1)' nanodbc/nanodbc.cpp:1587: 08S01: [Microsoft][ODBC Driver 11 for SQ

whenever I try to collect data from a table in the database. This continues until I restart my R session.

Has anyone seen this behavior before or could provide a way to fix it?

Solution

One case in which this error definitely occurs : when there is a varchar type column in the table. you can check this simply by clicking on the connections pane in rstudio, going to the relevant table and checking the column types.

if the troublesome column is varchar_col1, then,

sometbl <- tbl(con, in_schema("schema_name","table_with_trouble"))
sometbl %>% head()

should reproduce your error.

I am not sure why this happens, but here is a workaround :

sometbl <- tbl(con, in_schema("schema_name","table_with_trouble")) %>%
    mutate(alt_col1 = as.character(varchar_col1)) %>%
    select(-varchar_col1)
sometbl %>% head()

should work.