You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 hosts two database named CK_Sales and CK_Inventory. You create a Service Broker to enable the CK_Sales and CK_Inventory databases to communicate. You want to create a new service named // Domain.com/Income.
What should you do first?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 hosts two database named CK_Staff and CK_Projects.
You create a Service Broker to enable the CK_Staff and CK_Projects databases to communicate. You create two message types named // Certkiller /Projects/Budget and // Domain.com/Projects/Expenses as well as the queue named // Domain.com/Expenses/ExpenseQueue.
You create a contract named // Domain.com/Expenses/ExpenseSubmission that uses the two message types and the queue. You want to create a new service named // Domain.com/Expenses that will listen for and accept messages from // Domain.com/Expenses/ExpenseSubmission by using // Domain.com/Expenses/ExpenseQueue.
Which CREATE SERVICE statement should you use?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Some of the servers on the Domain.com network run Windows Server 2003 while the rest run Windows NT 4.0 Server. The client computers run Windows 98, Windows NT 4.0 Workstation and Windows XP Professional.
The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 runs Windows Server 2003 and hosts a database named CK_Projects. You need to enable the CK_Projects database to accept Simple Object Access protocol (SOAP) requests. You plan to accomplish this by creating a Hypertext Transfer Protocol (HTTP) endpoint. You want to ensure that the most secure authentication method is used when client computers connect to the HTTP endpoint.
What should you do?

You work as a database developer at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com that manufactures bicycles. At present you are creating a Web application that will retrieve data from a database named Certkiller -DB01. Currently the application connects to a database server that is configured to run SQL Server 2005. One of the functions of this application will permit shift managers to see the amount of bicycles that was manufactured during the current shift. A Domain.com employee named Kara Lang creates a stored procedure named usp_GetPersonData in the dbo schema. You create an HTTP endpoint that exposes a Web method named PersonData. This Web method permits client applications to access the stored procedure.
Kara Lang makes use of 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 = ‘ Certkiller ‘,
NAMESPACE = ‘http://Adventure-Works/Persons’)
You need to identify the correct WEBMETHOD and NAME to create the endpoint.
What command should you use?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional.
The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. The database instance running on Certkiller -DB01 uses the Active Directory account named CK_db01. You need to enable one of the databases to accept Simple Object Access protocol (SOAP) requests. You plan to accomplish this by creating a Hypertext Transfer Protocol (HTTP) endpoint that uses Kerberos authentication. You need to ensure that the Service Principal Name (SPN) is automatically associated with the database instance running on Certkiller -DB01.
What should you do?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that hosts a database named CK_Projects. The CK_Projects database contains a table named Documents that contains an XML column named DocLocation. Domain.com users complain that queries against the DocLocation column are processed very slowly. You want to improve the performance of queries that are run against the DocLocation column by creating an index on the column.
What should you do?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. You have received instruction from the CIO to create an online catalog application that will display the product information on the Domain.com Web site. The product information is stored in a SQL Server 2005 database server named Certkiller -DB01. The CIO informs you that the data is stored as relational data. However, the data should be passed to the application as an XML document by using FOR XML.
A Domain.com employee named Clive Wilson works in the Research and Development department. You instruct him, to test the application. Clive Wilson detects that not all the items matching the query appear in the XML document. He determines that only the products that have values for all elements in the schema appear in the XML document. You instruct him to modify the Transact-SQL statement in order that all products matching the query appear in the XML document.
What should Clive Wilson do?

You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Domain.com has its headquarters in Washington and branch offices in Detroit and Delaware. All servers on the Domain.com network run Windows Server 2003 and all client computers run Windows XP Professional.
The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that is located at headquarters. Certkiller -DB01 hosts a database named CK_Sales that stores sales data for the entire company. While Certkiller -DB01 is offline for maintenance purposes, all sales data are collected in XML documents. Once Certkiller -DB01 is back online, you must add the data from the XML documents to the CK_Sales database.
What should you do?

You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database server named Certkiller -DB01.
A Domain.com user named Ally Wagner is a member of the Research and Development department. You instruct her to create a stored procedure that will delete data from the Contact table in a Certkiller -DB01. The stored procedure encompasses the subsequent Transact-SQL statement to handle any errors that occur.
BEGIN TRY
BEGIN TRANSACTION
DELETE FROM CK_Sales.Products
WHERE ProductID = @ProductID
COMMIT TRANSACTION
END TRY
BEGIN CATCH
DECLARE @ErrorMessage nvarchar(2000)
DECLARE @ErrorSeverity int
DECLARE @ErrorState int
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState) END CATCH
Ally Wagner tests the stored procedure and detects that it leaves open transactions. You inform her to modify the stored procedure in order that it properly handles the open transactions.
What should she do?

You work as the database administrator at an investment company named Domain.com. Domain.com has its headquarters in New York and branch offices in Boston, Atlanta, Miami, and New Orleans. The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01 that is located at headquarters. Certkiller -DB01 contains a large database named CK_Investments. The CK_Investments database stores investment data for each office. You need to implement a parameterized stored procedure that will decrement the AvailableInvestments table when a real estate investment is secured. The stored procedure must accept the OfficeID, InvestmentTypeID, StartDate and EndDate as input parameters.
What stored procedure should you use?