Search code examples
sqlsql-serverdatabasedatabase-administrationunixodbc

How to prevent port timeouts for large remote queries from SQL Server?

I have an unixODBC connection from a linux-based SAP HANA database trying to pull massive amounts of data from another SQL Server 2012 database. Every now and then though I get "Login timeout expired" errors and I also notice via netstat that while the connection reaches "ESTABLISHED" status, it just closes out after about 10 seconds. The issue doesn't happen every time, and I've already done all kinds of due diligence on the network side and found no issues on that end.

Is there anything that can be done on the SQL Server configuration so that the connection doesn't time out?

The current remote query timeout is 600 seconds and remote login time-out is 30 seconds.

I also read in the following link that increasing "Connection timeout" setting in SQL Server helps but how do I change it, it's grayed out.

enter image description here

Solution

  • You are right to note that you need to change the remote query timeout setting to 0.

    This link give pretty neat answer to your question. There are ways for doing it via both the SQL Server management Studio, and the command line. However, you need to first login with a user that has the required permissions.