I am updating a couple of shiny apps to use the odbc library instead of RJDBC and/or RODBC.
Overall this is working well and faster. However, I have run into one problem that I can not seem to figure out.
When I query a column with fields that hold long strings of text, my query is only returning 255 characters. For example, the following query returns the expected results on my local machine, but on my shiny server fields in the NotesNotes column are truncated to 255 characters. RODBC returns the expected results.
notes <- dbGetQuery(con, "SELECT NoteDate, NoteNotes = CONVERT(VARCHAR(4000), NoteNotes) FROM Notes")
I have tried a number of variations on the query with no luck.
I am running R 3.4.3. The versions of the R packages that this leverages are the same on my machine and the server. I have tried several variations of the query and continue to hit the same issue.
I am running this on an open source shiny server (v18.104.22.1686) with R 3.4.3 and odbc (v1.1.3) and dbi (v0.7).
The database is SQL Server 2012.
Any ideas for next steps in troubleshooting this would be much appreciated. Thanks.