These questions are based on: 70-229 – Designing and Implementing Databases with Microsoft SQL Server 2000 Enterprise Edition Microsoft Self-Test Software Practice Test.
Objective: Retrieving and modifying data
SubObjective: Import and export data; methods include the bulk copy program, the Bulk Insert task and Data Transformation Services (DTS)
Item No. 70-22.214.171.124
Single Answer, Multiple Choice
You are the SQL Server 2000 administrator for an apparel retailer with stores worldwide. Your company wants to use SQL Server to improve sales and inventory update procedures. Consultants have been hired to do an in-depth study of company procedures. Each consultant will only be analyzing one area of the company.
The consultants periodically will need access to data that is in a SQL Server 2000 database named Sales, which is about 750 MB. The consultants will need access to only a small portion of the data, however. The consultants will be dialing into the network to download their data.
You create a snapshot of the database that you will use for testing. Any proposed procedural changes will first be implemented on this copy of the database.
You want each consultant to have access to only the portion of the database they need to fulfill their contractual duties. Only the appropriate users should be able to transfer the data to the consultants. In addition, the consultants might need their data updated periodically.
What is the best method to transfer the data so it can be used by the consultants?
- The bcp utility
- Snapshot replication
- Transactional replication
- A SELECT. . . INTO statement
- Data Transformation Services (DTS)
- Data Transformation Services (DTS)
You should use Data Transformation Services (DTS) because the consultants should have access only to the portion of the data they need. DTS will allow you to create custom packages for each consultant.
You should not use the bcp utility. The bcp utility is used to import data from a text file into SQL Server or to export data from SQL Server to a text file. A text file would not be the most appropriate form of the data for the consultants.
You should not use snapshot replication. Although snapshot replication could be used, each consultant would need the appropriate permissions. In addition, security risk would be greater because the consultants could possibly be granted the wrong permission.
You should not use transactional replication because the consultants do not need updates with each transaction.
You should not use a SELECT…INTO statement. A SELECT…INTO statement can only be used to transfer data from one table or view to another table.
Using the DTS Export Wizard, consultants can receive only the portion of the database they need. Because these individual packages transfer only a portion of the data, the transmission time will be relatively small, as compared with the transmission of the entire database. For only certain users to be able to initiate the transfer, passwords can be applied to the packages. An owner password will allow the end-user to execute and modify the package. A user password will allow only the end-user to execute the package. The consultants will be given a user password, and you will have an owner password. The consultants can periodically connect to the database and re-execute the package, thus updating their data. When a consultant’s project is complete, the DTS package would simply be deleted.
1. Microsoft SQL Server 2000 Books Online – Data Transformation Services
- DTS Basics
2. MCSE Training Kit Microsoft SQL Server 2000 Database Design and Implementation – Managing and Manipulating Data
- Lesson 1: Importing and Exporting Data – Using DTS