error while fetching rows in R

Question

I want to fetch some data from my SQL server in R. The way I'm doing this is,

rs=dbSendQuery(con,"myquery")
data=fetch(rs,n=-1)

this works perfectly for a small table. However, for a bigger table, the fetch command says,

Warning message:
In fetch(ms, n = -1) : error while fetching rows

The problem still remains even if I restrict my rows (n=10). So, I'm not sure if it's a timeout problem or what.

What might be the case?

data shows,

1] creator ratio  
<0 rows> (or 0-length row.names)

Answer 1

There are couple of points I want to mention which can help OP in identifying and fixing problem.

1) Do not use fetch. Instead use dbFetch. The quote from R-help suggests as

fetch() is provided for compatibility with older DBI clients - for all new code you are strongly encouraged to use dbFetch()

2) Execute your query from Query Editor in SQL Server Management Studio and check for performance. Fine tune tables used query for indexes. Once ready and happy try it from R

3) If query is selecting many columns then it would be good to first try selecting just one or two columns.

4) I hope you freeing resources and closing connection in later part of your code. It can be done like:

# Free all resources
dbClearResult(rs) 
# Close connection
dbDisconnect(con)