A transaction is a group of statements that are combined into a single logical unit. All statements succeed as a whole, or if any one of the statement fails, they all fail as a whole.
BEGIN TRAN --> Marks the starting point of a transaction.
SAVE TRAN Save_point --> Sets a new save point with in a transaction.
COMMIT TRAN --> Marks the end of a transaction and makes the changes permanent.
ROLLBACK [TRAN] [Save_point] --> Rolls back a transaction to the starting point or to the specific save point.
1. The following script if executed, what will be the output if you have only 200 Rupees?
IF Cash > 200
'Buy Fruits'
'Buy Snacks'
(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.
2. The following script if executed, what will be the output you have only 200 Rupees?
IF Cash > 200
BEGIN
'Buy Fruits'
'Buy Snacks'
END
(a) Buy Fruits, Buy Snacks.
(b) Buy Snacks.
(c) None of the statements will execute.
3. Which one of the following function returns the open transaction count?
(a) @@ERROR
(b) @@FETCH_STATUS
(c) @@TRANCOUNT
(d) @@ROWCOUNT
4. Consider using nested transactions, if any one transaction rollbacks what will happen to other transactions with in the nesting?
(a) All other transactions will be committed except the one which rollback.
(b) All the transactions will be rollback regardless of the nesting level.
(c) None of the transactions will execute.
(d) Creates open transactions.
5. An open transaction prevents others from viewing the data that is modified, which command can be used to make the modifications permanent?
(a) UPDATE TRAN
(b) DELETE TRAN
(c) SELECT TRAN
(d) COMMIT TRAN
6. When using a TRY…CATCH block, which function helps to find out the description of the error?
(a) ERROR_SEVERITY
(b) ERROR_STATE
(c) ERROR_MESSAGE
(d) ERROR_NUMBER
Show Answers:
1.B
ReplyDelete2.C
3.C
4.B
5.D
6.C
REAL WORLD EXAMPLE:
ReplyDeleteFrom: "Archana"
Subject: try-catch and @@error
Date: Monday, July 12, 2010 5:54 PM
hello,
i am having one query in sql server 2008 try catch block.
if i am using try-catch, then what is use of using @@error in try
block if any exception i can catch in catch block.
any help will be truely appreciated.
thanks in advance.
-------------------------------------
From: "Eric Isaacs"
Subject: Re: try-catch and @@error
Date: Monday, July 12, 2010 10:44 PM
@@Error is the old way to handle errors in SQL Server. With @@Error
you would have to capture that value into another local variable after
each operation and then check that value to see if it was non-zero.
With a Try-Catch block, you have more control and no longer need
@@Error. Instead, you should use ERROR_NUMBER().
From MSDN:
In the scope of a CATCH block, the following system functions can be
used to obtain information about the error that caused the CATCH block
to be executed:
* ERROR_NUMBER() returns the number of the error.
* ERROR_SEVERITY() returns the severity.
* ERROR_STATE() returns the error state number.
* ERROR_PROCEDURE() returns the name of the stored procedure
or trigger where the error occurred.
* ERROR_LINE() returns the line number inside the routine
that caused the error.
* 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.
Source: http://msdn.microsoft.com/en-us/library/ms175976.aspx
-Eric Isaacs