sql server - R-Bug? R RODBC sqlFetch returns partial table, sqlQuery returns incorrect results when connection broken silently between RackSpace and Azure SQL -

r users,

i on rackspace running windows server 2012r2. intend use rodbc sqlfetch/sqlquery retrieve table azure sql.

i first created system dsn, dsnx, using odbc data source administrator.

i used rstudio create connection:

xsqlchannel <- odbcconnect(dsn = 'dsnx', uid = "myuid", pwd = "mypwd") 

and tried download data:

xdfabc <- sqlquery(xsqlchannel, 'select * dbo.abc') 

the function returned silently. however, xdfabc contains partial data of dbo.abc - e.g., dbo.abc contains 22871017, xdfabc contains 1600 rows only.

and, when re-run same code 2nd attempt,

xdfabc <- sqlquery(xsqlchannel, 'select * dbo.abc') 

it reports error:

 "08s01 0 [microsoft][sql server native client 11.0]communication link failure" "[rodbc] error: not sqlexecdirect 'select * dbo.abc'" 

i tried use sqlfetch() download full dataset, wrap of sqlquery(). again, first attempt returns partial datasets no error message, second attempt returns error message.

my guess connection broken when partial data downloaded, returns partial dataset without reporting error. and, when tried download again, reports error.

the connection issue between rackspace server , azure sql server, has nothing r. tried use python download, fails , report connection lost errors.

i concerned in r sqlfetch , sqlquery did not report error when download not retrieved full datasets expected. should sqlfetch , sqlquery check connection still active after query, can confident query runs successful completely?

however, tried:

count_xdfabc <- sqlquery(xsqlchannel, 'select count(*) dbo.abc') 

it returns 17000000 @ first time, , 18000000 @ second time, third time, ..., tenth time. never report error message.

please let me know whether experienced similar issue.

thank you.

yg