Problem with SQL Server 2005 Express

Hello,

I am programmer from Bulgaria and I am new in working with SQL server. I have a problem and want to discuss.

Imagine that there is software system working on Windows XP professional, SQL Server 2005 express database, programs (source code on Microsoft C/C++) that connect to SQL Server 2005 express via ODBC and around 30 users that working 24 hours a day.

For simplicity let imagine that in database have 2 main tables. In first table users add record. The program get value of certain field from that record and add that value to certain field in existing record in second table. (For example => imagine that in first table insert record for sell of some good /document of sell/ and in the second table contain all amount of sells of that good.)

Everything goes fine and working very well, but sometimes (3 or 4 times a day) the data of second table don’t add the value. The users get angry and say that they have not security in the data. And I don’t know what to do, because the program is the same, only database is changed before a month to SQL Server 2005 express (before there was Access database).

Every update/change in database stays with transactions.

Please help me to resolve that problem. Have someone the same problem? Or may be I must make some settings in database, or in transaction manager (MSDTC), or etc.?

Greetings to everybody!

hi,

you do not say how you propagate the update to the 2nd table...

is there a trigger on the very fist table or do you just execute 2 UPDATE STATEMENTS?

check your code to verify the 2 updates stay in the very same transaction as well..

regards

|||

Hi Andrea,

The software system that I am talking about has strong rules to modify database and nobody else outside that software system don’t modify database.

And because the system is complex I give an example only with 2 tables. In fact there are several tables in which the system add different certain fields. Every updates/modify of the tables stay in the body of transaction.

transaction per 1 record:

1. begin transaction

2. many changes (insert, delete and update records) on related tables

3. commit transaction

When I put the system on the test with test data everything is ok. When the users start to input data and get the results (reports and etc.) in real work everything is ok.

The problem is that sometimes transactions don’t work. (If for some document transaction not working and after that the same document are input again with the same conditions into the system => everything is ok for second case, but for first case have document and amount is not added).

For example =>

- the specialist for deliver of goods inputs document for deliver of goods and show me printed stores document that system print, but that quantity don’t go to store and trader can’t sell

- or trader make selling of goods and show me printed invoice that the system print, but the quantity in the store don’t decrease and he don’t know what quantity of goods are in the sore.

- or in account department account clerk make payment to some invoice and show me input document into the system for payment, but the invoice stays unpaid.

- into the manufacture are the same cases – in the system have document for production, but sometimes quantity of the production not go to the store and traders cant sell.

And etc.

If there was single case I will think that it is error of specialist that input data into the system. But it is system that works on heavy conditions – many documents are input ant people with different occupation working together into one software system and make management decisions. Therefore I think inaccuracy like this is unallowable.

May be triggers will solve the problem, but some parts of the software system must be develop again. (Still more that this system worked very well with Access database)

I need more ideas.

Thank you and regards.

|||

hi,

I do think you probably have to check your application code, as it seems transactions got broken and not rolled back for the entire enlisted actions...

but it's very difficult in this scenario to point a finger somewhere..

regards...

|||

Hi Andrea,

I check ones again all source code that include input execute actions into transactions. There I don’t find any problem. But I see that the transactions are in the body of thread. This is because to avoid decrease of speed when many users working at the same time (in my servers program).

So, I remove threads and let the transactions run on the main program. Now I am waiting for results of the real work.

/This will decrease speed of my servers program because until the program run execute actions from one user without threads (in main servers program) the other users will wait/

May be the problem is when the transactions run in the body of threads.

I hope that someone from Microsoft corp. will check the situation – many transactions in the body of many threads – is there some problems?

Regards!

sql