character(0) return when using RODBC

Question

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.

Hamon

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? – Immoderate

Unfortunately it's not equivalent. What do you mean by "lowest common denominator" SQL Syntax? – Hamon

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. – Immoderate

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. – Immoderate

Yeah I'll try through odbcConnect – Hamon

Is the USE query separate from the SELECT query? If so, does inserting a semicolon between the queries help? – Nab

The semicolon didn't help, I still received the output of character(0) – Hamon

Answer 1

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!

Caye

For me it was a PRINT statement in a stored procedure. – Slavery

Answer 2

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.

Broadminded

Another cause is a USE <DatabaseName> statement – Broadminded

Works for me. Thanks. – Ambo

Answer 3

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.

Archer

I had already tried that actually, it was in another post somewhere. Unfortunately it didn't fix my issue. – Hamon