Hi bit of a shot in the dark here but open for offers.

We have a Redshift database 

version
PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.901

which is hosted on the cloud now I  downloaded the recommend driver from

https://docs.aws.amazon.com/redshift/latest/mgmt/install-odbc-driver-windows.html

Installed the 64bit then gained connection.   I then setup a linked server within SQL 2012

EXEC master.dbo.sp_addlinkedserver @server = N'CONSOLE', @srvproduct=N'Redshift', @provider=N'MSDASQL', @datasrc=N'Console'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CONSOLE',@useself=N'False',@locallogin=NULL,@rmtuser=N'test',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'CONSOLE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

Which is all fine however getting some very odd behavior where on certain tables if I select a 14k location table it never retuns but if i limit it to 5 returns instantly.

Also for another table which has 200 columns I can only select 1 row otherwise it comes back with 

OLE DB provider "MSDASQL" for linked server "console" returned message "[Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
".

Yet when I use a the aginioty workbench client which I'm guessing uses its own form of connection there are no issues with it.

Any help or advice on this would be great .

Many Thanks

Robert 

 

OLE DB provider "MSDASQL" for linked server "console" returned message "[Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

 

Hi Robert,

Based on my research, the above error indicates that the issue is in the communication between client and server. Please review the following blog to troubleshoot this issue.

Connecting from Outside of Amazon EC2Firewall Timeout Issue
ttp://docs.aws.amazon.com/redshift/latest/mgmt/connecting-firewall-guidance.html

Also, I would like to suggest you post the question in the Amazon Redshift forum to get specific support.

Thanks,
Lydia Zhang

HI Lydia Thanks you for your response however 

I'm at a complete stand still now as I now matter what I do nothing seems to work.

1. ODBC created and tests fine using 

https://s3.amazonaws.com/redshift-downloads/drivers/AmazonRedshiftODBC64.msi

and 

https://s3.amazonaws.com/redshift-downloads/drivers/AmazonRedshiftODBC32.msi

Set to Retrieve Entire Result to Memory  with 

KEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\:

• KeepAliveTime: 30000

• KeepAliveInterval: 1000

• TcpMaxDataRetransmissions: 10

•  added and the EC 2 instance rebooted.

2. Linked server created however 

• when I do a select of more that 4 based on a simple condition it continuously runs until I stop it
• when I do a select * from openquery (redshift, 'select * from table limit 16')  it works  
• when I do a select * from openquery (redshift, 'select * from table limit 17') it doesn't work

Symptom is when you stop it the cancel goes on forever so you have to 

3. SSIS ODBC connection using the 32 driver 

• select with a limit of 2 it works 
• select with a limit of 3 it hangs and once you cancel the execution you have to go to task manager to abort visio studio  

Also tried the PLSQL 64 bit driver but the 32 bit one wont connect to the db so I cant use ODBC through SSIS.

(psqlodbc_09_03_0300-1)

There must be away to get either connect through SSIS or linked server to be able to extract information if anyone could help me I'm open to ideas.

Many Thanks

Robert  

 

If it doesn't work in Linked Server, and it doesn't work in SSIS, then it's probably a problem with the Redshift ODBC driver or with Redshift itself. 

By the way have you seen the announcement for Azure SQL Data Warehouse?

David