Establish Microsoft SQL Server Connection with R/RStudio

Question

I am trying to connect R with Microsoft SQL server. I have used Toad for SQL Server 6.8 so far for my queries. However, for some other analysis (which can be easily performed in R) I want to connect database with R.

I have tried R function "dbconnect" with providing server name and database name. See query below:

   odbc_con <- dbConnect(odbc::odbc(),
                  Driver = "SQL Server",
                  Server = "xxxxx",
                  Database = "yyyyy", 
                  Uid = 'US\dhrdesai',
                  Pwd = rstudioapi::askForPassword("Database password"),
                  Port = 1433)

However, I got following errors:

Error: nanodbc/nanodbc.cpp:950: IM002: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

and

Error: unexpected ')' in " Port = 1433)"

Have anyone faced the same or know any other way to connect R with SQL server.

Possible duplicate of [Connect R to a SQL Server database engine](https://stackoverflow.com/questions/39401230/connect-r-to-a-sql-server-database-engine)? — Thom A, Jan 15 '19 at 10:17
Your last error signals me that there's something wrong in your code before this call. Can you share the entire script? Have you made sure that there's no funky business going on when sending commands to the console? Have you tried restarting your R session? — Roman Luštrik, Jan 15 '19 at 10:28

xhr489 · Answer 1 · 2019-01-15T10:48:40.393

You need to use a double backslash \\ every time you see a \. I just made my connection work yesterday with the following code. Also pehaps you have not installed all the packages that is required.

library(DBI)
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")

Establish Microsoft SQL Server Connection with R/RStudio

1 Answers1