You work at the regional sales office. You are responsible for importing and exporting data in SQL Server 2005 databases. The main office asks you to send them a text file that contains updated contact information for the customers in your region. The database administrator in the main office asks that the data be sorted by the StateProvince, Surname, and FirstName columns. You need to satisfy these requirements by using the least amount of effort. What should you do?

You are writing a query that returns data from tables in a SQL Server 2005 database. Some of the data is stored in relational columns, and some of the data is stored in XML data type columns. Your query needs to return a relational result set that contains data from relational fields and attribute values from XML data type columns. Which two methods of the XML data type should you use? (Each correct answer presents part of the solution. Choose two.)

You are a database developer for a company that manufactures bicycles.

You are creating a Web application that will retrieve data from a database named AdventureWorks. The application connects to a database server that is running SQL Server 2005. One of the functions of your application will allow shift supervisors to see how many bicycles were manufactured during the current shift. A user named Tom creates a stored procedure named usp_GetPersonData in the dbo schema. You are creating an HTTP endpoint that exposes a Web method named PersonData. This Web method allows client applications to access the stored procedure. You use the following statement to create the endpoint.

CREATE ENDPOINT SQLEP_AWPersons AS HTTP (PATH = ‘/AWpersons’, AUTHENTICATION
= (INTEGRATED), PORTS = (CLEAR), SITE = ‘SQL1’) FOR SOAP (WEBMETHOD ”
(NAME=”), BATCHES = DISABLED, WSDL = DEFAULT, DATABASE = ‘AdventureWorks’,
NAMESPACE = ‘http://Adventure-Works/Persons’) You need to specify the correct WEBMETHOD and
NAME to create the endpoint. Which command should you use?

You use Service Broker to manage data requests between SQL Server 2005 databases. You need to make changes to objects for an existing service. You want to ensure that no messages can be received by the service until the changes are complete. What should you do?

You are creating a Web-based application to manage data aggregation for reports. The application connects to a SQL Server 2005 database named DataManager. One page in the application has controls that execute stored procedures in a database named ReportingDatabase. There is an existing Service Broker connection between the DataManager database and ReportingDatabase. You want to add two new message types to the existing service. In each database, you create message types named ProcessReport and SendResult. You need to add the two new message types to the existing service. What should you do first?

The sales department and the finance department are located in different buildings. The two departments are connected by a 2?megabit network. Both departments use SQL Server 2005. The sales department records all sales activities in a database named DB1. DB1 contains 380 tables and has a size of 10 GB. The finance department has read-only access to four tables in DB1 for reporting purposes. The finance department runs queries throughout the day and needs the data to be as up to date as possible. Users in the finance department tell you that report execution is slow. To optimize network capacity, your department decides to provide copies of these four tables to the finance department. You need to provide copies of these tables. You want to achieve this goal by using the copy method that uses the least amount of network bandwidth. What should you do?

You are responsible for managing a SQL Server 2005 database. This database is updated with data that is exported from another site. On a monthly basis, data is loaded into the database from a flat file by using the bcp utility. This operation should be minimally logged.

However, users report that they receive error messages indicating that the transaction log is full. You investigate and discover that the errors occur when the data is being imported. You need to prevent this problem from occurring. What should you do?

Your company uses a SQL Server 2005 database. Customer data from your trading partners is imported into the Customers table every night. You need to ensure that the customer record is updated if it already exists in the Customers table. If the record does not exist, the data needs to be inserted into the Customers table. What should you do?

You are planning to import customer data from the Customer table and the Country table in your SQL Server 2000 database into a SQL Server 2005 database. You want to ensure that during the import process, each value in the CountryCode column of the Customer table has a corresponding record in the CountryCode column in the Country table of the SQL Server 2005 database.

You define a foreign key between these two tables. Thus, referential integrity will guarantee that the import routine fails if there are any records where a CountryCode value does not exist in the Country table but does exist in the Customer table. You need to ensure that the import process does not fail if records are encountered that do not exist in the Country table. What should you do?