Sql-server – Connecting to SQL Server from R which is Running on Remote UNIX Machine

odbcrsql serverunixunixodbc

We have a UNIX machine which includes R engine on it and installed SQL Server ODBC Driver 11.

We need to connect R to SQL Server. SQL Server currently works on another machine, so we tried to connect R to SQL Server with the code below.

library(RODBC)

dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX:1663;')

But getting that error:

Warning messages: 1: In odbcDriverConnect("driver=ODBC Driver 11 for
SQL Server;server=10.XXX.XX.XX:1663;") :

[RODBC] ERROR: state HYT00, code 0, message [unixODBC][Microsoft][ODBC
Driver 11 for SQL Server]Login timeout expired 2: In
odbcDriverConnect("driver=ODBC Driver 11 for SQL
Server;server=10.XXX.XX.XX:1663;") :

[RODBC] ERROR: state 08001, code 87, message
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]A network-related
or instance-specific error has occurred while establishing a
connection to SQL Server.
Server is not found or not accessible. Check
if instance name is correct and if SQL Server is configured to allow
remote connections. For more information see SQL Server Books Online.
3: In odbcDriverConnect("driver=ODBC Driver 11 for SQL
Server;server=10.XXX.XX.XX:1663;") :

[RODBC] ERROR: state 08001, code 87, message
[unixODBC][Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network
Interfaces: Connection string is not valid [87]. 4: In
odbcDriverConnect("driver=ODBC Driver 11 for SQL
Server;server=10.XXX.XX.XX:1663;") : ODBC connection failed

If I run telnet to SQL Server from UNIX machine, it works perfectly. I mean there is a healthy connection between these two machine.

How can we connect R to SQL Server without using DSN?

Best Answer

I changed delimiter which between server and port from : to , . It fixed my problem.

dbhandle <- odbcDriverConnect('driver=ODBC Driver 11 for SQL Server;server=10.XXX.XX.XX,1663;')
Related Question