How do I create a datasource and to connect this from R?

Question

I don't know where are my code error. I created a datasource and after included in a RODBC function.

The datasource configuration is:

• Name: sqldatabases
• Driver: SQL Server Native Client 11.0
• Server: BRDCVMDB01
• Database in SQL Server: ANALYTICS_FA_CP_SPRO

I hope you can help me!

Thank you!

First Attempt

library(RODBC)

connect <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=sqldatabases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;")

Warning messages: 1: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=databases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : [RODBC] ERROR: state 08001, code 53, message [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. 2: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=databases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : [RODBC] ERROR: state HYT00, code 0, message [Microsoft][SQL Server Native Client 11.0]Login timeout expired 3: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=databases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : [RODBC] ERROR: state 08001, code 53, message [Microsoft][SQL Server Native Client 11.0]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. 4: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=databases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : ODBC connection failed

2nd Attempt

cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=databases;trusted_connection=yes;")

Warning messages: 1: In for (i in seq_along(args)) { : closing unused RODBC handle 14 2: In for (i in seq_along(args)) { : closing unused RODBC handle 12 3: In for (i in seq_along(args)) { : closing unused RODBC handle 8 4: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=databases;trusted_connection=yes;") : [RODBC] ERROR: state 28000, code 18456, message [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'ATRAME\mjordao'. 5: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=databases;trusted_connection=yes;") : [RODBC] ERROR: state 42000, code 4060, message [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "databases" requested by the login. The login failed. 6: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=databases;trusted_connection=yes;") : ODBC connection failed

3rd Attempt

cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;")

cn <- odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=sqldatabases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;")

Warning messages: 1: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=sqldatabases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : [RODBC] ERROR: state 08001, code 53, message [Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [53]. 2: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=sqldatabases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : [RODBC] ERROR: state HYT00, code 0, message [Microsoft][SQL Server Native Client 11.0]Login timeout expired 3: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=sqldatabases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : [RODBC] ERROR: state 08001, code 53, message [Microsoft][SQL Server Native Client 11.0]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. 4: In odbcDriverConnect(connection = "Driver={SQL Server Native Client 11.0};server=sqldatabases;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;") : ODBC connection failed

Answer 1

I had ran into this error before, I would recommend check the actual running driver's name (for instance, mine is actually named SQL Server instead of the SQL Server Native Client, it took me a while to figure that out). And you want to make sure your SQL server is running.

And for the code, I recommend the following little changes :

Your original:

odbcDriverConnect(connection="Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=databases;trusted_connection=yes;")

change to:

odbcDriverConnect('Driver=SQL Server Native Client 11.0;server=BRDCVMDB01;database=ANALYTICS_FA_CP_SPRO;trusted_connection=TRUE;')

I hope this can help :)

Answer 2

With ODBC, there are two main ways to connect: either with a Data Source Name (DSN) which you indicate you use or a DSN-less driver version. In RODBC this differs with two methods odbcConnect and odbcDriverConnect. See usage section in docs.

Right now you conflate the two in running DSN-less driver connections but referencing a DSN. Simply connect with appropriate settings. Actually one of your coded lines (first of 3rd attempt) without warning messages should should have potentially worked:

# DSN CONNECTION
cn <-odbcConnect('sqldatabases;trusted_connection=yes')

# DRIVER CONNECTION
cn <- odbcDriverConnect("Driver={SQL Server Native Client 11.0};server=BRDCVMDB01;database=ANALYTICS_FA_CP_SPRO;trusted_connection=yes;")