SQL Server requires `varchar(max)` and `varbinary(max)` columns to be at the end of `SELECT` statements.

[jimhester]

Issue Description and Expected Result

SQL Server requires varchar(max) and varbinary(max) columns to be at the end of SELECT statements. If they are before the end a [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index error is returned. This is apparently a known workaround for this problem.

Database

SQL Server 2013 (13.00.1601)

Reproducible Example

library(DBI);library(odbc)
con <- dbConnect(odbc::odbc(), "test")

# Reorder columns and put the species column first
it <- iris[1:10, c(5, 1, 2)]
dbWriteTable(con, "it", it)
#> [1] TRUE

# SELECT using normal order with varchar(max) first
dbGetQuery(con, "SELECT * from it")
#> Warning in value[[3L]](cond): nanodbc.cpp:2755: 07009: [Microsoft][ODBC
#> Driver 13 for SQL Server]Invalid Descriptor Index
#> Warning: Pending rows
#> NULL

# However SELECT using varchar(max) at the end work fine
dbGetQuery(con, "SELECT \"Sepal.Length\", \"Sepal.Width\", Species from it")
#>    Sepal.Length Sepal.Width Species
#> 1           5.1         3.5  setosa
#> 2           4.9         3.0  setosa
#> 3           4.7         3.2  setosa
#> 4           4.6         3.1  setosa
#> 5           5.0         3.6  setosa
#> 6           5.4         3.9  setosa
#> 7           4.6         3.4  setosa
#> 8           5.0         3.4  setosa
#> 9           4.4         2.9  setosa
#> 10          4.9         3.1  setosa

Session Info

devtools::session_info()
#> Session info -------------------------------------------------------------
#>  setting  value                       
#>  version  R version 3.3.1 (2016-06-21)
#>  system   x86_64, mingw32             
#>  ui       RStudio (1.0.35)            
#>  language (EN)                        
#>  collate  English_United States.1252  
#>  tz       America/New_York            
#>  date     2016-11-07
#> Packages -----------------------------------------------------------------
#>  package    * version     date       source                          
#>  assertthat   0.1         2013-12-06 CRAN (R 3.3.1)                  
#>  blob         0.0.0.9000  2016-11-07 Github (jimhester/blob@d677541) 
#>  clipr        0.2.1       2016-06-23 CRAN (R 3.3.2)                  
#>  crayon       1.3.2       2016-06-28 CRAN (R 3.3.1)                  
#>  curl         2.1         2016-09-22 CRAN (R 3.3.1)                  
#>  DBI        * 0.5-12      2016-10-31 Github (rstats-db/DBI@4f00863)  
#>  devtools     1.12.0.9000 2016-10-06 local                           
#>  digest       0.6.10      2016-08-02 CRAN (R 3.3.1)                  
#>  evaluate     0.10        2016-10-11 CRAN (R 3.3.2)                  
#>  formatR      1.4         2016-05-09 CRAN (R 3.3.2)                  
#>  git2r        0.15.0      2016-05-11 CRAN (R 3.3.1)                  
#>  htmltools    0.3.5       2016-03-21 CRAN (R 3.3.2)                  
#>  httr         1.2.1       2016-07-03 CRAN (R 3.3.1)                  
#>  jsonlite     1.1         2016-09-14 CRAN (R 3.3.1)                  
#>  knitr        1.14        2016-08-13 CRAN (R 3.3.2)                  
#>  magrittr     1.5         2014-11-22 CRAN (R 3.3.1)                  
#>  memoise      1.0.0       2016-01-29 CRAN (R 3.3.1)                  
#>  odbconnect * 0.0.0.9000  <NA>       local                           
#>  R6           2.2.0       2016-10-05 CRAN (R 3.3.1)                  
#>  Rcpp         0.12.7      2016-09-05 CRAN (R 3.3.1)                  
#>  reprex       0.0.0.9001  2016-11-07 Github (jennybc/reprex@826ddf4) 
#>  rmarkdown    1.1         2016-10-16 CRAN (R 3.3.2)                  
#>  roxygen2     5.0.1       2015-11-11 CRAN (R 3.3.1)                  
#>  stringi      1.1.2       2016-10-01 CRAN (R 3.3.1)                  
#>  stringr      1.1.0       2016-08-19 CRAN (R 3.3.1)                  
#>  testthat     1.0.2.9000  2016-10-11 Github (hadley/testthat@46d15da)
#>  tibble       1.2         2016-08-26 CRAN (R 3.3.1)                  
#>  withr        1.0.2       2016-06-20 CRAN (R 3.3.1)

[hongooi73]

@hadley notified me of this issue. I can repro it with the odbc package, but not with any other tool that uses ODBC (the protocol). This is with a local SQL Server 2016 instance.

Attached are a couple of scripts I used for testing. One is a sql script that creates a small table with a varchar(max) column. The other tries various methods of extracting the data from the table. Only odbc fails.

varcharTest.zip

(One of the packages used, RevoScaleR, is part of Microsoft R Client, which is a free Windows download from here.)

[jimhester]

I think we have confirmed elsewhere that this is a driver issue rather than something we can fix in this package.

[hongooi73]

Was commit 44547b8 meant to workaround this problem? I'm still getting this error with the CRAN build.

I've also asked the guys if there are any ways that the package can deal with this, without having to reorder the columns.

[jimhester]

44547b8 ensures tables created by this package do not have varchar(max) columns, but if a table already exists with one the issue will still occur.

Our contact at Magnitude said he does not think there is anything we can do in the package to handle this, the change needs to come from the driver.

[vadimus202]

Is there a way to throw an error with a message listing which variables need to be placed last?
This issue turns up when I do a select top 1000 * from tbl on an unfamiliar table, which also affects printing a table viadplyr::tbl().

Thanks!

[gwd666]

Mid 2020 an still an issue :-(