Exam 70-761 topic 1 question 16 discussion

Correct answers are: TRY...CATCH Raiseerror ... WITH LOG Passed exam today with 970 score and had it on my exam.

TRY..CATCH, RAISERROR WITH LOG - 100%

The answer is WITH NOWAIT. To raise error with severity 19 and above you have to add the option WITH LOG, which logs the error in the SQL Server error log as well as the Windows application log. To use the WITH LOG option you must be a member of the sysadmin role or have the ALTER TRACE permission. SQL Server Agent fires an alert when a specific error occurs. For example, you might specify error number 2571 to respond to unauthorized attempts to invoke Database Console Commands (DBCC). SQL Server Agent fires an alert when any error of the specific severity occurs. For example, you might specify a severity level of 15 to respond to syntax errors in Transact-SQL statements.

The 2 important requirements are that it be Severity 14 and a SQL Server Alert be triggered. For any severity other than 16, you have to use RAISERROR. A SQL Server Alert is an object setup under the SQL Server Agent. You can specify the Alert by error number or severity. The alert with trigger when an error is raised WITH LOG. So the option has to be RAISERROR....WITH LOG A SQL Server Alert is nothing to do with the client.

Continuing..So first you need to make an entry in sys.messages using a stored procedure sp_addmessage (you cannot just insert a value in system objects) "exec sp_addmessage 50555,14,N'the update failed'" 4. And now use RAISERROR so the answer is either 2 or 3 (most probably 3 because NOWAIT send the log message instantly to the user or the client) Happy learning :)

Okay there are couple of things this question sheds some light on - 1. The answer cannot be THROW because when you use THROW to throw an user defined error, the severity is always set to 16. 2.When THROW is used to throw and user defined function, the message_id doesn't need to be stored in sys.messages table.(Here the message id is 50555) 3.However when you use RAISERROR to throw an UD error message then it is mandatory to have a message_ID associated with the user defined test message in sys.messages. If you just raiserror with that message_id it will raise an error but the error message wouldn't have the user defined message becasue it doesnt have that particular message_id in sys.messages table. This is the error message that is thrown: "Error 50555, severity 14, state 1 was raised, but no message with that error number was found in sys.messages. If error is larger than 50000, make sure the user-defined message is added using sp_addmessage." Continued in next comment

The correct answer is Raiserror with nowait

xp_logevent can be used to log a user-defined message in the SQL Server log file and in the Windows Event Viewer. xp_logevent can be used to send an alert without sending a message to the client. Correct answer is WITH LOG: https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/xp-logevent-transact-sql?view=sql-server-ver15

It has to be raiserror to meet requirement of 'severity level must be 14.' since by default throw always return 16. Plus we need nowait option to return error to client immediately.

"A Microsoft SQL Server alert must be triggered when the error condition occurs." Seems to be correct answer "..With Nowait" Ref. Itzik Ben-Gan QUerying Microsoft Sql Server 2014" "You can issue RAISERROR with a severity level > 20 if you use the WITH LOG option and if you have the SQL Server sysadmin role. SQL Server will then terminate the connection when the error is raised. -You can use RAISERROR with NOWAIT to send messages immediately to the client. The message does not wait in the output buffer before being sent."