Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

Exam 70-761 Chapter 3 Skill 3.2

memorize.ai (lvl 286)

(0)

37 cards

()

Section 1

Preview this deck

How do you set a LOCK_TIMEOUT to infinity?

Front

1 / 37

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (37)

Section 1

(37 cards)

How do you set a LOCK_TIMEOUT to infinity?

Front

LOCK_TIMEOUT -1

Back

What happens if you use the THROW command in a CATCH block?

Front

The exception bubbles up the stack and activates the next CATCH block or treminates the execution if none is found.

Back

What's the range of ERROR_STATE?

Front

1 to 255

Back

What happens if you have a missmatching number of BEGIN and COMMIT statements?

Front

You get a Msg 266 Error. The error doesn't stop the execution though even if you set XACT_ABORT

Back

What is GO for?

Front

GO signals the end of a batch of Transact-SQL statements to the SQL Server utilities. GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

Back

Which form of the THROW command can you also use outside a CATCH block?

Front

THROW with parameters.

Back

What could you do to make sure that if you commit a transaction in your stored procedure, that no outer query can rollback it?

Front

You could use a loop where you commit while @@ROWCOUNT > 0. It's considered bad practice though.

Back

How do you set a savepoint you can rollback to? An how do you rollback to it?

Front

SAVE TRANSACTION <savepoint name> ROLLBACK TRANSACTION <savepoint name>

Back

Say you have the query SELECT TOP(0) FROM dbo.ErrorLog* How do you use this query with an exclusive table lock on dbo.ErrorLog?

Front

SELECT TOP(0) * FROM dbo.ErrorLog WITH (TABLOCKX)

Back

What is the problem with the following part of a CATCH block? IF @@TRANCOUNT > 0 ROLLBACK TRAN THROW;

Front

Because ROLLBACK TRAN isn't terminated THROW isn't recognized as a command. It's rahter recognized as a savepoint or transaction name.

Back

Why don't you have the same problem with RAISERROR as with THROW when not terminating the previous statement with a ;

Front

Unlike THROW is RAISERROR not a reserved keyword

Back

What are three alternative writings for COMMIT TRANSACTION?

Front

COMMIT TRAN, COMMIT WORK or COMMIT

Back

What is a drawback of RAISERROR compared to THROW

Front

Errors lower than 20 doesn't terminate the batch, nor does it terminate or doom the transaction, irrespective to the XACT_ABORT option.

Back

What's the range of the third parameter of the THROW command?

Front

1 to 255

Back

transactional statements include DML, DDL and DCL (like GRANT or REVOKE) statements, what is not included in this and therefore are not undone with a rollback?

Front

Assigning values to variables as well as modufying datat in tablevariables.

Back

What is the LOCK_TIMEOUT and how do you set it to zero? How do you release it?

Front

It is the number of milliseconds that will pass before Microsoft SQL Server returns a locking error. SET LOCK_TIMEOUT 0; You release it with a COMMIT TRAN

Back

Give the correct order of the parameters when using THROW: message state errornumber

Front

THROW errornumber, message, state;

Back

Is it a good idea to use TRY CATCH and RAISERROR in a user defined function?

Front

No. none of them are premitted there.

Back

What is the problem with the following part of a CATCH block? SELECT 'This is an error message' THROW;

Front

THROW will not behave as intended. It is an alias here since the select is not terminated.

Back

THROW doesn't support specifying severity. What is the default then?

Front

Severity 16

Back

To use the WITH LOG command what do you have to know about the permission

Front

You have to be a member of the sysadmin role or have an ALTER TRACE permission.

Back

If XACT_ABORT is off, what happens to if you use THROW while an open transaction and you're not using a TRY CATCH. Is the transaction gonna be aborted or not?

Front

Not aborted and still commitable

Back

What is a disadvantage of the THROW to the RAISERROR command?

Front

THROW doesn't support WITH LOG or WITH NOWAIT

Back

What is autocommit?

Front

It means if you don't have a BEGIN TRANS and END TRANS you have autocommit. With autocommit each individual statement is considered a separate transaction.

Back

If you want to store logging data in a table from the CATCH block you could have a problem if the error sets the transaction in to a doomed state. How would you proceed to still achive this?

Front

1. Write your data in to a table variable (the only thing that isn't undone when a transaction rolls back) 2. Use a ROLLBACK TRAN loop to make sure you are not in a open but doomed connection. 3. Copy all the data from the variable in to the logger table.

Back

Where starts the first parameter of the THROW command at?

Front

50000

Back

What is the implicit transactions mode and how do you switch it on??

Front

It's similar to the autocommit mode. You don't have to open a transaction, this is done implicitly but you have to close it explicitly.

Back

What function helps you to find out wheter you are in a open transaction or not? And how is it's return value to be interpreted?

Front

@@TRANCOUNT. If the value is grater than zero you are in an open transaction.

Back

With XACT_STATE you can figure the state of a transaction. What is the returnvalue for what state?

Front

-1, transaction is doomed 0, no transaction is open 1, transaction is open and commitable

Back

What is the doomed state?

Front

A transaction in this state is not commitable anymore.

Back

What is important if you use RAISERROR with severity level 19 and up?

Front

You have to add the WITH LOG option.

Back

What return values from ERROR_SEVERITY are severe errors and not just informational?

Front

11 to 19

Back

Suppose you have a stored precedure that opens a transaction and within this transaction you alter data of a table. The transaction in the procedure is commited. The outer query though rollbacks the transaction. Is the altered data now permanent or not?

Front

No. SQL Server doesn't know real nesting of transaction. The last commit or rollback statement takes it all.

Back

What does SCOPE_IDENTITY()?

Front

Returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch

Back

From what severety will sql server terminate the connection?

Front

20

Back

What is a savepoint?

Front

It is possible to only rollback all the statements untill the savepoint.

Back

What result do you get, when you use a error function outside a CATCH block?

Front

You get back null.

Back