SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

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.

SQL Server: Remote procedure time out of 600 seconds exceeded

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 ;

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.

change remote query timeout on SQL Server

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.

SQL Server Linked Server query on Greenplum database

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.

SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019

Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.