I'm trying to use
odbcDriverConnect('driver={SQL Server};server=servername\instancename,port;database=testing;username=abc;password=123456')
to connect remote database server (sql server 2008). But I got
[RODBC] ERROR: state 08001, code 17, message [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
error. Any idea?
I can use
odbcDriverConnect('driver={SQL Server};server=localhost;database=testing;trusted_connection=true')
to connect my local database server (sql server 2008).
The correct syntax should be:
odbcDriverConnect('driver={SQL Server};server=servername\\instancename,port;database=testing;uid=abc;pwd=123456')
If you use Windows Authentication method:
odbcDriverConnect('driver={SQL Server};server=servername\\\\instancename,port;database=testing;trusted_connection=true')
Slash seems like an escape character, escaping slash works.
For the Microsoft ODBC Driver 11 for SQL Server on Linux with RODBC version 1.3-7 in R version 3.0.1 none of the above answers worked. What did work however was the following:
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;
Server=127.0.0.1; Database=MyDBName;
Uid=MyName; Pwd=XXXX")
(put in the relevant IP address, database name etc.).
In case of a trusted connection:
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;
Server=127.0.0.1; Database=MyDBName;
Uid=MyName; Pwd=XXXX; trusted_connection=yes")
trusted_connection
will only listen to "yes" or "no" and not to "true" and "false"
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With