OPENQUERY throwing "Out of memory" error: limit rows until all are loaded

Question

In SQL Server 2016 I have a stored procedure. In this SP I want to get all data from a view from a linked server (PostgreSQL) by using the following statement:

INSERT INTO myTable
SELECT Field1,
    Field2,
    Field3,
    ...
FROM OPENQUERY(myServer, 'SELECT * FROM myDatabase.mySchema.myView')

When I use it like this, I'm getting the following error message after a few minutes:

Out of memory while reading tuples.

I changed the SELECT statement in OPENQUERY to get only the first 1000000 rows which worked fine:

SELECT * FROM myDatabase.mySchema.myView ORDER BY Field1 LIMIT 1000000

Now I am unsure what the most practical way to get all data would be. I could insert the first 1000000 rows and then insert the next 1000000 using OFFSET. But I don't think this would be a nice solution as I don't know what the total number of rows is. A loop would be another way but I really don't know if this would be the easiest way to achieve what I want.

Any help would be appreciated.

Answer 1

i think you are using odbc driver for creating linked sever .It is issue with psqlODBC driver memory configuration .

You change odbc driver setting opening your data sources

Press "Configure", then in the opened data source details

"Options" section select "Datasource"

and in the opened window check the "use declare/fetch".