70-445 SQL 2005 Business Intelligence . PDF + Companion CD

from MSPRESS,
MCTS 70 – 445 , MS SQL Server 2005 Business Intelligence – Implementation and Maintenance
QUESTION 1
You develop a Windows-based application that interacts with a Microsoft SQL
Server database. The application inserts new rows into the database by calling the
following stored procedure. (Line numbers are included for reference only)
01 ALTER PROCEDURE dbo.sp_Update Certkiller Price
02 (
03 @category int,
04 @totalprice money OUTPUT
05 )
06 AS
07 SET NOCOUNT ON
08 UPDATE Products SET UnitPrice = UnitPrice * 1.1
WHERE CategoryID = @category
09 SELECT @totalprice = sum(UnitPrice) FROM Products
10 SELECT ProductName FROM Products
WHERE CategoryID = @category
11 RETURN @Totalprice
You application uses the ExecuteReader method of the SqlCommand object to call

Actualtests.org – The Power of Knowing
the stored procedure and create a SqlDataReader object. After the stored procedure
runts, your code must examine the SqlDataReader.RecordsAffected property to
verify that the correct number of records is successfully updated.
However, when you execute the stored procedure, the
SqlDataReader.RecordsAffected property always returns -1. How should you
correct this problem?
A. Change line 7 to
SET ROWCOUNT 0
B. Change line 7 to
SET NOCOUNT OFF
C. Change line 11 to
RETURN 0
D. Change line 11 to
RETURN @category
Answer: B
Explanation: SqlDataReader.RecordsAffected Property gets the number of rows
changed, inserted, or deleted by execution of the Transact-SQL statement.
The Transact-SQL SET NOCOUNT command stops the message indicating the number
of rows affected by a Transact-SQL statement from being returned as part of the results.
When SET NOCOUNT is ON, the count (indicating the number of rows affected by a
Transact-SQL statement) is not returned. When SET NOCOUNT is OFF, the count is
returned.
Reference:
SQL Server Books Online, SET NOCOUNT
.NET Framework Class Library, SqlDataReader.RecordsAffected Property [Visual
Basic]
Incorrect Answers
A: The SET NOCOUNT 0 is incorrect. We should use OFF instead of =.
C, D: We cannot change the functionality of the stored procedure by changing the
RETURN statement. Furthermore, returning = or the value of @Category value does not
achieve the desired result.
QUESTION 2
You develop an inventory management application called Certkiller Management
that will call a Microsoft SQL Server stored procedure named
sp_GetDaily Certkiller Sales. The stored procedure will run a query that returns your
daily sales total as an output parameter.
This total will be displayed to users in a message box.
Your application uses a SqlCommand object to run sp_GetDaily Certkiller Sales. You
write the following code to call sp_GetDaily Certkiller Sales:
sql Connection cnn = new sqlConnection (myConnString);
cmd.CommandType = CommandType.StoredProcedure;

Donwload Free PassGuide Braindumps-The Most Realistic Practice Questions and Answers,Help You Pass any Exams

Actualtests.org – The Power of Knowing
SqlParameter prm = cmd.Parameters.Add(“@ItemTotal”,
SqlDbType.Int);
prm.Direction = ParameterDirection.Output;
cnn.Open();
cmd.ExecuteNonQuery();
Now you must write additional code to access the output parameter. Which code segment should you
use?
A. MessageBox.Show(“Total is: ” +
cmd.Parameters["@Output"].Value.ToString());
B. MessageBox.Show(Total is: ” +
cmd.Parameters["@Output"].ToString());
C. MessageBox.Show(“Total is: ” +
cmd.Parameters["@ItemTotal"].Value.ToString());
D. MessageBox.Show(“Total is: ” +
cmd.Parameters["@ItemTotal"].ToString());
Answer: C
Explanation: The @ItemTotal parameter is declared as an output parameter with
SQL Server data type INT. We use the Value property of the SQLParameter class
to retrieve the value of this parameter. We must also convert the INT value to a
string value with the ToString method. We then supply this string to the
MessageBox.Show method.
Reference:
.NET Framework Class Library, SqlParameter Class [C#]
.NET Framework Class Library, SqlParameter.Direction Property [C#]
.NET Framework Class Library, SqlParameter.SqlDbType Property [C#]
.NET Framework Class Library, SqlParameter.Value Property [C#]
Incorrect Answers
A, B: The @ItemTotal parameter is the output parameter. Using @Output this way is
incorrect. Output is a keyword and no variable named @Output has been declared.
D: We must use the Value method to retrieve the value of the parameter.
QUESTION 3
You develop an enterprise application, called Certkiller Application that includes a
Windows Form presentation layer, middle-tier components for business logic and
data access, and a Microsoft SQL Server database.
You are in the process of creating a middle-tier component that will execute the
data access routines in your application. When data is passed to this component, the
component will call several SQL Server stored procedures to perform database
updates. All of these procedure calls run under the control of a single transaction.
The code for the middle-tier component will implement the following objects:
SqlConnection cn = new SqlConnection();
SqlTransaction tr;

Actualtests.org – The Power of Knowing
If two users try to update the same data concurrently, inconsistencies such as
phantom reads will occur. You must now add code to your component to specify the
highest possible level of protection against such inconsistencies.
Which code segment should you use?
A. tr = cn.BeginTransaction(“ReadCommitted”);
B. tr = cn.BeginTransaction(IsolationLevel.ReadCommitted);
C. tr = cn.BeginTransaction(IsolationLevel.Serializable);
D. tr = cn.BeginTransaction(“Serializable”);
Answer: C
Explanation:
Serializable is the highest isolation transaction level. It provide the highest possible
level of protection against concurrent data errors. The correct syntax to begin a
transaction with this transaction isolation level is:
cn.BeginTransaction(IsolationLevel.Serializable)
Reference:
.NET Framework Class Library, SqlConnection.BeginTransaction Method
(IsolationLevel) [C#]
.NET Framework Class Library, IsolationLevel Enumeration [C#]
Incorrect Answers
A: Incorrect syntax.
B: The ReadCommitted transaction isolation level can result in in non-repeatable reads or
phantom data. It does not give the highest possible protection from parallel updates.
D: Incorrect syntax.

the PDF is here :

http://rapidshare.com/files/144532860/70-445_Microsoft_Self_Paced_Training_Kit.pdf

and this is the companion CD :

http://rapidshare.com/files/143841643/445cd.rar

Free download:pass4sure Microsoft 70-445
Free download:testking Microsoft 70-445

password:www.certbible.org

High quality IT Certification Training Exam Questions, Study Guides and Practice Tests are in Downloadable PassGuide Testing Engine,Successful for IT Certification or Full Refund for you.Contact Us:Sales@PassGuide.com

Type	Exam Bible	New Questions & Answers	Latest Updated	Download link
PDF	All Certbible 's Exam Dumps	597	1 days ago	Available

PassGuide Training Materials & Practice Tests

• testking Microsoft Ts Exams Dumps
• testking Microsoft TS 70-656
• testking Microsoft TS 70-502(VB)
• testking Microsoft TS 70-502(CSharp)
• testking Microsoft TS 70-504(VB)
• testking Microsoft TS 70-504(CSharp)
• testking Microsoft TS 70-571
• testking Microsoft TS 70-503(CSharp)
• testking Microsoft TS 70-503(VB)
• testking Microsoft TS 70-642