You are creating an HTTP endpoint that will be used to provide customer data to external applications. Your

SQL Server 2005 computer is named SQL1. You create a stored procedure named dbo.usp_GetPersonData to

retrieve the data in the AdventureWorks database. You create the endpoint by using the following code.

CREATE ENDPOINT SQLEP_AWPersons

AS HTTP

(PATH = ‘/AWpersons’,

AUTHENTICATION = (INTEGRATED),

PORTS = (CLEAR),

SITE = ‘SQL1’)

FOR SOAP

(WEBMETHOD ‘PersonData’

(NAME=’AdventureWorks.dbo.usp_GetPersonData’),

BATCHES = DISABLED,

WSDL = DEFAULT,

DATABASE = ‘AdventureWorks’,

NAMESPACE = ‘http://Adventure-Works/Persons’)

The first users to connect to the endpoint tell you that they do not get any data. You connect to the endpoint and

discover that it is not responding.

You need to modify the endpoint so that data is returned as expected.

What should you do?

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 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 creating a SQL Server 2005 application that will store and manage legal documents.

The original documents are stored as XML documents on a file server. The application will be used to insert the

documents into the database. Later, the documents must be retrieved from the database. These documents must be

identical to the originals.

You need to design a table to store the document data.

What should you do?

TestKing.com has multiple servers in a distributed environment. You work with two SQL Server 2005 computers named SQL1 and SQL2. Each server uses SQL Server Authentication and they use different logins.
You need to write a distributed query that joins the data on SQL1 with the data on SQL2.
What should you do?

You configure a new SQL Server 2005 computer to use TCP/IP with all default settings.
Your corporate policy requires that each server use a firewall. You find that you can connect to the SQL Server instance from the local computer. However, client computers cannot connect to the SQL Server instance.
You need to identify the most likely cause of the connection issues. What should you do first?

You are preparing for a new installation of SQL Server 2005. You need to select the protocols that client computers might use to connect to the server.
Which two protocols can you use to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

Your application must access data that is located on two SQL Server 2005 computers.
One of these servers is named SQL1 and the other is SQL2. You have permissions to create a stored procedure on SQL1 to support your application. However, on SQL2 you only have permissions to select data.
You write the stored procedure on SQL1. The stored procedure accesses SQL2 by using the OPENQUERY Transact-SQL statement. However, the query fails when executed.
You need to troubleshoot the cause of the error. What should you do?

You work in San Francisco at the main office of your company. You are responsible for managing a SQL

Server 2005 database.

The sales department wants a report that compares customer activity in the previous quarter between the main

office in San Francisco and the branch office in Paris. They want the data sorted by surname and first name.

You restore a recent backup of the Paris database onto your server. You write queries to build the report, ordering

the data by the Surname and FirstName columns. You review the data and notice that the customer list from the

Paris database is sorted differently. The sales department needs the revised data within 15 minutes for a

presentation.

You need to implement the fastest possible solution that ensures that the data from both databases is sorted

identically.

What should you do?

You are responsible for maintaining scheduled jobs for the SQL Server 2005 databases in your department. One of the jobs aggregates data from multiple sources for reports. This job runs daily. The job consists of multiple steps.
Each step aggregates data for a specific report. A user reports that the data for some of the reports has not been updated recently. You need to ensure that every step of the job that aggregates data executes even when errors occur. What should you do?