Character (0) is returned when using RODBC

Question

I am trying to import some data directly into R from an SQL database. I made the connection without any problems, but retrieving the data was somewhat complicated. I will try to make this as clear as possible, because I don’t think I can make it reproducible due to some important information. When I run the following line, I get the data I need:

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

However, when I turn on a specific database that I want to extract from inside the server, I get a problem:

 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 a problem, I just don't know why and how to fix it. If there is anything that will facilitate your help, let me know and I will accept.

Answer 1

In case someone else encounters this problem, character(0) can also be the result of warnings in SQL such as

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

This can be SET ANSI_WARNINGS OFF including SET ANSI_WARNINGS OFF at the beginning of the SQL script.

Answer 2

I had the same problem. character(0) seems to result from using SQL using loops that RODBC does not complete.

The solution that got me right was to add set nocount on to the top of the SQL statement. If this is the same, he should do the trick.

Answer 3

I had the same problem and I found that there are 2 reasons:

• As others pointed out, SET NOCOUNT ON should be at the beginning of your request
• Only this did not solve the problem of returning the character (0). I found that my SQL query also had a PRINT statement that outputs a variable for debugging purposes. Having removed the PRINT instruction, you have successfully returned the output, in my case, a temporary table.

Hope this helps others!