June 19, 2008 at 10:14 am
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 11:30 am
Very strange. Is it always the same tables that are affected? Could there be an owner issue?
Is the data just gone, meaning it looks like the commands were never executed.
June 19, 2008 at 12:12 pm
Firstly thanks for taking the time to read and respond to my issue. I believe the problem may be widespread but we are testing our new 3rd party replication tool (Progress -> SQL Server) and the ledger table happens to be the first table that has the problem. I'm focused on this one table because if I can fix the problem here then I might be able to apply the fix across all tables.
Any ideas how to debug when I get no errors back? What tools could I use to provide more information? How can I see what SQL Server id doing with the updates that are present but never committing?
Thanks for the help.
June 19, 2008 at 12:53 pm
The record is there but the update never happened. The record is in the state prior to the update running. Would it help to send a sample of the trace file?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply