Unable to query Azure SQL Server from RStudio

Question

I am trying to query an SQL Server database deployed on Azure from RStudio.

I established a connection. I can see all of the tables and get a preview of the data from the RStudio GUI. However, when I try to query the data I get an error.

library(dplyr)
library(odbc)

con <- dbConnect(odbc(),
                 Driver = "SQL Server",
                 Server = "#.database.windows.net",
                 Database = "loremipsum",
                 UID = "loremipsum",
                 PWD = "loremipsum", 
                 Port = 1433)

First query I tried:

users <- as.data.frame(sqlQuery(con, "SELECT * FROM AspNetUsers"))

The error I got:

Error in sqlQuery(con, "SELECT * FROM AspNetUsers") : 
  first argument is not an open RODBC channel

I also tried this query

result <- dbSendQuery(con, "SELECT * FROM AspNetUsers")
first_100 <- dbFetch(result, n = 100)

With the following error:

Error in result_fetch(res@ptr, n) : 
  nanodbc/nanodbc.cpp:2966: 07009: [Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index

It strange because the connection must be valid, given that it gets access to the data through the GUI.

Any hints?

Does this answer your question? [First argument is not an open RODBC channel](https://stackoverflow.com/questions/52721747/first-argument-is-not-an-open-rodbc-channel) — Thom A, Dec 22 '19 at 14:39
@Larnu somewhat. I eventually solved the issue by switching to another libary - RODBC. It seems that thats a bug in the way odbc handles character columns as point [here] (https://stackoverflow.com/questions/45001152/r-dbi-odbc-error-nanodbc-nanodbc-cpp3110-07009-microsoftodbc-driver-13-fo) — Prometheus, Dec 22 '19 at 14:52

score 1 · Accepted Answer · answered Dec 23 '19 at 01:18

Congratulations you have solved the issue:

I eventually solved the issue by switching to another libary - RODBC. It seems that thats a bug in the way odbc handles character columns as point:R DBI ODBC error: nanodbc/nanodbc.cpp:3110: 07009: [Microsoft][ODBC Driver 13 for SQL Server]Invalid Descriptor Index

I post this as answer and this can be beneficial to other community members.

Unable to query Azure SQL Server from RStudio

1 Answers1