R connecting to Local SQL Server

3
votes

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

2
sqlsql-serverr
Also, you can see a line break, \n in the connection string since you break it in R which may be calling an unrecognized server instance. Try running connection string on one line. – Parfait
updated error message without the line break – dluck
See connection string examples here. You seem to be connecting to a local instance and not explicit server. Try Integrated Security=true; instead of trusted_connection=yes. – Parfait
I made the change and still received the same error message. – dluck

2 Answers

2
votes

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.

1
votes

I had the same error but it was caused by a different problem. For some reason {SQL Server} did not allow me to connect to Local SQL Server but changing it to {SQL Server Native Client 11.0} worked.

library(RODBC)
dbconnection <- odbcDriverConnect("Driver={SQL Server Native Client 11.0};Server= (local)\\SQLSERVER2016;Database=Test_DB;trusted_connection=yes")

Turns out that SQL Server driver known as SQL Server ODBC Driver is an old driver whereas SQL Server Native Client contains both the SQL OLE DB provider and SQL ODBC driver to support native connectivity to SQL Server.