R connecting to Local SQL Server

I cannot connect to my local sql server through Rstudio. I am able to connect to a SQL Server that my company hosts and I can connect to my local SQL Server through Management Studio. What am I doing wrong?

Is there something I need to do to connect locally? The (LocalDB)v11.0 was the exact server name that I connect to my local SQL Server through Management Studio.

install.packages("RODBC")

library(RODBC)

dbconnection <- odbcDriverConnect("Driver=SQL Server;Server=(LocalDB)v11.0;Database=NBA;trusted_connection=yes")

The error message I get is the following:

Warning messages:
1: In odbcDriverConnect(“Driver=SQL Server;Server=(LocalDB)v11.0;Database=NBA;trusted_connection=yes”) :
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

2: In odbcDriverConnect(“Driver=SQL Server;Server=(LocalDB)v11.0;Database=NBA;trusted_connection=yes”) :
[RODBC] ERROR: state 01000, code 53, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).

3: In odbcDriverConnect(“Driver=SQL Server;Server=(LocalDB)v11.0;Database=NBA;trusted_connection=yes”) : ODBC connection failed

Answer

try double backslash… I tried the following and it’s working fine:

library(odbc)
dbconnection <- odbcDriverConnect("Driver=SQL Server;Server=(local)\SQLSERVER2016;Database=Test_DB;trusted_connection=yes")
data <- sqlFetch(dbconnection, 'dbo.temp_table', colnames=FALSE, rows_at_time=1000)

Thanks.

Advertisement

Answer