SQL Server Performance Forum – Threads Archive

SMS (Save My Soul)….. HELPPPPP…….

Hi, Am working on VB6-SQL Server 7 base on a Database Application for a Broker firm.
From last few weeks am facing a mysterious problem, i request you folks to please
help [V] me out as am not getting any solutions for the same. In this system, ‘n’ Users can punch in Transactions from different terminals & the
server is centrally located. All transactions are first stored in a table called
TxnH_Temp & TxnD_Temp, the Header & Details section tables. After certain point which
is decided by the User itself they start a process called Posting, which means that all
these transactions which are residing in the 2 tables said above are transfered to 2 final
tables called TransactionHeader & TransactionDetails. My problem is while in this Posting process some of the records are entered twice,thrice even more.
I checked my code if there is something wrong, but didn’t got any clue. And more interestingly this
malfunction doesn’t occur frequently; everything works perfecrtly without any duplications
& then suddenly one or two duplicate records are thrown, in addition it doesn’t happen everyday or
to every record. Out of all occurences, after close analysis i found that majority of the entries
happened on Saturdays & Wednesdays, Why dont know ? I request you people to please help me out of these problem as soon as possible. Shaggy

Do You Create This Temp Tables User Wise? If Not,May be this problem is occuring bcoz of that, I would suggest you to use #TxnH_Temp & #TxnD_Temp Instead of that ot create tmp tables user wise and use that.
I Guess it would be happening when no. of transaction is high for that day. & it would be occuring when two users are saving the data at the same time. How do u save your records in Table ( Thru SP Or Query ?) Thanks
Sandy

Hi Sundeip, Though the name suggest that the 2 tables are temporary but they are not created at runtime & neither i’ve used # to create. these 2 tables are simply replica of the 2 final tables named TransactionHeader & TransactionDetails. actually Sundeip, the process Posting is a multi-step process & once it is done u cannot change any data field of a record in the tables TransactionHeader & TransactionDetails. ‘coz of this i allowed user to keep the data in another table till he wishes, here i’ve allowed user to modify data fields & once he is satisfied that records are all ok then he starts the Posting process. Secondly, i’ve used Queries through-out the project & in some places did opened recordsets & move in loop to fetch each record. Thanx, Shaggy

So, At a Time All the users access the same tmp tables ?
and Why Dont u Use Procedure for saving purpose ?

Does the underlying tables has primary keys & constraints to not allow duplicates? (moved from Analyis forum) Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Not necessarily all users access the same tmp table, they may be in some different part of the system accessing some other tables, but yes when they are entering Transactions they all enter into same table. Thanx Satya, i did thought for setting Primary Keys though i’ve not yet applied so, ‘reason’ i wont be able to xplain it u…. anywayz setting Primary Keys is a perfect solution to deal with duplicates… but what i want to know is why & how its happening…. & are there any other alternatives to deal with duplicates other than PK. Shaggy

Uniqueness is the benefit of not making a copy of the data and only deleting the records that are duplicates. It’s main drawback is that we have to alter the original table and add a sequential record number field to uniquely identify each record. http://www.sql-server-performance.com/rd_delete_duplicates.asp for reference.
You can use PROFILER to monitor when you’re sure its occuring on a specific day/time. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

I Guess This problem would be occuring when two users are saving the data at the same time.
U Try to check It

Thanx Satya! Shaggy

In any case obtain the Pkey & constraint to avoid future occurences. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

]]>