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?
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.