Code Focused

My Biggest VB Programming Mistake

When "Oops!" becomes "OMG!" On VB columnist Joe Kunk shows how a simple oversight when using SQL Server 2000 Data Transformation Services (DTS) nearly ended in disaster.

• By Joe Kunk
• 02/14/2011

Like all developers, I make mistakes, lots of them. Fortunately they are usually minor mistakes that are easily detected and corrected. I have made all the common mistakes like forgetting the NOT operator, using an OR instead of an AND, plus slightly more serious mistakes like neglecting to handle multi-user concurrency checking, performing deletes when I meant to do updates, and spending days to develop a feature only to find the customer really didn't want it after all, and so on.

One of my biggest programming mistakes was listed as Trap #1 in the On VB article "5 Traps to Avoid in Visual Basic." In short, I had made the mistake of declaring the user identity as a Visual Basic module-level variable in an ASP.NET application, setting the Web application identity for all of the Web application users to be the same as the last authenticated user. This had the undesirable result of expectantly displaying other users' information. I neglected to do multi-user testing and it went into production. Fixing it was easy; I stored the user identity in Session. See the article for more traps to avoid in Visual Basic development.

Another of my biggest programming mistakes was actually not a coding mistake at all but a failure to fully understand one of the tools I was using. In this case, the tool was SQL Server 2000 Data Transformation Services (DTS).

I was working to provide executives with the ability to view production Human Resources data ad-hoc via Microsoft Query. To make the data more understandable and lessen the chance of a misinterpreted query, I created several flat views of the data for primary dimensions like employee, location, fiscal period, etc. Following procedure, I created the views on the test server and scripted them onto the production server. The executives liked the ability to dynamically query these views and then asked for several more.

I created the new views on the test server and when finished, I needed to implement the new views and a couple updates to the old views onto the production server. Rather than script the views, I thought it would be faster to use the DTS Import/Export wizard (Figure 1) to copy the views from the test server to the production server.

[Click on image for larger view.]

Figure 1. Opening screen of the SQL Server 2000 DTS Import/Export Wizard.

Working my way through the wizard screens, I came to the "Select Objects to Copy" screen shown in Figure 2. My selections on that page completed exactly as shown. Notice the highlighted option "Include all dependent objects" that is checked by default under the Create Destination Objects checked option. Copy all objects was not checked and the desired views had been individually selected with the "Select Objects... " button.

[Click on image for larger view.]

Figure 2. The SQL 2000 DTS Import/Export Wizard Select Objects screen.

See the problem? By leaving the "Include all dependent objects" checked, when it deleted the existing destination views, it deleted the dependent objects of the Views which were the tables themselves. My attempt to copy a couple views into our most critical database actually resulted in dropping and recreating the source tables from the database while they were in use in production. I was caught totally off guard; less than 5 minutes later we received several calls from HR that they were no longer able to see employee data in their main application. Truly, "Oops" becomes "Oh My God!" at that point.

How did I fix it? Fortunately due to the criticality of the database, we had nightly full backups and transactional backups running every 2 hours. This error occurred only 20 minutes after the last transactional backup. We restored the last full backup and applied the transactional backups to get within 20 minutes of the error. The users were able to identity and re-apply the updates for those last 20 minutes. Subsequently I refused to use the DTS Import/Export Wizard ever again and I always generated scripts to create or update test objects on the production server after careful review.

Lessons Learned
Most developer mistakes are minor; a few are not. The lesson I learned from these incidents is to stay alert and remain fully aware of what I am doing, even during mundane tasks. What programming mistakes have you made and how did you address them?