R RODBC SQL Server Connection

Question

I use windows authentication to connect to sql server. And I'm trying to connect to the sql server table in R. But R is not connecting to the database. I also tried typing in my windows login for uid and pwd. still no luck.

library(RODBC)  
driver.name <- "SQL Server"
db.name <- "dw-xxx"
host.name <- "xx-xxx"
port <-"xxxx"
server.name <-"dw-xx"
con.text <- paste("DRIVER=",driver.name,
              ";Database=",db.name,
              ";Server=",server.name,
              ";Port=",port,
              ";PROTOCOL=TCPIP",
              ";trusted_connection=true",
              sep="")
con1 <- odbcDriverConnect(con.text)

Warning messages:
1: In odbcDriverConnect(con.text) : [RODBC] ERROR: state 42000, code 4060, message [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "dw-xxx" requested by the login. The login failed.
2: In odbcDriverConnect(con.text) : [RODBC] ERROR: state 01S00, code 0, message [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute
3: In odbcDriverConnect(con.text) : ODBC connection failed

> odbcGetInfo(con1)

Error in odbcGetInfo(con1) : argument is not an open RODBC channel

RStudio : Version 1.0.153
Microsoft SQL Server Management Studio 14.0.17177.0

I have not seen PROTOCOL in connection strings. Try examples here: connectionstrings.com/microsoft-sql-server-odbc-driver — Parfait
andersspur.wordpress.com/2013/11/26/… I found this website, and sestup connection according to the tutorial. and now it's saying my table is not found on channel — Joyce Lee
Yes, that's building a DSN (pre-built, tailored connection file) where here you connect directly to driver. With what command does that error of table and channel? That may be a different issue than this connection one. — Parfait

Emiel Emiel · Accepted Answer · 2020-05-11T13:48:03

I got this same problem. Apparantly the PORT format was not accepted. It was working when I moved the port to the server part likes this (giving SERVER=server,port):

con.text <- paste("DRIVER={",driver.name,"}",
          ";Database=",db.name,
          ";Server=",server.name,
          ",",port
          ";PROTOCOL=TCPIP",
          ";trusted_connection=true",
          sep="")

R RODBC SQL Server Connection

1 Answers