[R] RODBC & MS SQL Server: repeated calls to sqlGetResults() problem

[Matthew Dowle]

Dear list,

RODBC is mostly working very well on Windows XP talking to MS SQL Server.
However, when trying to retrieve a result set in repeated batches the first
batch returns results ok, but then subsequent calls return no data (see code
below). I tried setting believeNRows=FALSE both in odbcConnect() and in
sqlGetResults() but this doesn't appear to make any difference. Also
odbcFetchRows() suffers the same problem.  The documentation states
"sqlGetResults is a mid-level function. It should be called after a call to
odbcQuery and used to retrieve waiting results into a data frame. Its main
use is with max set to non zero it will retrieve the result set in batches
with repeated calls."

Have I mis-understood? Is this a driver problem? Something else?  Any
help/advice much appreciated.

Many thanks in advance,

Matthew


> channel = odbcConnect("MY DSN")
> odbcGetInfo(channel)
[1] "Microsoft SQL Server version 08.00.0760. Driver ODBC version 03.52"
> odbcQuery(channel, "select * from TEST")    # Table TEST contains a single
column by 100 rows
[1] 1
> sqlGetResults(channel, max=10)
  last_update
1  2004-02-06
2  2004-02-06
3  2004-02-06
4  2004-02-06
5  2004-02-06
6  2004-02-06
7  2004-02-06
8  2004-02-06
9  2004-02-06
10 2004-02-06
> sqlGetResults(channel, max=10)
[1] last_update
<0 rows> (or 0-length row.names)    # why is this empty?
> 
> version
         _              
platform i386-pc-mingw32
arch     i386           
os       mingw32        
system   i386, mingw32  
status                  
major    1              
minor    9.0            
year     2004           
month    04             
day      12             
language R              
>