You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to
BillingUsers.
Which Transact-SQL statement should you use?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads data from a source system that uses the SQL Server change data capture (CDC) feature.
You have added a CDC Source component to the data flow to read changed data from the source system.
You need to add a data flow transformation to redirect rows for separate processing of insert, update, and delete operations.
Which data flow transformation should you use?

• A. Pivot
• B. CDC Splitter
• C. Merge Join
• D. DQS Cleansing

HOTSPOT
You are developing a SQL Server Integration Services (SSIS) package to implement an incremental data load strategy. The package reads rows from a source system and compares them to rows in a destination system. New rows will be inserted and changed rows will be updated.
You have used a Lookup transformation and a Conditional Split transformation. The Lookup transformation joins the source and destination table on the business key, and includes all columns from the destination table in the data flow output. The Conditional Split transformation inspects the destination columns and directs data flow to either insert new records or update existing records.
You need to configure the Lookup transformation to ensure that all records flow to the Conditional Split transformation, regardless of whether the rows match an existing row in the destination table.
Which setting should you select? (To answer, select the appropriate option in the answer area.)

You are the senior database administrator for a Microsoft SQL Server environment that uses Windows Azure SQL Database.
You need to grant a junior database administrator only the permissions required to create new logins.
Which Windows Azure SQL Database role should you assign the junior database administrator?

• A. loginmanager
• B. serveradmin
• C. securityadmin
• D. setupadmin

You are developing a SQL Server Integration Services (SSIS) package to load data into a
Windows Azure SQL Database database. Thepackage consists of several data flow tasks.
The package has the following auditing requirements:
- If a data flow task fails, a Transact-SQL (T-SQL) script must be executed. - The T-SQL script must be executed only once per data flow task that fails, regardless of the nature of the error.
You need to ensure that auditing is configured to meet these requirements.
What should you do?

• A. Store the System::SourceID variable in the custom log table.
• B. Use an event handler for OnError for each data flow task.
• C. Enable the SSIS log provider for SQL Server for OnTaskFailed in the package control flow.
• D. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_package stored procedure.
• E. View the job history for the SQL Server Agent job.
• F. Deploy the project by using dtutil.exe with the /COPY SQL option.
• G. Create a table to store error information. Create an error output on each data flow destination that writes OnTaskFailed event text to the table.
• H. Store the System::ExecutionInstanceGUID variable in the custom log table.
• I. Create a table to store error information. Create an error output on each data flow destination that writes OnError event text to the table.
• J. Create a SQL Server Agent job to execute the SSISDB.catalog.create_execution and SSISDB.catalog.*tart_execution stored procedures.
• K. Create a SQL Server Agent job to execute the SSISDB.catalog.validate_project stored procedure.
• L. Deploy the .ispac file by using the Integration Services Deployment Wizard.
• M. Use an event handler for OnTaskFailed for the package.
• N. View the All Messages subsection of the All Executions report for the package.
• O. Use an event handler for OnError for the package.
• P. Store the System::ServerExecutionID variable in the custom log table.
• Q. Deploy the project by using dtutil.exe with the /COPY DTS option.
• R. Enable the SSIS log provider for SQL Server for OnError in the package control flow.