Confirm before delete/update in SQL Management Studio?

So for the second day in a row, someone has wiped out an entire table of data as opposed to the one row they were trying to delete because they didn't have the qualified where clause.

I've been all up and down the mgmt studio options, but can't find a confirm option. I know other tools for other databases have it.

From stackoverflow

• Try using a BEGIN TRANSACTION before you run your DELETE statement.

  Then you can choose to COMMIT or ROLLBACK same.

• Under Tools>Options>Query Execution>SQL Server>ANSI, you can enable the Implicit Transactions option which means that you don't need to explicitly include the Begin Transaction command.

  The obvious downside of this is that you might forget to add a Commit (or Rollback) at the end, or worse still, your colleagues will add Commit at the end of every script by default.

  You can lead the horse to water...

  You might suggest that they always take an ad-hoc backup before they do anything (depending on the size of your DB) just in case.

  Greg J : I agree with all of these suggestions and the best part of it is the 'lead a horse to water'. Without something concrete in place to stop the user, all of these will fail at some point. Thanks for the advice.

  le dorfier : This usually ends up with users who reflexively confirm without thinking.

• That is why I believe you should always:

  1 Use stored procedures that are tested on a dev database before deploying to production

  2 Select the data before deletion

  3 Screen developers using an interview and performance evaluation process :)

  4 Base performance evaluation on how many database tables they do/do not delete

  5 Treat production data as if it were poisonous and be very afraid

  CJM : I think 3 and 4 are a bit patronising, but 2 and 5 are sound pieces of advice.

  Dining Philanderer : Meant to be funny, not patronising... My apologies if that was not obvious...

• Put on your best Trogdor and Burninate until they learn to put in the WHERE clause.

  The best advice is to get the muckety-mucks that are mucking around in the database to use transactions when testing. It goes a long way towards preventing "whoops" moments. The caveat is that now you have to tell them to COMMIT or ROLLBACK because for sure they're going to lock up your DB at least once.

• In SSMS 2005, you can enable this option under Tools|Options|Query Execution|SQL Server|ANSI ... check SET IMPLICIT_TRANSACTIONS. That will require a commit to affect update/delete queries for future connections.

  For the current query, go to Query|Query Options|Execution|ANSI and check the same box.

  This page also has instructions for SSMS 2000, if that is what you're using.

  As others have pointed out, this won't address the root cause: it's almost as easy to paste a COMMIT at the end of every new query you create as it is to fire off a query in the first place.

• I'd suggest that you should always write SELECT statement with WHERE clause first and execute it to actually see what rows will your DELETE command delete. Then just execute DELETE with the same WHERE clause. The same applies for UPDATEs.

  Greg J : I agree and that is definitely a best practice, and if everyone followed that practice I wouldn't be typing right now :)

• So for the second day in a row, someone has wiped out an entire table of data as opposed to the one row they were trying to delete because they didn't have the qualified where clause

  Probably the only solution will be to replace someone with someone else ;). Otherwise they will always find their workaround

  Eventually restrict the database access for that person and provide them with the stored procedure that takes the parameter used in the where clause and grant them access to execute that stored procedure.

• First, this is what audit tables are for. If you know who deleted all the records you can either restrict their database privileges or deal with them from a performance perspective. The last person who did this at my office is currently on probation. If she does it again, she will be let go. You have responsibilites if you have access to production data and ensuring that you cause no harm is one of them. This is a performance problem as much as a technical problem. You will never find a way to prevent people from making dumb mistakes (the database has no way to know if you meant delete table a or delete table a where id = 100 and a confirm will get hit automatically by most people). You can only try to reduce them by making sure the people who run this code are responsible and by putting into place policies to help them remember what to do. Employees who have a pattern of behaving irresponsibly with your busness data (particulaly after they have been given a warning) should be fired.

  Others have suggested the kinds of things we do to prevent this from happening. I always embed a select in a delete that I'm running from a query window to make sure it will delete only the records I intend. All our code on production that changes, inserts or deletes data must be enclosed in a transaction. If it is being run manually, you don't run the rollback or commit until you see the number of records affected.

  Example of delete with embedded select

  delete a --select a.* from from table1 a join table 2 b on a.id = b.id where b.somefield = 'test'

  But even these techniques can't prevent all human error. A developer who doesn't understand the data may run the select and still not understand that it is deleting too many records. Running in a transaction may mean you have other problems when people forget to commit or rollback and lock up the system. Or people may put it in a transaction and still hit commit without thinking just as they would hit confirm on a message box if there was one. The best prevention is to have a way to quickly recover from errors like these. Recovery from an audit log table tends to be faster than from backups. Plus you have the advantage of being able to tell who made the error and exactly which records were affected (maybe you didn't delete the whole table but your where clause was wrong and you deleted a few wrong records.)

  For the most part, production data should not be changed on the fly. You should script the change and check it on dev first. Then on prod, all you have to do is run the script with no changes rather than highlighting and running little pieces one at a time. Now inthe real world this isn't always possible as sometimes you are fixing something broken only on prod that needs to be fixed now (for instance when none of your customers can log in because critical data got deleted). In a case like this, you may not have the luxury of reproducing the problem first on dev and then writing the fix. When you have these types of problems, you may need to fix directly on prod and you should have only dbas or database analysts, or configuration managers or others who are normally responsible for data on the prod do the fix not a developer. Developers in general should not have access to prod.

• Isn't there a way to give users the results they need without providing raw access to SQL? If you at least had a separate entry box for "WHERE", you could default it to "WHERE 1 = 0" or something.

  I think there must be a way to back these out of the transaction journaling, too. But probably not without rolling everything back, and then selectively reapplying whatever came after the fatal mistake.

  Another ugly option is to create a trigger to write all DELETEs (maybe over some minimum number of records) to a log table.

• Lock it down:

  REVOKE delete rights on all your tables.

  Put in an audit trigger and audit table.

  Create parametrized delete SPs and only give rights to execute on an as needed basis.

How to create an ASP.NET custom control with a dash in the name?

I want to set up an ASP.NET custom control such that it has a custom name, specifically, with a hyphen within it, so it might look like this in markup:

<rp:do-something runat="server" id="doSomething1" />

I don't mind if this syntax requires setting up a tag mapping in web.config or something to that effect, but the tagMapping element doesn't quite match up for what I'd like to do.

From stackoverflow

• I wouldn't think this is possible due to the restrictions on class namings. I don't believe you can refer to a control class in markup without refering to it by name

  Is there a specific reason you need the hyphen?

• Just use camelCase, its easier to read anyways.

• John, you're right. I did some searching in Reflector and it looks like it doesn't get there:

  Type ITagNameToTypeMapper.GetControlType(string tagName, IDictionary attribs)
  {
      string str;
      string str2 = this._nsRegisterEntry.Namespace;
      if (string.IsNullOrEmpty(str2))
      {
          str = tagName;
      }
      else
      {
          str = str2 + "." + tagName;
      }
      if (this._assembly != null)
      {
          Type type = null;
          try
          {
              type = this._assembly.GetType(str, true, true);
          }
  

  Implemented in System.Web.UI.NamespaceTagNameToTypeMapper, System.Web.

  @Jonathan: I have a specific business reason for wanting to do it this way. Oh well.

Problem with class design and inheritance in Flash AS3.

I have problems with how to design some classes. I have three classes. One superclass, and two subclasses.

One subclass (AnimatedCharacter) is made by flash, and is used to display the object on screen. The other (CharacterPhysics) is made by myself to extend the superclass.

The problem is that the object I use, is of the type AnimatedCharacter, so I can't just put it in a variable of type CharacterPhysics.

What I tried is some sort of Decorator pattern, by giving the object of type CharacterPhysics a reference to the other object. But now I have to override all the methods of the superclass and pass the methodcalls to the reference. Not an ideal situation.

Does someone know how to solve this kind of problem?

From stackoverflow

• I don't quite understand the purpose of this class structure you describe (the class names confuse me), but in general a few things come to mind that might help you:

  Almost always the best solution is to try and rethink your class model by evaluating whether you should for example break up the responsibilities of classes in an alternate way so that you could utilize inheritance and polymorphism in a better way.

  "The problem is that the object I use, is of the type AnimatedCharacter, so I can't just put it in a variable of type CharacterPhysics."

  If you want to put an AnimatedCharacter into a variable of type CharacterPhysics, the former should extend the latter, or you should have a common interface (or superclass) for both and then type the variable as such. If this is not possible, my opinion is that you should probably try to rethink and refactor your whole class structure, assuming that you have a solid "object-oriented" reason for wanting to do this in the first place ;).

  If the above is not possible, there are some other tricks you can evaluate in your context:

  • The use of mixins can work as a "poor man's multiple inheritance". Derek Wischusen has some examples on how to implement them in AS3 at flexonrails.net.
  • "Kind of" implementing the decorator pattern with flash.utils.Proxy. The problem with this approach is that you defer a lot of error checking from compile time to runtime, but the good thing is that you don't have to manually write the "proxying" implementations of all of the methods of the "decorated" object, but write just one (callProperty()) instead.

• You can interpret a sublass as an instance of a superclass but not vice sersa. Did you state this backwards?

  If so, you could use:

  vas cp:CharacterPhysics;

  ...

  var ac:AnimatedCharacter = cp As AnimatedCharacter

• Off the top of my head, it seems like those 2 should be interfaces which your main class implements

ASP.NET Refreshing one Window from another

I have a page with a GridView on it that launches a popup, using Javascript. The user then selects an item, that updates the data connected to the GridView and closes the popup.

How do I refresh the first (ie the Calling page) so that I can refresh the data shown in my Gridview?

From stackoverflow

• Try this inside your popup:

  <script>
  window.opener.location.reload()
  </script>
  

  That should refresh the page that opened the pop-up

  David Smit : This causes the following: --------------------------- Microsoft Internet Explorer --------------------------- The page cannot be refreshed without resending the information. Click Retry to send the information again, or click Cancel to return to the page that you were trying to view.

• If you simply need to trigger a postback on the calling page, this should work:

  <script>
  window.parent.document.forms[0].submit();
  </script>
  

  By explicitly submitting the form, you avoid the warning that appears if you just refresh the calling page.

  If you need to trigger an OnSelectedIndexChanged event on the GridView during the postback, then things are a bit fiddlier, but you should be able to do it by calling window.parent.document.__doPostBack() with suitable arguments.

• Here is the Solution:

  Dim CloseScript As String = "<script language='javascript'>function closeWindow(){ window.opener.document.forms[0].submit();window.close();}closeWindow();</script>"
  

  In .NET 2.0 you have to add this to the page to register above Javascript:

   'register with ClientScript 
      'The RegisterStartupScript method is also slightly different 
      'from ASP.NET 1.x 
      Dim s As Type = Me.[GetType]()
      If Not ClientScript.IsClientScriptBlockRegistered(s, "CloseScript") Then
          ClientScript.RegisterClientScriptBlock(s, "CloseScript", CloseScript)
      End If
  

• Does this avoid the 'page cannot be refreshed' message

  window.opener.location = window.opener.location;
  

  (sorry I would have just left a comment on TonyB's post but I don't have enough SO points, so I'm not allowed... :(

How to increase mysqld-nt CPU usage

I have a pretty much default installation on mysql on Windows 2003. I am rebuilding some indexes and the process only seems to use 3-20% of the CPU.

Is there a way to allow it to use more and speed up the process?

From stackoverflow

• This applies to every application/process, not only mysql. If your database is using 3-20% CPU and the final performance is still unacceptable it means that you don't lack processor power, since it is most of the time idle. What is most probable is your bottleneck is at your HDD or HDD-controller level. Have you tested the I/O bandwitch and access time of your HD?

• Can you mount a ramdisk, and move your database tables to that instead? You'll need lots of RAM, but if your DB is only a few hundred MB, then you'd be skipping the heavy disk IO. Obviously, you'd want to be working from backups in case the power went out...

• Also, along the lines of what Fernando mentioned, Try to figure out where you bottleneck is. It's probably the hard disk. Open up Perfmon, and add counters for PhysicalDisk to see if that's where your bottleneck is. From the activity you are doing, it's probably writing to the actual disk that is causing the slow down.

SQL Command Add Database Diagramming

Is there a tsql command on sqlserver 2008 which can be run in order to enable Database Diagramming instead of this dialog appearing:

This database does not have one or more of the support objects required to use database diagramming. Do you wish to create them?

From stackoverflow

• The script is a little too long to add here, but here's what you can do. 1) Create a new database. 2) Start sql server profiler 3) Click the "Database Diagrams" folder in management studio. 4) Clear the profiler. 5) Confirm the message box with a prompt to enable diagramming. 6) Profiler now contains the script that enabled diagramming. 7) Select the script in profiler and copy the output from the bottom pane.

  Kim

  Tim Howland : devious, I love it!

Does VB.NET trash Access databases?

Grasping at straws here... I work with a VB6 desktop system using several 2003-style Access databases (.MDB). Recently, I changed the first function from VB6 to VB.NET, still using an Access database. This is more than a conversion, but a rewrite with additional functionality. It is still fairly simple functionality, with a low-volume database. We have 1400 customers, small businesses with varying machine qualities. Most customers are happy with the new screen and functionality. A very few of those customers have experienced EXTREME slowness loading the datagridview. Customer Service tells us that 1) the machines have at least 1 GB of RAM, and 2) rebooting always solves the problem.

I wrote an app to severely slow down my machine, and it STILL runs better for me than it does for those few customers. Also, my Access database has never been trashed by this application.

Any suggestions?

Thanks!!

From stackoverflow

• No, VB.Net works great with Access. SHARED environments will trash access.

  Since rebooting solves the problem I would check that you are closing your connections properly.

  TcKs : I don't think so. Need commonly use a access DB for 1-10 users on LAN. If the users does not use it very much, all works fine. But this behaviour is not "by default". Some experience with tweaking is required. But all information are available on internet.

  David-W-Fenton : Competent programmers have no difficulty writing multi-user apps using Jet databases for the data store.

• Sounds like a big memory leak to me.

  Some customers will leave your application running for longer than others, and will be harder hit.

  Using Access where there are more than a few concurrent users inevitably results in pain.

  David-W-Fenton : Using Access with multiple users when you are incompetent programing applications against the Jet databgase engine sometimes results in pain. But it's the INCOMPETENCE that is the problem, not Access/Jet.

  Booji Boy : VB.NET (& VB6) don't leak memory. (You can "leak" database connections, though).

• We have similar experience, the most cases are causes by antivirus. They check the file VERY often ( some antivures every access to file ).

  David-W-Fenton : AV should be turned off for MDBs if you are using file-extension-based scanning. There is but one instance of an actual in-the-wild Access virus ever, and if all you have is an MDB back end, there won't ever be any code run from that MDB anyway (as the users aren't opening it in Access).

• Rebooting while updating an access database can trash it.

  You need some more info so that you have a better understanding of what is going on. They need to collect some information for you on a workstation that is having the problem. Using task manager you can have them get the following info:

  • CPU utilization
  • What task is consuming the most cpu
  • Peak (committed) memory on XP - no equiv on Vista
  • Total (committed) memory on XP - no equiv on Vista
  • Available (physical) memory on XP - Free on Vista (made worthless by Superfetch)

  It's also possible to use the command line tool "SYSTEMINFO" on both XP and Vista to get Total and Available memory. If you have very little available and on XP if your Total committed is larger than your Total Physical then you are most likely swapping and lack of memory (or a memory leak) is causing your slow down.

  Bottom line is you need more information. It may be another app on the workstation is causing the problem. We had a situation where Notes 5.0 had a problem where if most of the window is covered up by another window and you received a new mail message the cpu utilization on Notes went to 100%. This caused apps to run slow and unless you are on the workstation looking at task monitor you would never guess it was Notes causing the problem. The problem was always called in on a different program (the one in the foreground). Access can also use 100% cpu in different modes even though it doesn't seem like it's doing anything.

  Gather as much info as you can. You might want to write a vbscript or program that will will gather some info for you so that whomever is having the problem can run it to gather the info before rebooting.

  A batch file that does the following will give you quite a bit of info:

  
  @echo off
  SystemInfo  >c:\systeminfo.log
  tasklist /v >>c:\systeminfo.log
  

  CindyH : nice collection of hints - thanks! I'll see what kind of info they can already collect and start with your suggestions for more.