Loading table into R from SQL Server database

Question

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:

Thanks for your help.

Harris_1129021 is the database, dbo is the schema, and Applicant is the table name. The Database is typically specified in the connection string or DSN. So should probably be in_schema("dbo", "Applicant"). But dbo is a kind of default schema, so you don't really need to specify it. — David Browne - Microsoft
– David Browne - Microsoft, Commented Jan 6, 2022 at 17:36
Thanks David. So do I put the Harris_1129021 database name in the dbConnect command? I tried that, but did not work. — mr_puddles
– mr_puddles, Commented Jan 6, 2022 at 17:43

mr_puddles · Accepted Answer · 2022-01-06 18:00:47Z

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"))

Kudos for submitting the answer to your own question! This is the way :-)

Collectives™ on Stack Overflow

Loading table into R from SQL Server database

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related