Disclaimer:
***Although the steps outlined in this blog will work, it is NOT supported by Quest. Use at your own risk and be prepared to recreate your database if something goes wrong.***
This article is intended for those familiar with Quest’s migration products but will work in general for any SQL table in which you want to set a field back to NULL. Quest uses MSDE or SQL to store its migration database and there are many tables and relationships within the database to track and execute the migrations. The tables are always being updated by the migration processes but one of the things that doesn’t happen automatically is clearing values in a field that has already been populated. This is by design to prevent you from accidentally destroying your database, however, if you know what you’re doing you can safely remove values by running an update directly against the SQL table.
For example, I have an MSDE 2005 database and I installed the SQL Server Management Studio Express Edition (SSMSEE) so I could manage the local instance of my server.
After opening the SSMSEE and navigating to the table which contains the data I want to clear I create this update routine which will select the data I’m interested in and set the value back to NULL.
Here’s a look at the database without any filters. Note the ExchangeMailboxStore value. Let’s run a query to make sure we’re isolating just the fields with a value in it.
I performed a quick query to make sure I’m selecting the data I wish to update. For my example, I wanted to clear the ExchangeMailboxStore field. Here’s the query.
Now to perform the actual update you simply paste this SQL Query into the query window and execute it (!). In a couple of seconds it will update your data. Refresh the table and you should see NULL in the field you chose.
UPDATE T_NMEObjects
SET ExchangeMailboxStore = NULL
WHERE (ExchangeMailboxStore IS NOT NULL)
SET ExchangeMailboxStore = NULL
WHERE (ExchangeMailboxStore IS NOT NULL)
Now you should see a popup indicating how many rows were affected by your update and you should see your fields and their new null value. That’s it. As I indicated at the beginning of this blog, this procedure is not supported by Quest per se, but it’s good to know it will work in a pinch if you have no other recourse.
