0

I'm trying to get data from AWS SQL Server. This code works fine from local PC, but it didn't work from shiny-server (ubuntu).

library(dbplyr)
library(dplyr)
library(DBI)    

con <- dbConnect(odbc::odbc(),
                 driver = "FreeTDS",
                 server = "aws server",
                 database = "",
                 uid = "",
                 pwd = "")
tbl(con, "shops")
dbGetQuery(con,"SELECT *
                  FROM shops")

"R version 3.4.2 (2017-09-28)"

packageVersion("dbplyr")
[1] ‘1.2.1.9000’

packageVersion("dplyr")
[1] ‘0.7.4’
packageVersion("DBI")
[1] ‘0.7.15’

I have next error: 

tbl(con, "shops")
Error: <SQL> 'SELECT *
FROM "shops" AS "zzz2"
WHERE (0 = 1)'
  nanodbc/nanodbc.cpp:1587: 42000: [FreeTDS][SQL Server]Incorrect syntax near 'shops'.

But dbGetQuery(con,"SELECT * FROM shops") works fine. Can you explain what's going wrong?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
jyjek
  • 2,627
  • 11
  • 23

1 Answers1

1

This is more likely because the FreeTDS driver does not return the class that dbplyr expects to see in order to use the MS SQL translation. The workaround is to take the result of class(con) and then add the following lines right after you connect, but before calling tbl(). Replace the [you class name] with the results of the class(con) call:

sql_translate_env.[your class name] <- dbplyr:::`sql_translate_env.Microsoft SQL Server`
sql_select.[your class name]<- dbplyr:::`sql_select.Microsoft SQL Server`
edgararuiz
  • 625
  • 5
  • 9