MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 - Database Development

Errata for MCTS Self-Paced Training Kit (Exam 70-433): Microsoft® SQL Server® 2008 - Database Development




The errata list is a list of errors and their corrections that were found after the product was released. If the error was corrected in a later version or reprint the date of the correction will be displayed in the column titled "Date Corrected".

The following errata were submitted by our customers and approved as valid errors by the author or editor.

Color Key: Serious Technical Mistake Minor Technical Mistake Language or formatting error Typo Question Note Update



Version Location Description Submitted By Date Submitted Date Corrected
Printed
Page CD-ROM

Answer option incorrect in practice test question In the practice test included on the Companion CD, question 433P_4.1_04 has an incorrect answer option. The question reads: "You have a table named Pets, which contains the following columns: petName, which stores the name of the pet ownerName, which stores the name of the pet's owner You also have a table named Owners, which contains the following columns: ownerName, which stores the name of the owner of one or more pets age, which stores the age of the owner location, which stores the name of the city in which the owner lives You must retrieve the pet's name, owner's name, and pet’s age for all pet owners over the age of 50 who live in Seattle. From the list on the right, select the portion of the statement you should use. Place your selections in the list on the left in the order in which they appear in the statement. Place your selections in the list on the left by clicking the items in the list on the right and clicking the arrow button. You can also use the up and down buttons to rearrange items in the list on the left. You may not need to use all the items from the list on the right." The answer option: "(SELECT age FROM Owners WHERE location = 'Seattle') AS own" Should read: "(SELECT ownerName, age FROM Owners WHERE location = 'Seattle') AS own"

Microsoft Press  Jul 13, 2010 
Printed
Page xx
Digital Content for Digital Book Readers box near the top of the page

Change: http://go.microsoft.com/fwlink/?LinkId=139187 To:http://go.microsoft.com/fwlink/?LinkID=146933"

Derrick Story
Derrick Story
O'Reilly AuthorO'Reilly Blogger 
May 06, 2010  Mar 09, 2012
Other Digital Version
CD-ROM
practice test included on the Companion CD, question 433P_2.3_03

The question reads: "Your company uses two tables to track current and past employees, named Employees and FormerEmployees. Both tables use the same columns: employeeID, name, department, and homePhone. You must create a Data Manipulation Language (DML) trigger to transfer any employees deleted from the Employees table into the FormerEmployees table." One answer possibility is missing: "(employeeID, name, department, homePhone)" One answer possibility is incorrect. Change: "SELECT" To: "SELECT (employeeID, name, department, homePhone)" The correct order of answers is: "CREATE TRIGGER TR_moveEmployee ON Employees AFTER DELETE AS INSERT INTO FormerEmployees (employeeID, name, department, homePhone) SELECT (employeeID, name, department, homePhone) FROM deleted"

Derrick Story
Derrick Story
O'Reilly AuthorO'Reilly Blogger 
May 06, 2010 
Printed, PDF
Page xx
Sample Database instructions

The process for attaching the databases fails for AdventureWorks2008. As documented by several users at MS forums and elsewhere, an error message indicates something is attempting to open the Documents folder as a file. A workaround is to download and install the databases from AdventureWorks2008R2_RTM.exe at http://msftdbprodsamples.codeplex.com/releases/view/45907.

Note from the Author or Editor:
A new version of the AdventureWorks2008 familiy of databases now exists under the title SQL Server 2008 SR4 on Codeplex. The install file can be downloaded from http://msftdbprodsamples.codeplex.com/releases/view/37109 . Please review the database prerequisites located at http://msftdbprodsamples.codeplex.com/wikipage?title=Database%20Prerequisites carefully.

Mike Brumley  Sep 18, 2010 
Printed, PDF
Page 14
1st paragraph

A FULL OUTER JOIN provides the same result as a LEFT OUTER JOIN with the table where the foreign key is defined on the left side of the JOIN keyword Should read: A FULL OUTER JOIN provides the same result as a LEFT OUTER JOIN with the table where the PRIMARY key is defined on the left side of the JOIN keyword ================== And, In addition, when foreign key constraits are defined, an OUTER JOIN defined with the primary key table being defined as the outer table provides the same results as an INNER JOIN. Should read: In addition, when foreign key constraits are defined, an OUTER JOIN defined with the foreign key table being defined as the outer table provides the same results as an INNER JOIN.

Note from the Author or Editor:
Although the correction listed above is not valid, the wording for the two sentences referenced still need to be changed to be 100% correct. Error is actually on page 14 and is in the paragraph following FIGURE 1-5. Detailed description of error: If there are foreign key constraints on the join column, a FULL OUTER JOIN provides the same result set as a LEFT OUTER JOIN with the table where the foreign key is defined on the left side of the JOIN keyword. This is because every value in the foreign key column must have a matching row in the primary key column for the constraint to be satisfied. In addition, when foreign key constraints are defined, an OUTER JOIN defined with the primary key table being defined as the outer table provides the same results as an INNER JOIN. Should read: If the join column includes a Foreign Key/Primary Key relationship and NULL values are not allowed in the Foreign Key column, a FULL OUTER JOIN provides the same result set as a LEFT OUTER JOIN when the table where the foreign key is defined is on the left side of the JOIN keyword. This is because every value in the foreign key column must have a matching row in the primary key column for the constraint to be satisfied. In addition, when foreign key constraints are defined and do not allow NULL values, an OUTER JOIN defined with the primary key table being defined as the outer table provides the same results as an INNER JOIN.

Gurjeet Singh  Feb 14, 2012  Mar 09, 2012
Printed
Page 20

"across all subcategories" should be "without a ProductSubcategoryID" On page 20, the last sentence of the "Using the GROUP BY Clause" section is incorrect. Change: "The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum, and maximum list prices of products across all subcategories." To: "The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum, and maximum list prices of products without a ProductSubcategoryID."

Microsoft Press  Jul 13, 2010  Mar 09, 2012
PDF
Page 44
By the end of the page

You said: "The result set for the preceding query is shown in Figure 1-6. The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum, and maximum list prices of products across all subcategories." Not only you can see, at a glance, that the proposed minimum is bigger than other minima in the Figure (and the proposed maximum smaller than other maxima) but also is not true that the query provides a summary if you don't use with rollup or with cube.

Note from the Author or Editor:
Good catch. The error is actually on page 20, not page 44. The sentence "The result set for the preceding query is shown in Figure 1-6. The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum, and maximum list prices of products across all subcategories." should be replaced with: ""The result set for the preceding query is shown in Figure 1-6. The top row, where the ProductSubcategoryID is listed as NULL, contains the information for products without a ProductSubcategoryID (NULL)."

Guillermo Guberman  Oct 10, 2011  Mar 09, 2012
Printed
Page 202
third sentence of the 'Subqueries Without Correlation to the Outer Query' section

Change: The following example uses a subquery to return all products that are cheaper than the average product price. To: The following example uses a subquery to return all products that are more expensive than the average product price.

Derrick Story
Derrick Story
O'Reilly AuthorO'Reilly Blogger 
May 06, 2010  Mar 09, 2012
Printed
Page 330

Code sample is missing On page 330, the second sentence of the first paragraph refers to code that is not printed in the book. Change: "The following code defines a geography type variable and sets the variable to a line between two points." To: "You can define a geography type variable and set the variable to a line between two points."

Microsoft Press  Jul 13, 2010  Mar 09, 2012
Printed
Page 331
step 5

On page 331, step 5 refers to a sample file named Airport.xls, which is not available. This section of the practice should be disregarded.

Microsoft Press  May 06, 2010 
Printed, PDF
Page 342
NOTE VIEWING AVAILABLE LANGUAGES

On page 342, in the note titled VIEW AVAILABLE LANGUAGES, the specified query retuns an error when executed. Change: SELECT * FROM sys.full-text_languages ORDER BY lcid To: SELECT * FROM sys.fulltext_languages ORDER BY lcid

Note from the Author or Editor:
This correction should be implemented as it reads. Detailed description of error: On page 342, in the note titled VIEW AVAILABLE LANGUAGES, the specified query retuns an error when executed. Change: SELECT * FROM sys.full-text_languages ORDER BY lcid To: SELECT * FROM sys.fulltext_languages ORDER BY lcid

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 361
2nd sentence of AUTHORIZATION argument description

On page 361, in the second sentence of the AUTHORIZATION argument description, "message type" should be "contract". Change: "If you are logged on as an account that does not have sa, dbo permissions when you create the message type, you must define your own user account, a role that you belong to, or a user account to which you have the impersonate permission." To: "If you are logged on as an account that does not have sa, dbo permissions when you create the contract, you must define your own user account, a role that you belong to, or a user account to which you have the impersonate permission."

Note from the Author or Editor:
This is correct. Detailed description of error: On page 361, in the second sentence of the AUTHORIZATION argument description, "message type" should be "contract". Change: "If you are logged on as an account that does not have sa, dbo permissions when you create the message type, you must define your own user account, a role that you belong to, or a user account to which you have the impersonate permission." To: "If you are logged on as an account that does not have sa, dbo permissions when you create the contract, you must define your own user account, a role that you belong to, or a user account to which you have the impersonate permission."

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 366
last script of exercise

On page 366, in the last script of the exercise, the "NewNoticeContract" contract should be specified for both services. Change: CREATE QUEUE AWNewNoticeQueue; CREATE QUEUE AWAckQueue; CREATE SERVICE AWNewNoticeService ON QUEUE AWNewNoticeQueue; CREATE SERVICE AWAckService ON QUEUE AWAckQueue; To: CREATE QUEUE AWNewNoticeQueue; CREATE QUEUE AWAckQueue; CREATE SERVICE AWNewNoticeService ON QUEUE AWNewNoticeQueue (NewNoticeContract); CREATE SERVICE AWAckService ON QUEUE AWAckQueue (NewNoticeContract);

Note from the Author or Editor:
Detailed description of error: On page 366, in the last script of the exercise, the "NewNoticeContract" contract should be specified for both services. Change: CREATE QUEUE AWNewNoticeQueue; CREATE QUEUE AWAckQueue; CREATE SERVICE AWNewNoticeService ON QUEUE AWNewNoticeQueue; CREATE SERVICE AWAckService ON QUEUE AWAckQueue; To: CREATE QUEUE AWNewNoticeQueue; CREATE QUEUE AWAckQueue; CREATE SERVICE AWNewNoticeService ON QUEUE AWNewNoticeQueue (NewNoticeContract); CREATE SERVICE AWAckService ON QUEUE AWAckQueue (NewNoticeContract);

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 366
Last two steps of the exercise

On page 366, the last two steps of the exercise are numbered incorrectly. Change the numbers of the last two steps to 4 and 5.

Note from the Author or Editor:
On page 366, the last two steps of the exercise are numbered incorrectly. Change the numbers of the last two steps to 4 and 5.

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 367
end of exercise

On page 367, at the end of the exercise, an additional step should be inserted after step 5 to adhere to best practices when ending a Service Broker conversation. Both participants in a conversation must call END CONVERSATION for the conversation to complete. The additional step should read as follows: 6. In the current query window, below the existing text, type, highlight, and execute the following commands to receive the EndDialog message that was sent to the AWNewNoticeQueue queue and to complete the conversation: DECLARE @dialog_handle UNIQUEIDENTIFIER; RECEIVE TOP (1) @dialog_handle = conversation_handle FROM AWNewNoticeQueue; END CONVERSATION @dialog_handle;

Note from the Author or Editor:
Correct Change as noted above.

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 367
step 5 of exercise

On page 367, in step 5 of the exercise, "AWNewNoticeQueue" should be "AWAckQueue". Change: 5. Execute the SELECT * FROM AWNewNoticeQueue statement again to verify that the row is no longer in the queue. To: 5. Execute the SELECT * FROM AWAckQueue statement again to verify that the row is no longer in the queue.

Note from the Author or Editor:
Correct Change as noted above.

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 367
step 3 of the exercise

On page 367, in step 3 of the exercise, "AWNewNoticeQueue" should be "AWAckQueue". Change: 3. Open a new query window, and type and execute the following command to view the AWNewNoticeQueue queue: SELECT * FROM AWNewNoticeQueue; To: 3. Open a new query window, and type and execute the following command to view the AWAckQueue queue: SELECT * FROM AWAckQueue;

Note from the Author or Editor:
Correct Change as noted above.

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 367
script in step 4 of exercise

On page 367, in step 4 of the exercise, "AWNewNoticeQueue" should be "AWAckQueue" and the "@XMLdata" variable is not set correctly. Change: SELECT * FROM AWNewNoticeQueue; DECLARE @dialog_handle UNIQUEIDENTIFIER , @XMLdata XML; SET XMLdata = (SELECT * FROM sys.tables FOR XML AUTO); RECEIVE TOP (1) @dialog_handle = conversation_handle FROM AWNewNoticeQueue; END CONVERSATION @dialog_handle; SELECT @XMLData; To: SELECT * FROM AWAckQueue; DECLARE @dialog_handle UNIQUEIDENTIFIER , @XMLdata XML; RECEIVE TOP (1) @dialog_handle = conversation_handle , @XMLdata = message_body FROM AWAckQueue; END CONVERSATION @dialog_handle; SELECT @XMLdata;

Note from the Author or Editor:
Correct Change as noted above.

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 367
script in step 2 of exercise

On page 367, in the script in step 2 of the exercise, "@XMLdata" must be specified as the body of the message. Change: SEND ON CONVERSATION @dialog_handle MESSAGE TYPE AWNewNotice; To: SEND ON CONVERSATION @dialog_handle MESSAGE TYPE AWNewNotice (@XMLdata);

Note from the Author or Editor:
This is correct. Detailed description of error: On page 367, in the script in step 2 of the exercise, "@XMLdata" must be specified as the body of the message. Change: SEND ON CONVERSATION @dialog_handle MESSAGE TYPE AWNewNotice; To: SEND ON CONVERSATION @dialog_handle MESSAGE TYPE AWNewNotice (@XMLdata);

Anonymous  Sep 28, 2010  Mar 09, 2012
Printed, PDF
Page 411
to_lsn argument description, 2nd sentence

On page 411, in the second sentence of the to_lsn argument description, "from_lsn" should be "to_lsn". Change: "All rows in the change table with an LSN value less than or equal to the from_lsn value are included in the result set." To: "All rows in the change table with an LSN value less than or equal to the to_lsn value are included in the result set."

Note from the Author or Editor:
On page 411, in the second sentence of the to_lsn argument description, "from_lsn" should be "to_lsn". Change: "All rows in the change table with an LSN value less than or equal to the from_lsn value are included in the result set." To: "All rows in the change table with an LSN value less than or equal to the to_lsn value are included in the result set."

Anonymous  Sep 29, 2010  Mar 09, 2012
Printed, PDF
Page 413
to_lsn argument description, 2nd sentence

On page 413, in the second sentence of the to_lsn argument description, "from_lsn" should be "to_lsn". Change: "All rows in the change table with an LSN value less then or equal to the from_lsn value are included in the result set." To: "All rows in the change table with an LSN value less then or equal to the to_lsn value are included in the result set."

Note from the Author or Editor:
This should be changed as suggested. Change: "All rows in the change table with an LSN value less then or equal to the from_lsn value are included in the result set." To: "All rows in the change table with an LSN value less then or equal to the to_lsn value are included in the result set."

Anonymous  Sep 29, 2010  Mar 09, 2012
Printed, PDF
Page 415
sys.fn_cdc_get_min_lsn function description, 1st sentence

On page 415, in the first sentence of the sys.fn_cdc_get_min_lsn function description, "maximum" should be "minimum". Change: "Returns the maximum LSN that exists in the start_lsn column of the cdc.lsn_time_mapping system table." To: "Returns the minimum LSN that exists in the start_lsn column of the cdc.lsn_time_mapping system table."

Note from the Author or Editor:
This is correct. Change: "Returns the maximum LSN that exists in the start_lsn column of the cdc.lsn_time_mapping system table." To: "Returns the minimum LSN that exists in the start_lsn column of the cdc.lsn_time_mapping system table."

Anonymous  Sep 29, 2010  Mar 09, 2012
Printed, PDF
Page 419
Step 4 of exercise

On page 419, in step 4 of the exercise, "AdventureWorksDW2008 database" should be "ProspectiveBuyer table". Change: "In the existing query window, below the existing code, type, highlight, and execute the following code to enable change tracking on the AdventureWorksDW2008 database:" To: "In the existing query window, below the existing code, type, highlight, and execute the following code to enable change tracking on the ProspectiveBuyer table:"

Note from the Author or Editor:
Change: "In the existing query window, below the existing code, type, highlight, and execute the following code to enable change tracking on the AdventureWorksDW2008 database:" To: "In the existing query window, below the existing code, type, highlight, and execute the following code to enable change tracking on the ProspectiveBuyer table in the AdventureWorksDW2008 database:"

Anonymous  Sep 29, 2010  Mar 09, 2012
Printed, PDF
Page 423
Last comment in script

On page 423, in the last comment of the T-SQL script, "the net" should be "all". Change: -- Return the net changes occurring within the query window. To: -- Return all changes occurring within the query window.

Note from the Author or Editor:
I believe this error should be on page 422 not 423. In Step 5, the comment in the code previously read -- Return the net changes occurring within the query window. the comment in the code should read -- Return all changes occurring within the query window. Step 7 should still read "net" changes, not "all" changes.

Anonymous  Sep 29, 2010  Mar 09, 2012
Printed
Page 424

"sp_send_db mail" should be "sp_send_dbmail" On page 424, the first bullet under "Chapter Summary" contains an incorrect command. Change: "You can use the sp_send_db mail to integrate e-mail messages into your database applications." To: "You can use the sp_send_dbmail to integrate e-mail messages into your database applications." Microsoft Press is committed to providing informative and accurate books. All comments and corrections listed above are ready for inclusion in future printings of this book. If you have a later printing of this book, it may already contain most or all of the above corrections.

Microsoft Press  Jul 13, 2010  Mar 09, 2012
Printed, PDF
Page 430
Chapter 8 Case Scenaria 1 answer, fourth sentence

On page 430, the fourth sentence in the answer of Chapter 8 Case Scenario 1, "geometry" should be "geography". Change: "For the warehouse region functionality, you should use the geometry data type." To: "For the warehouse region functionality, you should use the geography data type."

Note from the Author or Editor:
Detailed description of error: On page 430, the fourth sentence in the answer of Chapter 8 Case Scenario 1, "geometry" should be "geography" when referring to the warehouse region functionality. Change: "For the warehouse region functionality, you should use the geometry data type." To: "For the warehouse region functionality, you should use the geography data type."

Anonymous  Sep 29, 2010  Mar 09, 2012