Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-02-11 : 08:01:34
|
| John writes "I am using MS Access as a front end to SQL Server.Version 7.0 with service pack 4.OS is NT4 with service pack 6a.There are a significant number of scenarios where a record is inserted into a master table, followed by the insertion of multiple records into a child table. Referential integrity is set, with the Primary Key from the master table being a Foreign Key in the child table. One example of this in with Purchase Orders. In this case, the master table holds header information, for example SupplierID, Date, etc. The child table holds the detail lines, e.g. ProductID, quantity being ordered, etc.The master table has a primary key of type int.The child table has a two field primary key. Field-1 is a foreign key referring to the master table. Field-2 is a line number, type smallint.It is important that either all insertions are successful, or none of the insertions take place. Consequently, the entire update is done within a transaction.Occassionally (about one time in 30) the update fails on the first insertion into the child table with a "Query Timeout" error. If the update is removed from within a transaction, then it works perfectly every time. However, this is not really acceptable, since if a failure did occur, then an indeterminate number of records will have been written.Usually, if an error occurs, the user can immediately attempt the update again and it will then succeed. However, due to the overall number of places that this scenario is used, and the volume of transactions, the number of errors is not acceptable.Please help. Many thanks." |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-02-11 : 13:34:22
|
| Sounds like you have blocking.As it is happenning very frequently shouldn't be difficult to find the problem.Add this sp to masterhttp://www.mindsdoor.net/SQLAdmin/sp_nrInfo.htmlRun it while the system is running and it will show any blocking and the command causing it.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
|
|
Updating tables with referential integrity inside a transaction