• Article

SQL Server 2000

Scripts for Remote Management

Create an ASP script that you can reuse for many data-management purposes

I often work on Web sites that run on remote servers at an ISP. I don't always have access to all the management features on the servers, such as Enterprise Manager or Internet Service Manager, and lack of access hinders my ability to remotely manage the databases. In many cases, I need to write code to accomplish certain database management tasks. I often later reuse methods, modifying them for the purpose at hand. This situation calls for scripts that I can write once and use many times for similar purposes.

For example, at a client site, I recently encountered a live application that required a structural change to the database to properly handle contact data. The database held information about contacts in two places. The first place was the Contact field in the master Customer table, which contained the contact's full company name. This configuration didn't provide a way to associate two or more contacts with a company. The second place was the InterestList table, which contained contact information about people who specified their interests by filling out a Web form. The result of this setup was that neither the Customer table nor the InterestList table contained complete contact information. Also, the Customer table didn't contain an email field for the contact. A driving force in restructuring functionality was my need for an automated way to email large groups of people from my Web site, so I needed to capture the customers' email addresses.

To consolidate this customer information, I created a new table called Contact. Screen 1 shows the new table's structure. The table includes standard information such as first name, last name, phone number, and fax number. A foreign key, CustID, links a record back to a customer. If the record is from the interest list or for an instructor, then CustID can remain blank.

The table also includes several flags that designate the contact's status:

  • InstructorFlag-the contact is an instructor
  • InterestListFlag-the contact came from the InterestList application on the Web
  • PendingVerification-the contact information needs validating
  • PrimaryContact-the contact is the key contact for a customer

I used the PendingVerification field to specify that someone needs to verify the contact's information. I created a report that lists the new contact's information and shows which contacts need verifying. Clicking the contact name on this report opens a maintenance page where users can change the flag and other contact data. Note that Screen 1 shows default values for each flag field. I used the Transact- SQL (T-SQL) GetDate function to set the default value for the DateAdded field to the current date.

Next, I needed to move the existing contact data into the new table. I could have used Data Transfor-mation Services (DTS) or tried to export and import data, but both these methods are difficult to use on a remote system and hard to perform with data in different fields and tables that don't match the target table. Also, I needed to move the data quickly because I didn't want to take the system down for long.

Here's the simple approach I used. First, I found some Active Server Pages (ASP) and ADO database code that used the existing Customer table, and I placed the code into an ASP script. Then, I modified this code to extract all records from the Cus-tomer table and insert the contact information into the new Contact table. From the Contact table, I needed to copy the contact name into the FirstName and LastName fields. I also needed the Phone, Fax, and CustID fields.

Here's how the code in Listing 1 works. The code in Callout A performs startup actions such as defining variables, instantiating ADO objects, and setting parameters. The last step it performs is opening the rsCust record set. Next, the Do While statement starts a loop through the record set. The first line in Callout B extracts data from the Contact field and places it in the sFirstName variable. Then, the code calls the instr function to find the first space in the name:

  i = instr(sFirstName," ")

If the sFirstName field contains a space, the If statement divides the name and assigns the first part to the sFirstName field and the remaining string to the sLastName field:

  if i > 0 then
sLastName = right(sFirstName,len(sfirstname)-i)
sFirstName = left(sFirstName,i-1)
end if

The first line in Callout C outputs selected fields to the browser to serve as a log and an indicator of the work performed. The next three statements set the sSQL variable to the correct Insert statement, then set the CommandText property of the command object to that text. The next line executes the command and the code loops to the next record.

With these steps completed, I now had a simple ASP script that could perform the data transfer for me. Because the script was nondestructive, I ran it many times to test it. Next, I notified all users not to enter data during the time it took me to move contacts into the new table. When the application worked correctly, I deleted the Contact table and reran the script to transfer the contact data.

Next, I found all the code in the Web application that used the contact information from the Customer table. I changed my script to use the new Contact table, and I was able to make the change online because both the old and new tables contained the contacts. I could easily work on a copy of the script. When it worked correctly, I could copy it back to the original file name, effectively turning it on. To move files, I used either the Visual InterDev Project Explorer or the Visual InterDev editor.

I also needed to change the online maintenance pages for the contact information. To make this change, I deleted the contact field from the customer maintenance page and linked the field to the new contact management page. After all the pages that used the new Contact table worked correctly, I deleted the Contact column from the Customer table.

Next, I tackled the problem of moving the data from the InterestList to the Contact table. I copied the Listing 1 script and modified it to work with the InterestList table structure. I added a few more fields and set the InterestListFlag to Y. The process went smoothly because not as many tables used the InterestList as used the Customer table.

If you take the time to create a solid ASP script to move data from one table or format to another, the script will serve as a useful basis in your future remote management efforts. You can easily implement the script in Listing 1 across the Web, which makes it a compelling, reusable tool.

Bugs, comments, suggestions | Legal | Privacy | Advertising

Copyright © 2002 Penton Media, Inc. All rights reserved.