Cannot insert data over Linked Server into Redshift

Question

We've setup a new linked server using the Amazon Redshift ODBC driver (x64) in SQL Server 2014 Express. SELECT statements work as expected using OPENQUERY, eg:

SELECT * FROM OPENQUERY(Redshift_Linked_Server, 'SELECT * FROM tableA')

However we cannot use the INSERT functionality of OPENQUERY, eg:

INSERT OPENQUERY(
    Redshift_Linked_Server, 
    'SELECT SingleValue from tableA')
VALUES ( '2' )

When run we get the following error message back:

OLE DB provider "MSDASQL" for linked server "Redshift_Linked_Server" returned message "Unspecified error".

OLE DB provider "MSDASQL" for linked server "Redshift_Linked_Server" returned message "Transaction cannot have multiple recordsets with this cursor type. Change the cursor type, commit the transaction, or close one of the recordsets.".

Msg 7343, Level 16, State 2, Line 9 The OLE DB provider "MSDASQL" for linked server "Redshift_Linked_Server" could not INSERT INTO table "[MSDASQL]".

This happens when inserting from a single row using VALUES or when inserting from a data set using SELECT.

Is this a driver issue or can the linked server be configured to prevent this?

Answer 1

I solved this issue by using EXECUTE ('INSERT INTO TABLENAME1(COL1,COL2) SELECT COL1,COL2 FROM TABBLENAME2 ') AT

Hope this is useful to someone out there.