Connecting to Microsoft SQL Server with R (view is in a database in Microsoft SQL Server Management Studio (SSMS)

Question

I have reading rights to some "Views" (tables) in Microsoft SQL Server Management Studio (SSMS). I connect, make my query and export a files as csv and then read it in R. Now I would like to make my queries inside R. I have spendt some hours reading about this but still don't get how my code should be like to make the connection. I use the packages in tidyverse alot and have seen there is a dbplyr pacakge.

The informations when I connect to SSMS are: 1) Server type: Database Engine 2) Server name: I have this. 3) Authentication: Windows Authentication. 4) User name: I have this.

I don't need a password (maybe it is because I am at my works network?)

Can someone please write the complete code (including which libraries I have to load) for accessing the views in SSMS. So I have installed these packages:

library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)

Then I run the following code:

con <- dbConnect(odbc::odbc(), 
                 Driver = "SQL Server", 
                 Server = "something", 
                 user = "something\\my_username",
                 Trusted_Connection = "True")


dbListTables(con)

But I cannot se the views or tables that I need. It is a huge database. Actually when I use SSMS there are many databases and I only have access to some view inside that database.

Hillari

all the info here: db.rstudio.com/odbc – Substantive

Check here solutions.posit.co/connections/db/databases/… – Nahtanha

Answer 1

Well I solved the problem (thanks to the the link that denis refered to in the comment). Here is my code:

library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)

con <- dbConnect(odbc::odbc(), 
                 Driver = "SQL Server", 
                 Server = "path\\path", # remember \\ if your path has a \ 
                 Database = "the_database_name",
                 user = "your_user_name", # remember \\ if your username has a \
                 Trusted_Connection = "True")  

Hillari