Putting New Data into SQLSpyNet with the DTS Wizard

The DTS Wizard is a very flexible tool that allows us to import data from almost any data source, including Excel, DB2, Oracle, Access, and even Text files. Why do we need this? Many organizations have data spread throughout the company. Charles will have a spreadsheet of his customers, Mary will have a small Access database that has all her suppliers, and Jenny, the secretary, might have a text file (word document) with the names and phone numbers of all the staff members.

With all of these disparate types of information floating around the office it's pretty obvious that data management can become a real nightmare! Duplication of data is inevitable, and retrieval is almost impossible.

But here comes SQL Server 2000 to the rescue! With the introduction of DTS in SQL Server version 7.0, many organizations have been able to relieve the pain of trying to get all these disparate pieces of information together. The DTS Wizard is a point-n-shoot approach to import (or even export) data into our database. We go through a series of steps, selecting the database into which to enter the data, where the data is to come from, and so forth.

This is all fine for simple data, but what happens when we have more complex data that doesn't have a clear definition between first name and surname? DTS can even take care of this, with your help of course.

Within a DTS package we can define VBScript that can be used to manipulate, format, or rerun a process. This gives us great flexibility when it comes to altering our data either before, during, or after the insert to the database.

There are many aspects to DTS packages, and we will only have a quick look at the basics. I suggest that you play with DTS packages in SQL Server 2000 because they rock! And the flexibility and control you have over the import/export process will surprise you.

Before we actually insert the data into our database, we are going to delete all the data out of our database. This will allow us to start from a clean slate. But just before we do this though, let's back up our database.

Backing Up the Database Before the Transfer

You might be wishing you didn't have to do this, but do you want to know the cool thing? You do not have to write the backup statement again! Because we wrote a backup task earlier (see Chapter 11, the section titled "Scheduling Jobs"), we can actually force it to run immediately. This means we can create a full database backup just by right-clicking on the job (under Management, SQL Server Agent, and then Jobs), and selecting Start Job.

You will see that the status of the job is set to executing. When the job has finished, the status will change to either Succeeded or Failed.

So there we go, a backup done nice and simply, with no extra code!

Debugging Stored Procedures

I have performed many tasks in numerous different roles, and one of the most frustrating has been debugging stored procedures. But no more! The ability to debug stored procedures as though we were debugging any development platform code is part of one of the enhancements to SQL Server 2000's Query Analyzer. We can insert break points, step into, step over, and so forth. This is wonderful for those of us who have tried to monitor what is happening in a stored procedure.

Previously we could do this with SQL Server 7.0 and Visual InterDev, but there was a lot of overhead in setting it up. With the new debugging tools, all we do is right- click and select Debug. How simple is that?

We have the standard debugging windows as well. We can get the values of variables from the Watch window and view the procedures that have been called and not completed in the Callstack window. This makes it easy to migrate from a development environment to using SQL Server 2000. So you Access developers out there must be getting really excited by now!

What? No More Room?

One of the trouble spots a DBA must keep an eye on is conserving a computer's most precious resources, memory and disk space.

If we have several databases on one server, we can find that we run out of disk space, and if that happens, our databases will fail.

Of course, in Spy Net's fictional scenario, that could mean World War III! But in the real world, running out of space can still cause serious problems, especially in mission-critical databases such as utilities or emergency response systems. In this section, we look at the causes of resource failure and several ways to avoid down time, including managing file and log size.

How Memory Affects Database Transactions

The memory-deprived databases will fail because tempdb is where most of the changes that you make to your data are performed before they are committed to disk. If you have enough RAM available, SQL Server 2000 will put as much of your database as it can up into RAM. After all, it is much faster to read from RAM than to scan a disk for the information. However, if RANI is a short commodity or you have concerns about the amount of disk space your database is eating, relax, because we have even have control over that.

When you are in Enterprise Manager you have the option to view how much space your data files for your database are allocated and how much is used. To see this information, simply click the SQLSpyNet (or any other) database within Enterprise Manager, and you will see a screen.

Shrinking Your Data Files to Reduce the Database

When we are talking about shrinking our data files we are not actually referring to the process of compacting them like a zip program would.

If we shrink our data files, we remove unused data pages. For example, if we had a table that had five data pages on which it stored the data, and we deleted two pages worth of the data, although our table would have only three pages that actually stored data, SQL Server 2000 still would have five pages allocated to the table.

When we shrink the data files, we just get rid of the extra two pages that the table was using. This does, however, have restrictions, but I think you get the idea.

What do we do when our data files are too large? Although we cannot shrink an entire database smaller than its original size, we can shrink our data files smaller than their original allocation sizes. We must do this by shrinking each data file individually by using the DBCC SHR I NKF I LE Transact-SQL statement. This allows us to reallocate how much space the given data file is allowed to use.

Build Your Digital Command Canter Part 1

The popularity of Star Trek is based, in part, upon our fascination with advanced technology. We're excited when Captain Picardorders his crew to take The Enterprise to another galaxy with the simple command "Make it so." In the distant future, it seems, we'll be able to entertain our merest whim by pushing a button or by telling a computer what we want done. Powerful machines will do the rest.

To succeed at digital marketing, you need to build your own version of The Starship Enterprise. You need to develop a digital command center in your company to communicate on a day-by-day basis with your customers and prospects. Unlike a traditional marketing department — often far removed from direct contact with customers — a digital marketing department must be hardwired to Customers. E-mail messages, for example, must be read and replied to Within hours, if not minutes, and your Web site content may need to be updated and changed daily. In addition, your customer database should be constantly churning with new information uploaded from your salespeople, your customer service representatives, and your customers.

Integration in the Digital Economy

To gather, store, process, and distribute digital information, you need to integrate your company into the digital economy. In the next three to five years, you will need the following capabilities in your company.

• Every computer is connected over a network using highspeed cable. This network includes an internal e-mail system that allows for the sending and receiving of Internet e-mail messages.
• Everyone involved in your company — employees, customers, and suppliers — are able to communicate by e-mail with everyone else in your company. As bandwidth becomes greater, you can upgrade your e-mail capability to include video-mail and live video-conferencing.
• Employees and contract workers are able to work at home, or at satellite offices, as easily as if they were in the office. Note: In the digital world, you may not have an office; your entire operation may run over a central server that electronically connects your organization.
• Your internal computer network is connected directly to the Internet over high-speed telephone or cable lines. This means every computer on your network is connected to the Internet at all times. In fact, it means your computer network is actually part of the Internet.
• You have security measures in place which restrict access to private corporate information on your network. One solution is to set up an Internet fire wall.
• Your marketing presentations, internal communications, and training courses exist in a multimedia format which combines text, graphics, sound, and video. You have software that allows for the easy development of multimedia content.
• You have one or many different types of online servers running on your network. This includes one or more Web servers, a BBS network server, and online database servers. Each of these servers are available to selected people within your organization over an Intranet, or outside your organization by way of the Internet.
• As an alternative to internal online servers, you can lease space with service bureaus. For example, you can rent space on a Web site that is halfway around the world. You can update its content as if the server was in your office. You may use a service bureau to set up and run your online BBS system. Remember, you don't need to buy expensive digital equipment in order to use it — you can rent it as well.
• Your customers access information about your business using a variety of digital communications tools including the telephone, fax, e-mail, the World Wide Web, interactive kiosks, and CD-ROM, and perhaps also through some sort of virtual reality (VR) device. Ideally, your customers are able to purchase your products and services electronically.
• You have an extensive database on all your customers and prospects. This database is relational in structure. All your digital communications and your digital marketing promotions are aimed at increasing the size, quality, and complexity of this database.
• All your computer systems are compatible. In the computer industry, this is known as "Open Systems Architecture." This means all your corporate information is accessible, no matter what type of computer is used. As well, your information should be accessible through all digital devices, not just computer-related ones.
• Your employees are fully fluent in the ways of basic digital technology. Everyone in your organization knows how to use the three major types of software programs: word processing, spreadsheet, and database. They are comfortable creating _and viewing multimedia content. They understand the basic concepts of the Internet, and can find the information they need on it.

In order to succeed at digital marketing over the long term, you need to set up this type of infrastructure within your company. However, man didn't get to the moon in one day. So take it one step at a time. Here's an explanation of how to take the first steps:

Working with Transactions with an SQL Server Database

Transactions provide a way for you to group together database executions as a group so that they succeed or fail together. For example, if you had an e-commerce site you may have code that allows visitors to add a quantity of an item to their shopping cart. When you do that you also want to remove the number of items ordered from your inventory.


Therefore, you have two actions that you need to execute. You want to add items to a shopping cart and you want to remove items from inventory.

These executions need to happen as a group. You don't want to add items to the shopping cart if something goes wrong with removing them from inventory. And the opposite is also true.

Therefore, the database executions need to be grouped in a Transaction. This technique shows you

how to use a Transaction object with an SQL Server database.

This ASP.NET page contains SQL Delete statements that delete records from the Employees table. But the records are not deleted because they are in a transaction and the transaction is not committed to the database.

The code that performs this task fires when the ASP.NET page loads:

Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs) Within that procedure you will need a Connection object and a Command object:

Dim DBConn as SQLConnection Dim DBDelete As New SQLCommand

You will also need a Transaction object:

Dim DBTrans As SQLTransaction

You start by connecting to the SQL Server database:

DBConn = New SQLConnection("server=localhost;"
& "Initial Catalog=TT;" _
& "User Id=sa;" _
& "Password=yourpassword;")
and opening that connection:
DBConn.Open()

You then start a transaction by calling the BeginTransaction method of the Connection object. That method returns an open transaction, which is placed into the local Transaction object:

DBTrans = DBConn.BeginTransaction()

The Command object will connect to the database through the Connection object:

DBDelete.Connection = DBConn

It will also use the Transaction object:

DBDelete.Transaction = DBTrans

Next, two records are deleted and executed from the Employees table:

DBDelete.CommandText = "Delete From Employees
& "Where EmpID = 1" DBDelete.ExecuteNonQuery() DBDelete.CommandText = "Delete From Employees
& "Where EmpID = 2"DBDelete.ExecuteNonQuery()

But the records are not actually deleted from the database since the RollBack method of the Transaction object is called:

DBTrans.RollBack()
lblMessage.Text = "No action was taken."

The RollBack method causes the queries that were executed within the Transaction object to be cancelled.

You could instead call the Commit method:

'DBTrans.Commit()

['his method causes the pending execute statements to be executed as a group so that they fail or succeed together.

Retrieving Data from a Stored Procedure

Stored procedures frequently return data from a database. They can return single values, tables joined together, or all the records from a single value, and much more. You can call stored procedures that return data from your ASP.NET pages. This technique shows you how to do that..

USE IT The page defined for this technique displays in a DataGrid control all the employees who have a birthday in the current month. The DataGrid has this definition:

asp: datagrid
id="dgEmps"
runat="server" autogeneratecolumns="True"
/