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.

What's the schema definition of the CertainReg table on the SQL Server side? — AlwaysLearning
– AlwaysLearning, Commented Aug 28, 2024 at 1:46
unload the table to s3 then issue a copy command via linked query. I was stuck in sql 2k8 so I did that with xp_cmdshell :) — user433342
– user433342, Commented Aug 28, 2024 at 4:47
Does it work if you do insert into openquery(redshift, 'select * from targettable') select * from source these linked servers are very finicky, although it looks like redshift messed something up in implementation. Also, check so column number match between servers — siggemannen
– siggemannen, Commented Aug 28, 2024 at 6:16
@siggemannen Both of these statements fail with the same cursor error - insert into OPENQUERY (REDDW,'select * from [red_dw].[marketing].[certainreg]') select * from CertainReg and - insert into OPENQUERY (REDDW,'select registrationcode,eventcode,dateregistered from [red_dw].[marketing].[certainreg]') values('4', '712', getdate() ) — user3866578
– user3866578, Commented Aug 28, 2024 at 11:49
@AlwaysLearning Here's the table definition on the sql side: TABLE [dbo].[CertainReg]( [RegistrationCode] [varchar](30) NULL, [EventCode] [varchar](30) NULL, [DateRegistered] [datetime] NOT NULL ) I don't believe this is the issue though as an insert with the 3 values will also fail when using the insert into format — user3866578
– user3866578, Commented Aug 28, 2024 at 11:53

Jeremy Caney · Accepted Answer · 2024-09-11 22:20:32Z

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;

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related