CODEWITHSUNDEEP
sql-serverrrodbc
Joyce Lee
Joyce Lee

Reputation: 21

R RODBC SQL Server Connection

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

Upvotes: 0

Views: 2736

Answers (1)

Emiel
Emiel

Reputation: 175

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="")

Upvotes: 0

Related Questions