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-05-10 : 07:56:11
|
| Ron writes "we have a vb6 app frontend and ms sql for our db. we do not have access to the source code of the application. in short, the programmer many moons back added a trace module to track down hard to reproduce bugs. this ultimately caused a runtime error 6, violation of referential integrity in table x. we were advised to load the program w/ /notrace to avoid the runtime, which it in fact does. however, now the same process can take up to 10 minues. a save for example, to a particular table, can literally spike the cpu for 10 solid minutes and then complete. i would add, oddly, that the spiking of the cpu occurs on all machines and platforms (save for one machine, although i have not been able to figure out what is unique about that machine). regardless, all other machines using win2k, winxp with the latest service packs experience this issue. is there any way w/o the source code to track down what the referential integrity error actually is, fix that, so we don't need to run the system w/ the no trace. any help/direction/assistance would truly be appreciated." |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2005-05-10 : 09:06:26
|
| You might be able to add a trigger to the table that logs the changes into another table to determine the error, and then perhaps the trigger can fix the integrity error itself when the situation arises.The good news is you learned first-hand why you should always use stored procedures for all database interfacing in your applications! - Jeff |
 |
|
|
Beachsandintoes
Starting Member
15 Posts |
Posted - 2005-05-10 : 10:11:26
|
| Hi,If you know the name of the table then you may want to try querying SQL Server for all of the SQL statements that have ran on that table.You said, "can literally spike the cpu for 10 solid minutes and then complete". If this is a fairly new issue then have you tried rebuilding the related indexes? Are the statistics up to date for the table(s) involved? Have any indexes been dropped or have new indexes been created?By the way, jsmith8858 is right. Store procedures are the way to go. A good rule of thumb is keep your application logic in the application layer and the storage logic in the storage layer.I hope this helps. I will watch for your reply.- Beach |
|
|
|
|
|
|
|
|
Notrace, referential integrity issues, VB 6 app