SQL Server 2005 Mainstream support Ended

The Microsoft SQL Server 2005 Mainstream support has ended by 12-April-2011. Therfore the certification exams associated with the product will also be expired. So please note that all the Microsoft SQL Server 2005 exams are scheduled to retire on June 30, 2011.

Choose from the following three MCITP certifications on SQL Server 2005.

MCITP certification	Prerequisite MCTS certification	Exam(s)
MCITP: Database Administrator	MCTS: SQL Server 2005	Exam 70-443:* PRO: Designing a Database Server Infrastructure by Using Microsoft SQL Server 2005 and Exam 70-444:* PRO: Optimizing and Maintaining a Database Administration Solution by Using Microsoft SQL Server 2005
MCITP: Database Developer	MCTS: SQL Server 2005	Exam 70-441:* PRO: Designing Database Solutions by Using Microsoft SQL Server 2005 and Exam 70-442:* PRO: Designing and Optimizing Data Access by Using Microsoft SQL Server 2005
MCITP: Business Intelligence Developer	MCTS: SQL Server 2005, Business Intelligence Development and Maintenance	Exam 70-446:* PRO: Designing a Business Intelligence Solution by Using Microsoft SQL Server 2005

*This exam is scheduled to retire on June 30, 2011.

Buy a Microsoft Certification Pack with Second Shots and save up to 20%

Microsoft Certification Exam Packs provide large discounts plus a Second Shot for every exam in the pack. The Second Shot offer allows you to retake an exam if you do not pass it the first time.

New: If you obtained your Exam Pack voucher code before January 1, 2011, you have until June 30, 2011, to complete your exams and any retakes. If you obtain your Exam Pack voucher code on or after January 1, 2011, you have until June 30, 2011, to schedule and take your first exam. 

You can find the benefits of doing certification from Microsoft’s web site. You can find the salary ranges in INR by job for employees with MCTS – SQL Server 2005 certification.

Those who have are willing to have SQL Server 2005 certification should consider to take the exam before 30-June-2011 followed by a upgrade exam for SQL Server 2008.

Error Handling in SQL Server 2005

TRY…CATCH construct is used for error handling in side T-SQL code. A TRY…CATCH construct consists of 2 blocks. One is TRY block that is immediately followed by a CATCH block. TRY…CATCH construct can be nested. When an error condition is identified in TRY block, the control is immediately transferred to CATCH block where the error can be handled.

The following code represents a basic TRY…CATCH construct.

BEGIN TRY

SELECT 1/0;

END TRY

BEGIN CATCH

      SELECT ERROR_MESSAGE();

END CATCH

Error Functions:

The following error functions can be used inside a TRY…CATCH construct to get the information about the error that is occurred.

ERROR_NUMBER() returns the error number.

ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters such as lengths, object names, or times.

ERROR_SEVERITY() returns the error severity.

ERROR_STATE() returns the error state number.

ERROR_LINE() returns the line number inside the routine that caused the error.

ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.

TRY…CATCH with RAISERROR

RAISERROR can be used inside a TRY…CATCH construct for error handling. RAISERROR generates error message using user defined messages that is stored inside sys.messages catalog view or build a message dynamically.

The following code returns an error message and the WITH LOG statement causes the error to be logged into windows application log. Using event viewer, you can view the log.

BEGIN TRY

    RAISERROR ('Error raised in TRY block.', 16,10) WITH LOG;

END TRY

BEGIN CATCH

    SELECT Error_Message() as Message_text,

               Error_Severity() as Severity,

               Error_State() as State

END CATCH

Questions:

1. What types of error cannot be handled by TRY…CATCH construct. Choose all correct answers from the following:

a. All DDL statement errors

b. All DML statement errors

c. Divide by Zero error

d. Syntax errors that prevent a batch from executing.

Answers:

1. d.