1

I'm having to update my access to SQL Server from R on windows. The only option I see right now is odbc. Unfortunately, the ODBC standard and this driver (using nanodbc) in particular has some problems that are difficult to ignore:

  • Microsoft "updated" their ODBC driver years ago to require any "large-ish" columns must be requested last (fails with Invalid Descriptor Index; an issue for nanodbc was closed, not fixed), requiring me to know a lot more about the structure of the table than I need to know in other DBMSs
  • datetimeoffset is unsupported (https://github.com/r-dbi/odbc/issues/207), though this can be worked around by manually parsing the string; unfortunately, it seems to be losing millisecond precision and adds the TZ as (for example) Pacific Standard Time, and not something like more "standard" like -07:00, -0700, or +07 (or even PDT or America/Los_Angeles, though I prefer the first two)
  • an older version of ODBC that is not encumbered with the first bullet (column order "bug") breaks some string-equality tests, and comparison against nvarchar(max) columns fails with The data types ntext and varchar are incompatible in the equal to operator (I opened an issue at odbc, pending); workarounds require changing every query that does this

The non-ODBC driver RSQLServer has been deprecated in lieu of the "newer" odbc driver. One suggestion was to look at the RStudio professional database odbc drivers, but those are unixy-only.

So are there DBI-friendly methods for accessing a SQL Server? Basic requirements:

  • Windows 10
  • R-3.4 or newer
  • DBI-compatible (or similar-enough)
  • supports variable binding (via string-interpolation)
  • preferably not rJava-based (I could always risk using RSQLServer, even if unmaintained)
  • linux compatbility is desired but not strictly required

(For the record: I do not control the server, but must access it; I cannot affect the schema, so column-order is fixed; I believe that R Services for SQL Server will not suffice, I still need simultaneous access to files/resources not available to the server.)

  • Perhaps some glue language in between that could grab the SQL data, write to a file that your R set up can then read or dump to a temp local machine db table or something that is easier to work with on the R end of things? Tempted to recommend asking on stackoverflow ... – ivanivan Sep 5 '18 at 2:34
  • I'm active on SO, and I thought the solution was too close to SO's off-topic "recommend or find a book, tool, software library". The "glue" solution is certainly possible, in which case I'd likely use extensive and repetitive calls to bcp, isql, and/or sqlcmd, but ... dang, that will perform horribly, esp considering I'm running dozens/hundreds of queries per hour. It makes binding a little harder, lots of test-code to guard shell-escaped quotes and/or sql-injection-like errors. – r2evans Sep 5 '18 at 2:52
  • Yeah, that's why I said "tempted" :) I only played with R a little bit when I took my college stats class, and then out of curiosity so I don't know enough about R and complex work flow.. i'm sure there has to be a decent way of doing the odbc thing and having that work as needed for data pulls and process it in R... but I also remember odbc being a royal pita when I used it in school... – ivanivan Sep 5 '18 at 3:04
  • +1 on the ODBC/pita, it is (and usually has) proving troublesome. I actually considered using R's reticulate to connect via python's pymssql native library, but ... that's moving in the same direction as rJava. – r2evans Sep 5 '18 at 3:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.