如果连接断开，从SQL Server到Oracle服务器的OpenQuery无限期挂起 [英] OpenQuery from SQL Server to Oracle server hangs indefinitely if connection is lost

问题描述

我有一个计划的作业，该作业在SQL Server 2005数据库上运行.它运行一个存储的proc，该proc从View导入数据.该视图由链接的Oracle服务器的OPENQUERY组成.

I have a scheduled job which runs on a SQL Server 2005 database. It runs a stored proc which imports data from a View. This View consists of an OPENQUERY to a linked Oracle server.

我无法控制Oracle服务器-它在地理上和虚拟上都与Sql Server安装分开.视图仅具有只读访问权限，我每30分钟从其中获取一次数据.

I have no control over the Oracle server - it is both geographically and virtually separated from the Sql Server installation. The View just has read-only access and I grab the data from it every 30 mins.

有时，到链接服务器的连接将断开，挂起或无响应.发生这种情况时，作业(在SQL Server上)也会挂起，无限期地等待连接返回.在这种情况下，作业永远不会失败也不会完成，而我首先知道的是当用户抱怨缺少数据时.

On occasion, the connection to the linked server will either drop, hang or be otherwise unresponsive. When this happens, the Job (on SQL Server) also hangs, waiting indefinitely for the connection to return. The Job never fails nor completes in this scenario and the first I know about it is when users complain about missing data.

在同一服务器上查询数据库将失败，这是预期的-只有在链接的Oracle服务器上使用OPENQUERY时，它才会失去对连接的感知".

Querying a database on the same server fails as expected if there's a connection problem - it's only when OPENQUERY is used on the linked Oracle server that it loses its 'awareness' of the connection.

是否有一种方法可以使作业意识到与链接服务器的连接已断开并因此失败?还是我必须创建第二个Job来检查第一个Job没有挂起?

Is there a way to make the job realise the connection to the Linked Server has dropped and fail accordingly? Or do I have to create a second Job to check that the first one hasn't hung?

推荐答案

对于长期运行的操作，我们也遇到了类似的问题.操作将开始(并且也完成了他的工作并完成了)，但通话从未返回.最终，我们能够追溯到防火墙关闭了与Oracle服务器的连接.

We were having similar issues for long running operations. The op would be started (and also did his work and finished), but the call never returned. Eventually we were able to trace this down to a firewall closing the connection to the Oracle server.

我们可以通过在服务器上使用参数SQLNET.EXPIRE_TIME来防止此情况，该参数将定期"ping"客户端，从而阻止防火墙关闭端口.也许这对您也有帮助.

We could prevent this by using the parameter SQLNET.EXPIRE_TIME on the server, which would periodically "ping" a client and thus keep the firewall from closing the port. Maybe this helps in your case as well.

这篇关于如果连接断开，从SQL Server到Oracle服务器的OpenQuery无限期挂起的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！