11

I am trying to import some data directly into R from a SQL Database. I have set up the connection without an issue but extracting the data has been somewhat challenging. I will try and make this as clear as possible because I don't think I can make it reproducible due to some sensitive information. When I run the following line I get the data I wanted:

myconn <- odbcConnect("COMPANYNAME", uid = "support", pwd = "password111?")
sqlQuery(myconn, "SELECT * FROM Metrics")

However, when I include a specific database I want to extract from within the server I get an issue:

sqlQuery(myconn, "USE companyname_clientname SELECT * FROM Metrics")

Where companyname_clientname is the database. Instead of the data I want, I get 

character(0)

I know that introducing "USE companyname_clientname" is the issue, I just don't know why or how to fix it. If there is anything that would make this easier for you to help me let me know and I will accommodate.

Harrison Jones
  • 2,256
  • 5
  • 27
  • 34
  • ODBC connections will only support a sort of "lowest common denominator" SQL syntax. I'm not terribly familiar with sql server; would `FROM companyname_clientname.Metrics` be equivalent? – joran Jul 17 '13 at 15:48
  • Unfortunately it's not equivalent. What do you mean by "lowest common denominator" SQL Syntax? – Harrison Jones Jul 17 '13 at 15:51
  • I just mean that ODBC is meant to work with virtually _every_ SQL db out there, so many of the language extensions particular to a certain db won't work. I wouldn't count on anything beyond plain old select, insert, update and delete. – joran Jul 17 '13 at 15:55
  • 1
    If anything's going to work, I would guess it would be changing the name of the db you connect to in `odbcConnect`, but that's just speculation. – joran Jul 17 '13 at 15:57
  • Yeah I'll try through `odbcConnect` – Harrison Jones Jul 17 '13 at 16:09
  • Is the `USE` query separate from the `SELECT` query? If so, does inserting a semicolon between the queries help? – Blue Magister Jul 17 '13 at 17:58
  • The semicolon didn't help, I still received the output of `character(0)` – Harrison Jones Jul 17 '13 at 20:38

3 Answers3

13

I had this same issue and found that there were 2 contributing reasons:

  1. Like others pointed out, SET NOCOUNT ON needs to be at the start of your query
  2. That alone didn't fix the issue of returning a character(0). I found that my SQL query also had a PRINT statement outputting a variable for debugging purposes. Removing the PRINT statement then successfully returned an output, in my case a temp table.

Hope this helps others!

mkirzon
  • 413
  • 5
  • 9
10

In case anyone else comes across this issue, the character(0) can also be a result of warnings in SQL, such as 

Warning: Null value is eliminated by an aggregate or other SET operation.

This can be fixed by including SET ANSI_WARNINGS OFF at the top of the SQL script.

Eddie
  • 101
  • 1
  • 5
8

I had the same issue. The character(0) seems to be as a result of the SQL using loops which the RODBC doesnt complete.

The solution that got me right was to add set nocount on to the start of the SQL statement. If its the same issue, it should do the trick.

Farzad
  • 842
  • 2
  • 9
  • 26
Calven
  • 81
  • 1