Exam 70-761 topic 1 question 106 discussion

Actual exam question from Microsoft's 70-761

Question #: 106
Topic #: 1

HOTSPOT -
You need to develop a Transact-SQL statement that meets the following requirements:
✑ The statement must return a custom error when there are problems updating a table.
✑ The error number must be the value 50555.
✑ The error severity level must be 14.
✑ A Microsoft SQL Server alert must be triggered when the error condition occurs.
Which Transact-SQL segment should you use for each requirement? To answer, select the appropriate Transact-SQL segments in the answer area.
Hot Area:

Show Suggested Answer

Suggested Answer:

RAISERROR generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated
CATCH block of a TRY"¦CATCH construct. New applications should use THROW instead.
Note: RAISERROR syntax:
RAISERROR( { msg_id | msg_str | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ]
The LOG option logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine.
References:
https://msdn.microsoft.com/en-us/library/ms178592.aspx

by Bartek at Oct. 11, 2019, 12:50 p.m.

Comments

Submit Cancel

Angelcr

4 years, 5 months ago

The answer is correct: LOG: Logs the error in the error log and the application log for the instance of the Microsoft SQL Server Database Engine. https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2016

upvoted 1 times

...

Vermonster

4 years, 5 months ago

The answer to the 2nd part is correct, assuming the error number added to sys.messages with sp_addmessage. The first option is interchangeable though - no requirements on that

upvoted 1 times

...

HA2020

4 years, 7 months ago

Answer: The syntax used for raiserror is wrong. From Microsoft doc: https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017 When msg_str is specified, RAISERROR raises an error message with an error number of 50000. Severity levels from 0 through 18 can be specified by any user. Severity levels from 19 through 25: WITH LOG option is required. Only member of sysadmin fixed server role or user with ALTER TRACE permissions can specify WITH LOG. Syntax: RAISERROR ( { msg_id | msg_str | @local_variable } { ,severity ,state }[ ,argument [ ,...n ] ] )[ WITH option [ ,...n ] ] Ex. RAISERROR (N'This is message %s %d.', -- Message text. 10, -- Severity, 1, -- State, N'number', -- First argument. 5); -- Second argument. -- The message text returned is: This is message number 5.

upvoted 2 times

...

mattia_88

5 years, 8 months ago

is correct if before is added the message with code 50555 EXEC sp_addmessage 50555, 14, N'%s';

upvoted 2 times

...

Bartek

5 years, 8 months ago

Its strange that we can use both error id and message in RAISERROR. In addition RAISERROR cannot see eerror id > 50000 if there is not permanently written to sys.messages https://sqlhints.com/tag/raiserror-vs-throw/

upvoted 1 times

...