Letters, November 2002

An Easier Way to Restore to a New Server?

Kalen Delaney's article "Safe Transit" (September 2002, InstantDoc ID 25983) provided a great description of how to move your database to a new server and how to reconnect and match up login and user IDs by using the new stored procedures sp_sidmap and sp_prefix_sysusersname. However, a simple way of accomplishing the same task is to perform frequent backups of the master database so that you have a current picture of your system, then restore master as well as the user database to the new server. This approach doesn't require new login or user IDs and doesn't require new SIDs. Everything just connects and works the way it always did.

—Andrew Lambert
[email protected]

Restoring the master and user databases to the new server is an excellent solution when you're restoring a database to a SQL Server that doesn't already have other databases and logins on it and when it can be used as part of the process of restoring a complete system, which I'll explain in an upcoming article. But if you're moving the database onto a SQL Server that already hosts other databases and other users—and has its own set of logins—just restoring a backup of master from another system won't serve the existing users of the new server.

—Kalen Delaney

Wholesale Deletes

I find Michelle A. Poolet's articles about database modeling and design valuable, and I have a couple of questions about deleting rows. First, I need to know how to delete all the rows from two tables that have the following relationship: TableA has the primary key pa and the foreign key pb, which references TableB. And TableB has the primary key pb and the foreign key pa, which references TableA. Do I first need to delete the constraints that exist between the tables, delete the rows from the tables, then recreate the referential-integrity constraints?

Second, can you reinitialize an integer IDENTITY field? I want to reinitialize the field after all the rows in the table are deleted. For example, suppose I have an int IDENTITY field studentno in the Student table. I add 10 records, then delete all the records from the table. After I delete all the records and try to insert a row, however, the new IDENTITY value is 11—instead of 1, as I want. Do I need to drop the table, then recreate it?

—Priya K.
[email protected]

Because you're going to delete all the rows in both TableA and TableB, dropping and recreating the tables may be the fastest, most straightforward way to delete the rows and to reinitialize the IDENTITY column. But first, script these two tables and any associated constraints, triggers, indexes, and other database objects. For instance, if a third table has a foreign key constraint mapped to TableA or TableB, you have to drop that constraint before you can drop TableA and TableB. If you script first, you'll have a record of every database object that's dependent on TableA and TableB, and you'll be able to recreate the objects quickly and easily.

—Michelle A. Poolet

Cubes: The Simpler the Better

I regularly read Russ Whitney's Mastering Analysis column, and I have a related question. I'm trying to create a cube that has two time dimensions: one for the sales date and the other for the received date. I need to determine the turnaround time for each order (Received date - Sales date). Unfortunately, when I go to analyze the cube, it asks me to pick a time dimension. Any advice?

—Ciaran
[email protected]

Your question is common in analysis circles. I recommend building the cube with only one time dimension—use one date or the other. Then, you can create a measure for the turnaround time that shows the Received date minus the Sales date. If you really need to do some analysis with the other date, you can create a second cube that contains the same information as your first cube but that uses the other date for the time dimension. Cubes are like many other things in life: the simpler (more focused) the better.

—Russ Whitney

Demystifying DTS, Using DB2/390 as the Source

I used Itzik Ben-Gan's article "DTS and the Data Warehouse" (August 2002, InstantDoc ID 25544) to implement a refresh process that updates data from DB2/390 to SQL Server. Listing 1 shows the DB2 SQL code for setting up the source. I had to modify Ben-Gan's code in a few areas. For example, I needed to use two triggers for the UPDATE rollback on productid. The only other change I made was on the target side; I had to set up a linked server connection to the destination database as a separate connection. I kept getting errors from the DB2 driver when I tried to connect directly through ODBC. Ben-Gan's article and scripts have been very helpful to me, and I hope other readers find the DB2/390 version of the code useful.

—Mark Buzzard
[email protected]

Correction

In "Bottleneck Blues," May 2002, InstantDoc ID 24518, page 45, the sentence "Although DTS also uses bcp by supporting the BULK INSERT task, DTS can't schedule copy out and copy in separately" should have read, "Although the DTS Copy SQL Server Objects task also uses bcp, this task can't schedule copy out and copy in separately." On the same page, the sentence "Snapshot replication and DTS are user-friendly and functionally powerful wrappers of the bcp utility" should have said, "Snapshot replication and DTS are user-friendly and functionally powerful alternatives to the bcp utility." We regret any inconvenience these errors might have caused.