Extracting Data from SQL Server Table within a Schema

I am having a hard time writing and extracting data from SQL Server in RStudio. My database hierarchy is as follows:

• LDS
  • LDS
    • HIG
      • table_needed

I’ve tried:

ch <- DBI::dbConnect(odbc::odbc(), "LDS")
q1<- sqlQuery(ch,("
         SELECT 
          AP_NAME,AP_Dep
          FROM [hig].[tb_pp_oz_CURR]
          Where AP_Dep <> 'K'|'C';
          ")

also

ch<- DBI::dbConnect(odbc::odbc(), "LDS")
q1<- sqlQuery(ch,("
         SELECT 
          AP_NAME,AP_Dep
          FROM in_schema("HIG","tb_pp_oz_CURR")
          Where AP_Dep <> 'K'|'C';
          ")

From what I’ve read, my problem is that my LDS Schema is set to default. Is there a way to default my LDS schema? If not, is there a certain code I can use?

Answer

Here’s what I typically do:

library(dplyr)
library(DBI)

con <- DBI::dbConnect(odbc::odbc(), "LDS")

Then you can write your SQL as you normally would constructing the path to your schema as you would writing native SQL.

my_query <- 'SELECT 
          AP_NAME
         ,AP_Dep
          FROM [LDS].[LDS].[HIG].[tb_pp_oz_CURR]
          Where AP_Dep <> 'K'|'C''

Then you could pass that to your connections with:

returned_data <- tbl(con, sql(my_query)) %>% collect()

The other option is that you use dbplyr and do something like

library(dbplyr)

returned_data <- tbl(con, in_schema("HIG", "tb_pp_oz_CURR")) %>% 
    filter(!AP_Dep %in% c("K", "C")) %>%
    select(AP_NAME, AP_Dept) %>%
    collect()