Search code examples
rsql-serverodbcdbplyr

Loading table into R from SQL Server database

I get the following error when I try to load a table from a SQL Server in an Azure VM. I know the connection is successful, but I'm unable to get my syntax correct for the tables I want to load into R environment.

Error: nanodbc/nanodbc.cpp:1655: 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'Harris_11292021.dbo.Applicant'. [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

'SELECT *
FROM "Harris_11292021"."dbo.Applicant" AS "q14"
WHERE (0 = 1)'

I'm trying to load the table Applicant. Is the issue that the connection is to applied_systems and there is a further database and schema level below that before you get to table?

I tried to deal with that using in_schema command, but think I have implemented it wrong.

library(odbc)
library(tidyverse)

# Data connection to database --------------------------------------
con_applied <- dbConnect(odbc::odbc(), "applied_systems", timeout = 10)

# Load table -------------------------------------------------------------
policy_master <- tbl(con_applied, in_schema("Harris_11292021", "dbo.Applicant"))

Here is a screenshot of the connection:

schema

Thanks for your help.

Solution

  • Got it. Add database name in the dbConnect statement.

    # Data connection to database backup --------------------------------------
con_applied <- dbConnect(odbc::odbc(), "applied_systems", timeout = 10, database = "Harris_11292021")

# Load tables -------------------------------------------------------------
policy_master <- tbl(con_applied, in_schema("dbo", "Applicant"))