Error copying data from SQL Server to Redshift via a linked server connection

Question

We have local SQL Server data that we would like to sync to Redshift nightly. I can write individual values to the Redshift server, but can not can do a standard insert of data from a local table

Using this type command single line inserts work as expected:

EXEC('INSERT INTO [red_dw].[marketing].[CertainReg] (registrationcode, eventcode, dateregistered) (SELECT '4', '2', GETDATE())') AT REDDW

Running this code:

INSERT INTO [REDDW].[red_dw].[marketing].[certainreg] (registrationcode, eventcode, dateregistered) 
    SELECT * 
    FROM CertainReg

throws the following error:

OLE DB provider "MSDASQL" for linked server "REDDW" returned message "Unspecified error".
OLE DB provider "MSDASQL" for linked server "REDDW" 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 23
The OLE DB provider "MSDASQL" for linked server "REDDW" could not INSERT INTO table "[REDDW].[red_dw].[marketing].[certainreg]".

Can you tell what is wrong with the code or is there a better way to sync tables to Redshift nightly. I believe I am missing something basic, but lot's of searching the web has not produced any results.

Answer 1

Working with Amazon support, they stated it was not possible to do the direct insert from one table to another. They provided the workaround below, which does work, though it is not ideal. Here's their response:

---

Firstly, please allow me to inform you that, as a known exception that EXEC with Insert does not support with Select operation in Redshift.

I would like to inform you that, I just received an update from the internal team and they have worked out to INSERT SQL Server data into Redshift via a combination of Dynamic SQL and EXECUTE() AT LinkedServer. Please refer the below working example query and see if it works from your end.

DECLARE @id AS INT;
DECLARE @name AS VARCHAR(50);
DECLARE @sql NVARCHAR(max);

DECLARE personCursor CURSOR FOR
select id, first_name
from dbo.tb01;

OPEN personCursor;

FETCH NEXT FROM personCursor INTO
@id, @name;

WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql = 'insert into dev.public.sqltbdemo values(' + CAST(@id AS VARCHAR(10)) + ',' + '''' + @name + '''' +')';

PRINT @sql

EXEC(@sql) AT REDDW

FETCH NEXT FROM personCursor INTO @id, @name;
END;

CLOSE personCursor;
DEALLOCATE personCursor;