August 21, 2003 at 10:55 pm
My friend is developing an app and it's in that application.
In voucher entry option, let's say user has entered about 100 records
The same are accepted in a temporary table.
At the end, when user presses OK and if the total debit / credit tallies, the same is updated to my SQL server 7 database in a table call TRXN table.
Before update, I issue a BEGIN TRANS commnad and at the end after all 100 records are updated I issue COMMIT TRANS command.
While this voucher is being updated, If the user takes any report (Using CRYSTAL REPORT VER 8.5) it print transaction which are updated IN THE trxn table at that point in time. So the whole system goes for a toss, as the complete voucher is not yet FULLY updated in the TRXN table.
To SUm up, even though I have used BEGIN TRANS and COMMIT TRANS , the transaction are available to another user before COMMIT TRANS is issued. (If I END in between, all the transactions are rolled back as such, which is as expected).
One more, If I issue any SQL query (usign ISQL) while the transactions are being updated, the ISQL throws time out error. I do this by HALTING the execution of VB program in debug mode.
In crystal report, We connect to the database using ODBC connection string.
In the DSN which I have created, I select driver as SQL SERVER. And than I select the server name which I want to connect to. I use SQL Server authentication. Than I change the default database to my application database.
Can anyone please help me how to detect the problem?
Regards.
August 21, 2003 at 11:06 pm
It seems that other users can access your records from the transaction table when they are in the process of being saved. This can only happen if the transaction isolation level is read uncommited (correct me if I am wrong). If you are using vb 6.0 and sql 7.0 the default transaction isolation level has to be read committed. please check the transaction isolation level.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
August 22, 2003 at 3:55 am
Thanks for the reply.
I am using SQL statements to insert/update data and have not used anything like setting isolation level. Now I am going to trace each and every line of code hope it will be solved.
Regards.
August 22, 2003 at 4:02 am
For Curiosity. Are you using begin trans in your stored procedure or starting the transaction from the front end, if it is the stored procedure itself then you need to use SET XACT_ABORT in the store procedure.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply