One of our clients has an application (FoxPro 9) running on top of a SQL Server 2005 backend. Intermittently, they are losing their ODBC connection with the SQL Server database. Below is the initial error information:
我们的一个客户端有一个在SQL Server 2005后端运行的应用程序(FoxPro 9)。他们间歇性地失去了与SQL Server数据库的ODBC连接。以下是初始错误信息:
Err Msg: Connectivity error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
Err Msg:连接错误:[Microsoft] [ODBC SQL Server驱动程序] [DBNETLIB] ConnectionRead(recv())。
ODBC Err Msg: [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()).
ODBC Err Msg:[Microsoft] [ODBC SQL Server驱动程序] [DBNETLIB] ConnectionRead(recv())。
SQL State: 01000
SQL状态:01000
ODBC Err No: 10054
ODBC错误号:10054
ODBC Handle: 1
ODBC句柄:1
FoxPro Error No: 1526
FoxPro错误号:1526
We cannot duplicate this error on command. We have tried any number of solutions to no avail. One such hardware base solution which we found was described in: http://support.microsoft.com/kb/942861/en-us
我们无法在命令上复制此错误。我们尝试过任何数量的解决方案都无济于事。我们在其中找到了一个这样的硬件基础解决方案:http://support.microsoft.com/kb/942861/en-us
I mention this because it almost perfectly matches what we have been seeing. However, we have implemented all the workarounds listed in that posting (and in this one http://support.microsoft.com/kb/948496 ) - and the problem still continues.
我之所以提到这一点,是因为它几乎完全符合我们所看到的。但是,我们已经实现了该帖子中列出的所有变通办法(在这一个http://support.microsoft.com/kb/948496中) - 问题仍然存在。
This issue seems to show itself after the execution of long running queries, but we are not receiving any timeout errors, either from the application, or from SQL Server. I do not believe that this is the result of an idle timeout, because it sometimes occurs in the middle of an executing program.
在执行长时间运行的查询后,此问题似乎显示出来,但我们没有从应用程序或SQL Server收到任何超时错误。我不认为这是空闲超时的结果,因为它有时发生在执行程序的中间。
I am not a hardware guy, but both the network, and the server (Windows Server 2003), appear to be fast and well designed. There are times however, when the database server is under significant stress.
我不是硬件人,但网络和服务器(Windows Server 2003)似乎都很快且设计得很好。但是,有时候数据库服务器承受着巨大的压力。
If anyone has any suggestions on things we could try...please let us know!
如果有人对我们可以尝试的事情有任何建议......请告诉我们!
4 个解决方案
#1
Just a shot in the dark, but have you tried running a trace and trying to capture error events as well as any tsql. This might provide some clues or help you to see a pattern.
只是在黑暗中拍摄,但你尝试运行跟踪并尝试捕获错误事件以及任何tsql。这可能会提供一些线索或帮助您查看模式。
#2
Using pb application and ms sql as db, and 2 transaction objects set from the beginning. One function loops through a cursor using first transaction and within this loop, the second transaction object is used to update another table. If an explicit commit is not used after update(the second transaction obj) then the first connection is shut down and I get [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()) error. Once I call the appropriate commit statement for the corresponding transaction everything worked like a charm. And YES, the error is always somewhere before the location you actually get the crash-assuming you are in debug mode. It is interesting that Sybase managed to close/open the appropriate transaction without any need to explicitly issue a commit on the second transaction object!!!!
使用pb应用程序和ms sql作为db,并从头开始设置2个事务对象。一个函数使用第一个事务循环遍历游标,在此循环中,第二个事务对象用于更新另一个表。如果更新后没有使用显式提交(第二个事务obj),则第一个连接关闭,我得到[Microsoft] [ODBC SQL Server驱动程序] [DBNETLIB] ConnectionRead(recv())错误。一旦我为相应的事务调用适当的commit语句,一切都像魅力一样。并且是,假设您处于调试模式,错误总是在实际发生崩溃的位置之前的某个位置。有趣的是,Sybase设法关闭/打开相应的事务,而无需在第二个事务对象上显式发出提交!
#3
Just a follow-up on this question...with a partial solution.
只是关于这个问题的后续行动......部分解决方案。
I did run a trace, in fact a number of them. What I found, is that there appears to be multiple causes for these errors. I was able find and fix one of them, but we still get this error in other places, and they do not show up on the traces that I have done.
我确实做了一些追踪,实际上是其中的一些。我发现,这些错误似乎有多种原因。我能够找到并修复其中一个,但我们仍然在其他地方遇到此错误,并且它们没有显示我已经完成的跟踪。
So what was the deal with the one I did find? Well, from the trace I found that these ODBC errors appeared after another SQL Server error:
那么我找到的那个交易是什么?好吧,从跟踪中我发现这些ODBC错误出现在另一个SQL Server错误之后:
Error: 1203, Severity: 20, State: 1.
Process ID 94 attempted to unlock a resource it does not own: OBJECT: 25:1699834390:0 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
From the FoxPro code I found that an insert statement was causing this error... not always... but sometimes. In this insert, they were pulling all the fields from one table, and some of the fields from another table - into a third table. Every table in this database has an identity column called id_col, and the select statement that was populating the third table was returning two id_col fields.
从FoxPro代码我发现插入语句导致此错误...并不总是...但有时。在这个插入中,他们将一个表中的所有字段和另一个表中的一些字段 - 拉到第三个表中。此数据库中的每个表都有一个名为id_col的标识列,填充第三个表的select语句返回两个id_col字段。
insert into tablethree
select a.*, b.price, b.item, id_col
from tableone a, tabletwo b
where a.item = ....
When we restructured the code so that only one id_col was being returned...the errors stopped.
当我们重新构建代码以便只返回一个id_col时......错误停止了。
To be honest there is one other possible contributor to this error that I fixed at the same time. There was another large/long query right before this one that was using Foxpro Rushmore syntax (e.g. a.item+a.customer = lc_item+lc_customer) in a sql server query. We have had issues with this type of thing before, so it could be a contributor to the problem... but the evidence is highly in favor of the extra identity column being the cause.
说实话,我同时修复了这个错误的另一个可能的贡献者。在此之前还有另一个大/长查询在sql server查询中使用Foxpro Rushmore语法(例如a.item + a.customer = lc_item + lc_customer)。我们之前遇到过这类问题,所以它可能是问题的一个原因......但是证据非常有利于额外的身份栏是原因。
#4
Not sure if you have come across a complete solution, but have you looked into whether the network connection ever gets interrupted? One of the VFP programs I was developing started losing its SQL connection very frequently for users that were using laptops. It seemed that the laptops were temporary losing the network connection. Even if the connection is only gone for a couple seconds, the handle in VFP needs to be reset. Not sure if this is your exact issue, but sounded similar to me.
不确定您是否遇到过完整的解决方案,但是您是否考虑过网络连接是否中断?我正在开发的其中一个VFP程序开始经常丢失用于使用笔记本电脑的用户的SQL连接。似乎笔记本电脑暂时失去了网络连接。即使连接仅消失了几秒钟,也需要重置VFP中的句柄。不确定这是否是你的确切问题,但听起来与我相似。
#1
Just a shot in the dark, but have you tried running a trace and trying to capture error events as well as any tsql. This might provide some clues or help you to see a pattern.
只是在黑暗中拍摄,但你尝试运行跟踪并尝试捕获错误事件以及任何tsql。这可能会提供一些线索或帮助您查看模式。
#2
Using pb application and ms sql as db, and 2 transaction objects set from the beginning. One function loops through a cursor using first transaction and within this loop, the second transaction object is used to update another table. If an explicit commit is not used after update(the second transaction obj) then the first connection is shut down and I get [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()) error. Once I call the appropriate commit statement for the corresponding transaction everything worked like a charm. And YES, the error is always somewhere before the location you actually get the crash-assuming you are in debug mode. It is interesting that Sybase managed to close/open the appropriate transaction without any need to explicitly issue a commit on the second transaction object!!!!
使用pb应用程序和ms sql作为db,并从头开始设置2个事务对象。一个函数使用第一个事务循环遍历游标,在此循环中,第二个事务对象用于更新另一个表。如果更新后没有使用显式提交(第二个事务obj),则第一个连接关闭,我得到[Microsoft] [ODBC SQL Server驱动程序] [DBNETLIB] ConnectionRead(recv())错误。一旦我为相应的事务调用适当的commit语句,一切都像魅力一样。并且是,假设您处于调试模式,错误总是在实际发生崩溃的位置之前的某个位置。有趣的是,Sybase设法关闭/打开相应的事务,而无需在第二个事务对象上显式发出提交!
#3
Just a follow-up on this question...with a partial solution.
只是关于这个问题的后续行动......部分解决方案。
I did run a trace, in fact a number of them. What I found, is that there appears to be multiple causes for these errors. I was able find and fix one of them, but we still get this error in other places, and they do not show up on the traces that I have done.
我确实做了一些追踪,实际上是其中的一些。我发现,这些错误似乎有多种原因。我能够找到并修复其中一个,但我们仍然在其他地方遇到此错误,并且它们没有显示我已经完成的跟踪。
So what was the deal with the one I did find? Well, from the trace I found that these ODBC errors appeared after another SQL Server error:
那么我找到的那个交易是什么?好吧,从跟踪中我发现这些ODBC错误出现在另一个SQL Server错误之后:
Error: 1203, Severity: 20, State: 1.
Process ID 94 attempted to unlock a resource it does not own: OBJECT: 25:1699834390:0 . Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
From the FoxPro code I found that an insert statement was causing this error... not always... but sometimes. In this insert, they were pulling all the fields from one table, and some of the fields from another table - into a third table. Every table in this database has an identity column called id_col, and the select statement that was populating the third table was returning two id_col fields.
从FoxPro代码我发现插入语句导致此错误...并不总是...但有时。在这个插入中,他们将一个表中的所有字段和另一个表中的一些字段 - 拉到第三个表中。此数据库中的每个表都有一个名为id_col的标识列,填充第三个表的select语句返回两个id_col字段。
insert into tablethree
select a.*, b.price, b.item, id_col
from tableone a, tabletwo b
where a.item = ....
When we restructured the code so that only one id_col was being returned...the errors stopped.
当我们重新构建代码以便只返回一个id_col时......错误停止了。
To be honest there is one other possible contributor to this error that I fixed at the same time. There was another large/long query right before this one that was using Foxpro Rushmore syntax (e.g. a.item+a.customer = lc_item+lc_customer) in a sql server query. We have had issues with this type of thing before, so it could be a contributor to the problem... but the evidence is highly in favor of the extra identity column being the cause.
说实话,我同时修复了这个错误的另一个可能的贡献者。在此之前还有另一个大/长查询在sql server查询中使用Foxpro Rushmore语法(例如a.item + a.customer = lc_item + lc_customer)。我们之前遇到过这类问题,所以它可能是问题的一个原因......但是证据非常有利于额外的身份栏是原因。
#4
Not sure if you have come across a complete solution, but have you looked into whether the network connection ever gets interrupted? One of the VFP programs I was developing started losing its SQL connection very frequently for users that were using laptops. It seemed that the laptops were temporary losing the network connection. Even if the connection is only gone for a couple seconds, the handle in VFP needs to be reset. Not sure if this is your exact issue, but sounded similar to me.
不确定您是否遇到过完整的解决方案,但是您是否考虑过网络连接是否中断?我正在开发的其中一个VFP程序开始经常丢失用于使用笔记本电脑的用户的SQL连接。似乎笔记本电脑暂时失去了网络连接。即使连接仅消失了几秒钟,也需要重置VFP中的句柄。不确定这是否是你的确切问题,但听起来与我相似。