John Paul Cook : Tips and Tricks

SSMS Tips and Tricks: Scripting Inserts

John Paul Cook — Wed, 14 Oct 2009 02:03:25 GMT

Sometimes you need to be able to script the loading of a table. SSIS and the Import/Export Wizard can do this, but there are times when you really need a complete set of insert statements. Red Gate’s SQL Data Compare or SQL Packager can do this, but not...(read more)

Disaster Recovery for Your Windows Mobile Phone

John Paul Cook — Mon, 13 Apr 2009 02:18:00 GMT

Right after doing a hard reset of my Windows Mobile phone, I realized that my phone’s contacts hadn’t been backed up. Now that’s a disaster! I had Kalen Delaney’s and Paul Nielsen’s cell phone numbers on my cell phone – not something to lose. From the MVP newsgroup for Windows Mobile devices, I found out about the beta version of Microsoft MyPhone. It’s a limited beta and it took a few days before my application was accepted. After installing MyPhone on my phone, I used MyPhone’s sync feature to backup my data to the cloud. I can now resync my Windows Mobile phone from the web. Even if I lose or damage my phone, I could sync a new phone from the MyPhone website and be back in business quickly.

Speaking of damaging a phone, have you ever wondered what to do with a phone that gets wet? Assuming you drop your phone into relatively clean fresh water, one of my neighbors has a repair technique that has served her very well more than once. She boils the water out. Think back to your physics class. Boiling doesn’t have to mean hot. Lower the atmospheric pressure and the boiling point temperature is depressed. My neighbor rushes her wet electronic devices over to her father’s house. He puts the phone or other device in a bell jar and uses a vacuum pump to reduce the pressure until water is boiling at room temperature. After a short period of time, all of the water in the phone is boiled away.

Finding Tables Without Referential Integrity

John Paul Cook — Wed, 08 Apr 2009 17:38:00 GMT

Following up on yesterday's post from Louis, sometimes you encounter databases with missing referential integrity constraints. Even when the database is intended to have RI, accidents happen. Maybe a script dropped RI constraints before a bulk load and didn't quite get all of the constraints recreated. Whatever the reason, it's good to have a script to identify tables that are not referenced and do not reference other tables. Here's a script that works on SQL Server 2005 and 2008 (updated to no longer use sysreferences as per Adam Machanic's suggestion):

SELECT sys.objects.name
FROM sys.objects
LEFT JOIN sys.foreign_keys referenced
ON sys.objects.object_id = referenced.referenced_object_id
LEFT JOIN sys.foreign_keys parent
ON sys.objects.object_id = parent.parent_object_id
WHERE sys.objects.type = 'U'
AND referenced.referenced_object_id IS NULL
AND parent.parent_object_id IS NULL

Louis did a good job explaining the importance of using the database engine to protect the integrity of the data. As a consultant, I've seen lots of databases with varying degrees of referential integrity. It never works out well in the long run when referential integrity isn't enforced in the database. Application code - even if it is christened object relational mapping - is no substitute for imposing referential integrity checks in the database. A relational database is more than a data storage repository - and it should be.

Keep Your Server Clean by Using Virtual Applications

John Paul Cook — Thu, 31 Jan 2008 03:37:00 GMT

It's pretty well understood that it's highly desirable to keep servers lean and mean. But what do you do when you need to use a tool, particularly if it is needed only once? You could install the utility application and then remove it, but that does introduce risk. Virtual applications provide the answer. You can actually run an application without installing it.

There are two basic classes of virtual applications: agentless and those that require an agent. Microsoft Application Virtualization (formerly SoftGrid) and Altiris Software Virtualization Solution are well known examples of application virtualization that use an agent. In order for the virtualized applications to work, an agent must be installed on the machine. The agent provides a boundary, a sandbox, in which the application runs without interfering with what's installed on the machine. Agentless virtualized application solutions create a single, self-contained executable that runs without being installed and without elevated permissions. The application is isolated from the host it is running on and doesn't interfere. Examples of agentless virtualized application products are Xenocode and VMware's Thinstall. Trigence offers agentless application virtualization, but they haven't made it possible for me to evaluate it.

For a database server, the objective is to change it as little as possible. That's why I prefer agentless solutions. A large assortment of agentless virtualized applications can be downloaded from www.thindownload.com. Another agentless solution worthy of consideration is Portable Apps. While technically not fully virtualized applications, the applications that are included with or can be added to Portable Apps run without being installed. This makes the Portable Apps suite a quite useful suite of tools for servers. I copied the Portable Apps suite to a Corsair high speed USB memory stick. You can use any USB memory stick, but I did notice a considerable performance improvement for high speed sticks compared to tradeshow freebies. Last week I needed to compute a MD5 hash for a SQL Server installation iso file. I inserted the USB stick and ran winMd5Sum without installing it or in any way messing up the server.

Virtualized applications are also great for application testing, but that's a story for another day.