sqlQuery failing with memory error when row_at_time too large

Asked Nov 23 '16 at 06:33

Active Nov 23 '16 at 06:58

Viewed 159 times

I am sending this query to a sql server in R using RODBC::sqlQuery

MERGE "mytable" AS Target USING ( VALUES ('myname','POLYGON ((148.0000000000000000 -20.0000000000000000, 148.0000000000000000 -20.0000000000000000, 148.0000000000000000 -20.0000000000000000, 148.0000000000000000 -20.0000000000000000, 148.0000000000000000 -20.0000000000000000))')) AS Source ("name","polygon")
                   ON (Target."name" = Source."name")
                   WHEN MATCHED THEN
                   UPDATE SET Target."polygon" = Source."polygon"
                   WHEN NOT MATCHED BY TARGET THEN
                   INSERT ("name","polygon")
                   VALUES (Source."name", Source."polygon")
                   OUTPUT $action, Inserted.*, Deleted.*

It fails when row_at_time argument of sqlQuery is more than 10, Error in odbcQuery(channel, query, rows_at_time) : 'Calloc' could not allocate memory (107374182400 of 1 bytes)

but works if row_at_time < 10. (still the query takes quite a few seconds which is surprising as the table is indexed and very small: less than 100 rows)

Any idea why? Thank you

EDIT: This is the structure of the table I am writing on:

edited Nov 23 '16 at 06:58

asked Nov 23 '16 at 06:33

RockScience

17,932
26
89
125

What version of SQL are you using? – Tim Biegeleisen Nov 23 '16 at 06:35
@TimBiegeleisen sql server 12.0.4100.1 – RockScience Nov 23 '16 at 06:36
Does the same query run directly on SQL Server? – Tim Biegeleisen Nov 23 '16 at 06:37
@TimBiegeleisen It works in R if I specify row_at_time=1 (but it is slow). The same query works in toad and seems quite fast. – RockScience Nov 23 '16 at 07:04
[This](http://stackoverflow.com/questions/30581046/rodbc-cannot-allocate-memory) might be of help. – Chirayu Chamoli Nov 23 '16 at 07:05

sqlQuery failing with memory error when row_at_time too large

0 Answers0