Help querying from SQL Server database

dplyr
rstudio
odbc

#1

Hello,

I am trying to analyze data that is stored on a SQL Server database. I successfully established a connection using odbc::dbConnect. This caused RStudio to helpfully list the database and all its tables and views inside a beautiful hierarchical browser. Wonderful! Then I tried running tbl(con, "tableName") and then of course I get a cryptic error message from nanodbc that is truncated, so I can’t figure out what is wrong.

nanodbc/nanodbc.cpp:1587: 42S02: [Microsoft][ODBC Driver 11 for SQL Server]

I am using R 3.4.3 with the latest versions of odbc and dplyr (and everything else) on Linux using the above driver with unixodbc to connect. As I said, RStudio connects to the database brilliantly. I can view tables by clicking on their little icons in the Connections tab. I didn’t imagine that RStudio could be able to connect and show me the data, but that R+dplyr+odbc wouldn’t let me work with it.

Thanks in advance for any help.

attached base packages:
[1] parallel  stats     graphics  grDevices utils     datasets  methods   base     

other attached packages:
 [1] DBI_0.7         dbplyr_1.2.0    odbc_1.1.4      broom_0.4.3     lme4_1.1-15     Matrix_1.2-12  
 [7] bindrcpp_0.2    readxl_1.0.0    forcats_0.2.0   stringr_1.2.0   dplyr_0.7.4     purrr_0.2.4    
[13] readr_1.1.1     tidyr_0.7.2     tibble_1.4.1    ggplot2_2.2.1   tidyverse_1.2.1 swimr_0.0.92   

loaded via a namespace (and not attached):
 [1] minqa_1.2.4          colorspace_1.3-2     mclust_5.4           rprojroot_1.3-2     
 [5] base64enc_0.1-3      rmutil_1.1.0         rstudioapi_0.7       MatrixModels_0.4-1  
 [9] statip_0.1.5         bit64_0.9-7          AnnotationDbi_1.40.0 lubridate_1.7.1     
[13] xml2_1.1.1           splines_3.4.3        mnormt_1.5-5         knitr_1.18          
[17] jsonlite_1.5         nloptr_1.0.4         rJava_0.9-9          annotate_1.56.1     
[21] stabledist_0.7-1     compiler_3.4.3       httr_1.3.1           backports_1.1.2     
[25] assertthat_0.2.0     lazyeval_0.2.1       cli_1.0.0            acepack_1.4.1       
[29] htmltools_0.3.6      quantreg_5.34        tools_3.4.3          modeest_2.3.2       
[33] gtable_0.2.0         glue_1.2.0           bazar_1.0.6          reshape2_1.4.3      
[37] Rcpp_0.12.14         Biobase_2.38.0       cellranger_1.1.0     nlme_3.1-131        
[41] psych_1.7.8          timeDate_3042.101    openxlsx_4.0.17      spatial_7.3-11      
[45] rvest_0.3.2          RSQLServer_0.3.0     XML_3.98-1.9         stringdist_0.9.4.6  
[49] nleqslv_3.3.1        MASS_7.3-48          scales_0.5.0         timeSeries_3042.102 
[53] judgr_0.0.4          hms_0.4.0            SparseM_1.77         yaml_2.1.16         
[57] memoise_1.1.0        kimisc_0.4           fBasics_3042.89      segmented_0.5-3.0   
[61] stringi_1.1.6        RSQLite_2.0          junior_0.0.12        genefilter_1.60.0   
[65] S4Vectors_0.16.0     BiocGenerics_0.24.0  stable_1.1.2         rlang_0.1.6         
[69] pkgconfig_2.0.1      bitops_1.0-6         pracma_2.1.1         evaluate_0.10.1     
[73] lattice_0.20-35      bindr_0.1            cowplot_0.9.2        bit_1.1-12          
[77] plyr_1.8.4           magrittr_1.5         R6_2.2.2             IRanges_2.12.0      
[81] pillar_1.1.0         haven_1.1.1          foreign_0.8-69       survival_2.41-3     
[85] RCurl_1.95-4.10      modelr_0.1.1         crayon_1.3.4         rmarkdown_1.8       
[89] grid_3.4.3           blob_1.1.0           digest_0.6.14        xtable_1.8-2        
[93] stats4_3.4.3         munsell_0.4.3

#2

Is your table in the dbo schema, or is it in some other schema? (just floating guesses at the moment)


#3

Can you perform simple “SELECT TOP 10 * FROM Table_name” query like this without any errors?

variable <- dbGetQuery(connection,query_text)

#4

It turns out I had a few issues. One of my issues was trying to using LIMIT 10 instead of SELECT TOP 10 with a dbGetQuery. I’m not too familiar with databases so didn’t realize that wouldn’t work with SQL Server. Another one of my issues was a transposition typo in a cryptically named table, and one of my issues was that I did in fact need to change schemas with in_schema as nutterb I think was aiming at.

Problem solved!

Thanks everyone for the pointers!