Connecting to Azure SQL via R

Question

The code below lets me connect to Azure SQL server via R. However, I can only access a 'master' database and not the two databases below which I created. Tables show as blank, any ideas? Thanks

        library(RODBC) library(dplyr) library(DBI) 

    conn <- RODBC::odbcConnect(
        > dsn = "XXX",   uid = "XXX",   pwd = "XXX" )
        > 
        > db_list <- RODBC::sqlQuery(   channel = conn,   query = "SELECT name
        > FROM Master..sysdatabases")
        > 
        > test <- sqlTables(conn)

Answer 1

I would recommend that you use the "odbc" package, which to my experience is the most robust and versatile R package for connection to databases. When making a connection with this package you write explicitly which database you connect to.

# Library
library(odbc)

# Creating connection
my_connection <- dbConnect(drv = odbc::odbc(),
                           Driver = "SQL Driver",
                           server = "azuredatabase.azure.com,port",
                           database = "databasename",
                           uid = "user",
                           pwd = "password")