1

I'm trying this simple script.

library(RODBC)
channel <- odbcDriverConnect("driver={SQL Server};server=ServerName;database=TestDB;uid=;pwd=")
initdata<- sqlQuery(channel,paste("select * from MyTable;"))
odbcClose(channel)

There is no uid or password on my DB.

I keep getting this error:

Connection failed: SQL State: '01000' SQL Server Error: 53 SQL State: '08001' SQL Server Error: 17 Server does not exist or access denied.

There must be some setting that's off, right? I know my SQL Server is working fine, and I can connect to it using several other technologies (Excel, Access, VB.NET, & C#). I guess there is some configuration in R that's not quite right.

How can I get this working?

halfer
  • 19,824
  • 17
  • 99
  • 186
ASH
  • 20,759
  • 19
  • 87
  • 200
  • I can only tell you how I connect `odbcDriverConnect("driver=SQL Server;server=someSystemDSN;database=TestDB;uid=myUID;pwd=myPWD")` Have you tried not putting `uid` and `pwd` in your connection string at all? I do not use curly brackets around `SQL Server`. Also, is `ServerName` a valid systemDSN? – cryo111 Aug 13 '15 at 15:56
  • Thanks cryo. I tried a few different things inclusing: channel <- odbcDriverConnect("driver=SQL Server;server=my_name/SQLEXPRESS;database=TestDB;") and channel <- odbcDriverConnect("driver=SQL Server;server=my_name/SQLEXPRESS;database=TestDB;TestDB;uid=;pwd=;") and channel <- odbcDriverConnect("driver=SQL Server;server=my_name/SQLEXPRESS;database=TestDB;TestDB;uid="";pwd="";") I keep getting the same error. – ASH Aug 13 '15 at 19:39

1 Answers1

1

This is what ultimately worked for me.

dbconnection<-odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server; Server=Server_Name; Database=Table_Name; Uid=; Pwd=; trusted_connection=yes")

I found the solution here. RODBC odbcDriverConnect() Connection Error

Community
  • 1
  • 1
ASH
  • 20,759
  • 19
  • 87
  • 200