How to connect to SQL server database in R

Question

I am trying to connect to a SQL Sever database using R, but am not sure about the details of the query string. I usually use SQL Server Management Studio on SQL Server 2008 and connect using single sign-on. I found the example below

myconn <- odbcDriverConnect(connection="Driver={SQL Server 
Native Client 11.0};server=hostname;database=TPCH;
trusted_connection=yes;")

I get the following warning message

Warning messages:
1: In odbcDriverConnect(connection = "Driver={SQL Server \nNative Client 11.0};server=hostname;database=TPCH;\ntrusted_connection=yes;") :
  [RODBC] ERROR: state IM002, code 0, message [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
2: In odbcDriverConnect(connection = "Driver={SQL Server \nNative Client 11.0};server=hostname;database=TPCH;\ntrusted_connection=yes;") :
  ODBC connection failed

How can I find the specifics I need?

Answer 1

odbc, . , , "odbc" " ". , odbc 'con1', :

con<-odbcConnect('con1') #opening odbc connection


df<-sqlQuery(con, "select  *
                         from ssiD.dbo.HOURLY_SALES
                         ") #querying table


close(con)

Answer 2

.

library(RODBC)
dbconnection <- odbcDriverConnect("Driver=ODBC Driver 11 for SQL Server;Server=server_name; Database=table_name;Uid=; Pwd=; trusted_connection=yes")
initdata <- sqlQuery(dbconnection,paste("select * from MyTable;"))
odbcClose(channel)

. . RODBC odbcDriverConnect()

https://www.simple-talk.com/sql/reporting-services/making-data-analytics-simpler-sql-server-and-r/

Answer 3

"RSQLServer" . RStudio :

conn <- DBI::dbConnect(RSQLServer::SQLServer(),
                 server = '<server>', 
                 port = '<port>',
                 properties = list(
                   user = '<user>',
                   password = '<password>'
                 ))

, db_list_tables(conn) .

Answer 4

ODBC. "", "odbc". " (ODBC)". "", SQL Server. - . . , .

:

odbcDriverConnect("Driver=SQL Server Native Client 11.0;Server=<IP of server>;Database=<Database Name>;Uid=<SQL username>;Pwd=<SQL password>")

Answer 5

The problem is simpler. The big key is \nin the error message. Something repeated your connection string, so the driver name now has a newline character. This will not match the registered driver name. Then comes pain and suffering. Make sure the entire connection string is on the same line.

I often use: driver={SQL Server Native Client 11.0}; ...

and it works very well. Much better than relying on predefined connection names.