SQL Server Remote Procedure Time Out of 600 Seconds Exceeded
On SQL Server when I execute SQL query using Linked Server connected to Pivotal Greenplum database I got SQL error "Remote procedure time out of 600 seconds exceeded. Remote procedure '' is canceled." since the SQL query lasted for a long time before error is displayed. For solving the remote procedure time out error, SQL Server database administrators can configure system parameters as shown in this SQL tutorial.
As data professionals will know Pivotal Greenplum is a massively parallel data platform for analytics.
Data sizes on analytical data platforms are huge when compared to OLTP databases.
And analytical database queries can last for a long time.
For example, following SQL database query can be a sample.
declare @hana nvarchar(max) = 'select count(*) from storm.ekbe;'
exec (@hana) AT GREENPLUM
The SQL engine error is as follows:
Msg 7214, Level 16, State 1, Line 3
Remote procedure time out of 600 seconds exceeded. Remote procedure '' is canceled.
For solution, SQL Server administrators can configure system parameters and alter "remote query timeout" default value 600 seconds to 0 which means unlimited.
SQL Server system procedure sp_configure can be used to increase "remote query timeout" value as follows
EXEC sp_configure 'remote query timeout', 0 ;
GO
RECONFIGURE ;
GO
Output of the above sp_configure stored procedure is as follows if the change is successfull:
Configuration option 'remote query timeout (s)' changed from 600 to 0. Run the RECONFIGURE statement to install.
If the same SQL command is executed on Greenplum data warehouse using the same SQL Server Linked Server after reconfiguration for remote query timeout is done, the query will run until the result is returned from remote data platform.
Database queries can run for a long time at data warehouse platforms. If the query is triggered on SQL Server by using a Linked Server connection, query time out limitations can be avoided as shown in this SQL tutorial.