April 10, 2008 at 8:36 am
Our company uses an application that has so many problems that the support staff are running ad hoc updates against the database constantly. Standard procedure is to wrap the statement within a transaction to verify that they are only affecting the number of records required, and then highlight the statement without the transaction begin tran / rollback to execute. Occasionally when they run the statement outside the transaction they fail to highlight the entire statement and run the update without a where clause which affects from thousands to millions of records.
I am wondering if there is an editor that they can use that will parse the statement before executing to verify that a where clause exists or possibly determines the number of affected records and if that exceeds a threshold, then require additional user verification before applying the statement.
Any suggestions (other than fixing the application, I tried throwing that one out there already 🙂 ) would be greatly appreciated.
April 10, 2008 at 9:01 am
Jeff Wood (4/10/2008)
Our company uses an application that has so many problems that the support staff are running ad hoc updates against the database constantly. Standard procedure is to wrap the statement within a transaction to verify that they are only affecting the number of records required, and then highlight the statement without the transaction begin tran / rollback to execute. Occasionally when they run the statement outside the transaction they fail to highlight the entire statement and run the update without a where clause which affects from thousands to millions of records.I am wondering if there is an editor that they can use that will parse the statement before executing to verify that a where clause exists or possibly determines the number of affected records and if that exceeds a threshold, then require additional user verification before applying the statement.
Any suggestions (other than fixing the application, I tried throwing that one out there already 🙂 ) would be greatly appreciated.
You could threaten the support staff with sacking if they mess up again :hehe:
This is a tough one really. I had a similar problem at one place I worked at. What was even worse than your situation, one time, one of the support staff ran their statement, which included the begin tran but failed to highlight the commit tran statement. You can imagine the rest 😉
I took away everyone's read/write permisisons on the production database and said that every change to the live database had to go through me. I realise that this isn't necessarily practical in your situation (particularly if the application is bug-ridden and support has to work flat out to fix live issues). But you've got to draw the line somewhere.
I don't think there are any editors out there that will parse the statement in the way you suggested.
April 10, 2008 at 9:11 am
Why are they not including the transaction when executing? You may want to have them run the BEGIN TRAN and the UPDATE without the COMMIT/ROLLBACK. Once they verify the record count, they can issue the COMMIT/ROLLBACK. Obviously, there are some downsides to this as well as they could forget to run the COMMIT/ROLLBACK and leave the locks open.
They could run a SELECT COUNT(*) using the same FROM/JOIN/WHERE clauses as the UPDATE to check the row count of the result set prior to running the UPDATE, but here again you'd be relying on the user to not mess up.
April 10, 2008 at 11:43 am
I agree with SQLZ. If you want to maintain any amount of control, don't let them run it against production. Give them a daily, refreshed dev environment, have them run their "fixes" there and verify results. If the results are acceptable, YOU run it in production. If they run it and it incorrectly updates/deletes data in production, isn't that simply compounding the issue of the buggy-software??
-- You can't be late until you show up.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply