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.

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!