Weird SQL Server 2005 Sybase interaction [modified]
-
How's this for weird? One of my co-workers was attempting to develop an update trigger for an application that is developed using power-builder and (as far as I can tell) communicates to the database through a Sybase layer. The application is talking to a SQL Server 2005 server with a database set to SQL Server 2000 compatibility level. In the trigger, he is inserting into a table variable and running some logic based on that. The entire trigger runs just fine through all test cases in the DB; however it fails when the update is called from the application. With the error: Row changed between retrieve and update. Not having access to the application source, we couldn't debug very much through the app and Google was not our friend. Through debugging the trigger, we found the insert statement to be the culprit, but only when it insert 0 records into the table variable(we knew the logic below the trigger to be irrelevant as it was commented out). Being curious about the error message, I went ahead and printed
@@errorbefore and after the insert; then tested through Management Studio. I received no errors. Still curious, we ran the update through the app. We encountered no errors here either. Eventually we found that printing anything before the insert resolved the issue. In an attempt to figure out the problem, I even triedPRINT char(13)+'(0 row(s) affected)'. It ran with no problem through the app. Happy to be rid of the error, my colleague continued his development; but we're still wondering: "WTF?" What do you guys think? Shall we chalk it up to Sybase oddities or is there a reasonable explanation for this?modified on Friday, August 27, 2010 9:47 PM
-
How's this for weird? One of my co-workers was attempting to develop an update trigger for an application that is developed using power-builder and (as far as I can tell) communicates to the database through a Sybase layer. The application is talking to a SQL Server 2005 server with a database set to SQL Server 2000 compatibility level. In the trigger, he is inserting into a table variable and running some logic based on that. The entire trigger runs just fine through all test cases in the DB; however it fails when the update is called from the application. With the error: Row changed between retrieve and update. Not having access to the application source, we couldn't debug very much through the app and Google was not our friend. Through debugging the trigger, we found the insert statement to be the culprit, but only when it insert 0 records into the table variable(we knew the logic below the trigger to be irrelevant as it was commented out). Being curious about the error message, I went ahead and printed
@@errorbefore and after the insert; then tested through Management Studio. I received no errors. Still curious, we ran the update through the app. We encountered no errors here either. Eventually we found that printing anything before the insert resolved the issue. In an attempt to figure out the problem, I even triedPRINT char(13)+'(0 row(s) affected)'. It ran with no problem through the app. Happy to be rid of the error, my colleague continued his development; but we're still wondering: "WTF?" What do you guys think? Shall we chalk it up to Sybase oddities or is there a reasonable explanation for this?modified on Friday, August 27, 2010 9:47 PM
WTF, who was the evil bastard who architected this abomination. With so many competing and disparate layers you are going to get insane problems like this one! When it come down to the point where the solution is based on a print statement I would want nothing to do with it. run away, run away very fast
Never underestimate the power of human stupidity RAH
-
WTF, who was the evil bastard who architected this abomination. With so many competing and disparate layers you are going to get insane problems like this one! When it come down to the point where the solution is based on a print statement I would want nothing to do with it. run away, run away very fast
Never underestimate the power of human stupidity RAH