June 19, 2008 at 7:19 pm
We have a Progress database that uses triggers to send information to another Progress Database which in turn processes the information and converts the information to SQL DML statements (that is its sole purpose). The second Progress DB then issues an execute immediate command and sends the command to SQL Server 2005 and the database is updated (we can now use better reporting tools in a relatively real time environment.)
This all works fine 90% of the time but for some reason some statements are not executing but there is no error. For example we can post ledger transactions to a table in Progress and then 30 seconds later run a report in SQL Server and most of the accounts are in balance but sometimes (not always) there is a few missing (i.e if we update 8 accounts 6 may work and 2 will not).
While we were posting I ran a trace (SQL Profiler). When I looked through the trace file I found all update and insert statements were there even the ones that had they ran would of put the account in balance. For the accounts out of balance I copy and pasted the SQL statement from the trace file in a query window and executed it and everything worked and the account was back in balance.
I'm trying to figure out why the SQL statement did not actually execute or commit. There are no errors, I can see lock acquired and lock released, the sequence of events in the log look the same for ones that did not work as ones that did work. I'm at a loss as to what is going on since I cannot seem to get an error back. I believe the problem is on the SQL side since all the DML statements are showing up in the trace file that would put the accounts in balance but when I look at the record in the table (SQL Server) the update has not taken place in some cases. So far it is just updates that are failing. Any help would be appreciated??
June 19, 2008 at 7:40 pm
Perhaps they just rolled back. Are you tracing Commits and Rollbacks also?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 20, 2008 at 9:25 am
I'm seeing a pattern in the trace file when an update statement committs and when it does not.
I attached a WORD document with a sample from the trace file. How can I determine why the lock sequqnce is different. Would a different transaction isolation level be something to look at?
Thanks for the help.
June 20, 2008 at 9:39 am
I thnik I might have found my problem. I will post more after I do some more testing.
June 20, 2008 at 9:41 am
Could it be IMPLICIT TRANSACTIONS set off for some connection?
June 26, 2008 at 9:28 am
I found a bug in the Progress to SQL Server translation. Thanks for your reponses.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply