Interview Questions ASP.NET VIEW STATE

1). What is View State in Asp.net?
Ans: View state is nothing but a method that the ASP.NET use to preserve page and control values between postbacks. When the HTML markup for the page is rendered, the current state of the page and values that must be retained during postback are serialized into base64-encoded strings. This information is then put into the view state hidden field.

2). View state is client-side or server side state management techenique?
Ans: View state is client-side state management techenique

3). What are the client-side state management techenique supported by ASP.NET?
Ans: View state
Control state
Hidden fields
Cookies
Query strings

4). View state is used by Asp.net page atomatically or we need to apply it manuly?
Ans: View state is used automatically by the ASP.NET page framework to persist information that must be preserved between postbacks.


5). When you can use(take advantage of vs) view state?
or What you can do by use view state?
Ans: a) Keep values between postbacks without storing them in session state or in a user profile.
b) Store the values of page or control properties that you define.
c) Create a custom view state provider that lets you store view state information in a SQL Server database or in another data store.

6). What are the advantages of using view state?
Ans: No server resources are required : The view state is contained in a structure within the page code.
Simple implementation : View state does not require any custom programming to use. It is on by default to maintain state data on controls.
Enhanced security features : The values in view state are hashed, compressed, and encoded for Unicode implementations, which provides more security than using hidden fields.

7). What are the limitations of view state?
Ans: Limitations:
Because view state is stored in the page, it results in a larger total page size.
ASP.NET uses view state only with page and control properties.
View state isn't a good place to store sensitive information that the client shouldn't be allowed to see.

Read More »

Interview Questions ASP.NET State Management Techniques

Here are the top 10 questions answers for both experienced and beginners asp.net developers.

1).What is state management?
Ans: State management is the process by which you maintain state and page information over multiple requests for the same or different pages.

2).Http is stateless, What does this mean?
Ans: Stateless protocol is a communications protocol that treats each request as an independent transaction that is unrelated to any previous request so that the communication consists of independent pairs of requests and responses.

3).What is Session?
Ans: We know that Http is stateless, means when we open a webpage and fill some information and then move to next page then the data which we have entered will lost.
It happed do to Http protocol stateless nature. So here session come into existence, Session provide us the way of storing data in server memory. So you can store your page data into server
memory and retrieve it back during page postbacks.

4).What are the Advantage and disadvantage of Session?
Ans: Advantages:
Session provide us the way of maintain user state/data.
It is very easy to implement.
One big advantage of session is that we can store any kind of object in it. :eg, datatabe, dataset.. etc
By using session we don't need to worry about data collesp, because it store every client data separately.
Session is secure and transparent from the user.
Disadvantages:
Performance overhead in case of large volumes of data/user, because session data is stored in server memory.
Overhead involved in serializing and de-serializing session data, because in the case of StateServer and SQLServer session modes, we need to serialize the objects before storing them.

5).What is Session ID in Asp.net?
Ans: Asp.Net use 120 bit identifier to track each session. This is secure enough and can't be reverse engineered. When client communicate with server, only session id is transmitted, between them. When client request for data, ASP.NET looks on to session ID and retrieves corresponding data.

6).By default where the sessions ID's are stored ?
Ans: By default, the unique identifier for a session is stored in a non-expiring session cookie in the browser. You can specify that session identifiers not be stored in a cookie by setting the cookieless attribute to true in the sessionState configuration element.
We can also configure our application to store it in the url by specifying a "cookieless" session
The ASP Session cookie has this format:-
ASPSESSIONIDACSSDCCC=APHELKLDMNKNIOJONJACDHFN


7).Where does session stored if cookie is disabled on client’s machine?
Ans: If you want to disable the use of cookies in your ASP.NET application and still make use of session state, you can configure your application to store the session identifier in the URL instead of a cookie by setting the cookieless attribute of the sessionState configuration element to true, or to UseUri, in the Web.config file for your application.
The following code example shows a Web.config file that configures session state to use cookieless session identifiers.

Code:

<configuration>
  <system.web>
    <sessionState
      cookieless="true"
      regenerateExpiredSessionId="true"
      timeout="30" />
  </system.web>
</configuration>

8).Can you describe all the property set in web.config under session state?
Ans:

Code:

<configuration>
  <sessionstate
      mode="inproc"
      cookieless="false"
      timeout="20"
      sqlconnectionstring="data source=127.0.0.1;user id=<user id>;password=<password>"
      server="127.0.0.1"
      port="42424"
  />
</configuration>

Mode: The mode setting supports three options: inproc, sqlserver, and stateserver. As stated earlier, ASP.NET supports two modes: in process and out of process. There are also two options for out-of-process state management: memory based (stateserver), and SQL Server based (sqlserver). We'll discuss implementing these options shortly.
Cookieless: The cookieless option for ASP.NET is configured with this simple Boolean setting.
Timeout: This option controls the length of time a session is considered valid. The session timeout is a sliding value; on each request the timeout period is set to the current time plus the timeout value
Sqlconnectionstring: The sqlconnectionstring identifies the database connection string that names the database used for mode sqlserver.
Server: In the out-of-process mode stateserver, it names the server that is running the required Windows NT service: ASPState.
Port: The port setting, which accompanies the server setting, identifies the port number that corresponds to the server setting for mode stateserver.

9).What are Session Events?
Ans: There are two types of session events available in ASP.NET:
Session_Start
Session_End
You can handle both these events in the global.asax file of your web application. When a new session initiates, the session_start event is raised, and the Session_End event raised when a session is abandoned or expires.

10).How you can disable session?
Ans: If we set session Mode="off" in web.config, session will be disabled in the application. For this, we need to configure web.config the following way:

Code:

<configuration>
  <sessionstate  Mode="off"/>
</configuration>

Read More »

SQL Server interview questions | triggers all about triggers

Explain Triggers?

A trigger is a special type of event driven stored procedure. 

It gets initiated when Insert, Delete or Update event occurs. 

It can be used to maintain referential integrity. 

A trigger can call stored procedure.

Executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

You can specify which trigger fires first or fires last using sp_settriggerorder.

Triggers can't be invoked on demand.

They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens.

Triggers are generally used to implement business rules, auditing.

Triggers can also be used to extend the referential integrity checks

Trigger is one of the database objects and executes set of Transact SQL Statements Automatically in response to an event (INSERT, UPDATE,DELETE etc) with in database.
Generally Triggers are used to implement business rules.

How many triggers you can have on a table? A table can have up to 12 triggers defined on it.

How many types of triggers are there in Sql Server 2005?

There are two types of triggers
• Data Manipulation language (DML) triggers
• Data Definition language (DDL) triggers

DML triggers (implementation) will run when INSERT, UPDATE, or DELETE statements modify data in a specified table or view.

DDL triggers will run in response to DDL events that occur on the server such as creating, altering, or dropping an object, are used for database administration tasks

What are the different modes of firing triggers?

After Trigger: An AFTER trigger fires after SQL Server completes all actions successfully

Instead of Triggers: An INSTEAD OF trigger causes SQL Server to execute the code in the trigger instead of the operation that caused the trigger to fire.

Describe triggers features and limitations.

Trigger features:- 1. Can execute a batch of SQL code for an insert, update or delete command is executed 2. Business rules can be enforced on modification of data Trigger Limitations:- 1. Does not accept arguments or parameters 2. Cannot perform commit or rollback 3. Can cause table errors if poorly written What are the instances when triggers are appropriate? Answer When security is the top most priority. i.e. to allow unauthorized access When backups are essential When Maintenance is desired. Triggers can be fired when any error message is logged Keeping the database consistent. What is Nested Trigger? A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger. Syntax for viewing, dropping and disabling  triggers View trigger: A trigger can be viewed by using sp_helptrigger syntax. This returns all the triggers defined in a table. Sp_helptrigger table_name Drop a trigger DROP TRIGGER Trigger_name Disable a trigger:- DISABLE TRIGGER [schema name] trigger name ON [object, database or ALL server ]

Read More »

SQL Server interview questions | data integrity constraints

SQL Server interview questions | data integrity constraints

What is primary key?

A Primary Key in a table identifies each and every row uniquely.

It should not allow null values.

We could assign primary key on only column or more than one column also.

What is the difference between primary key and unique key?

Primary should not allow null; where as unique key will allow nulls.

By default Primary key is created as clustered index; whereas unique key is created as non clustered index.

What are the different levels of data integrity in SQL Server?

Entity Integrity, Domain Integrity, Referential integrity

Entity Integrity ensures that there are no duplicate rows in a table.

Domain Integrity enforces valid entries for a given column by restricting the type, the format, or the range of possible values.

Referential integrity ensures that rows cannot be deleted, which are used by other records.

Explain different constraints to maintain data integrity in SQL Server?

Check constraints: 
Check constraints will be useful to limit the range of possible values in a column.
We could create check constraints at two different levels

a) Column-level check constraints are applied only to the column and cannot reference data in another other column

b) Table-level check constraints can reference any column within a table but cannot reference columns in other tables

Default constraints:
Default constraints enable the SQL Server to write default value to a column when user doesn’t specify a value.

Unique constraints:
A unique constraint restricts a column or combination of columns from allowing duplicate values.

Primary key constraints:
Primary key constraints will allow a row to be uniquely identified. This will perform by primary key on the table.

Foreign key constraints:
Foreign keys constraints will ensure that the values that can be entered in a particular column exist in a specified table.

Read More »

SQL Integrity Constraints Foreign Key Not Null Unique Check

SQL Integrity Constraints

Integrity Constraints are used to apply business rules for the database tables.

Enforcing data integrity ensures the quality of the data in the database. 

For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value.

Data integrity falls into these categories:

Entity Integrity

Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

Domain Integrity

Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

Referential Integrity

Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft® SQL Server™ 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database.

User-Defined Integrity

User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).

The Constraints available in SQL are Foreign Key, Not Null, Unique, and Check.

Constraints can be defined in two ways

1) The constraints can be specified immediately after the column definition. This is called column-level definition.

2) The constraints can be specified after all the columns are defined. This is called table-level definition.

1) SQL Primary key:

This constraint defines a column or combination of columns which uniquely identifies each row in the table.

Syntax to define a Primary key at column level:

COLUMN name datatype [CONSTRAINT constraint_name] PRIMARY KEY

Syntax to define a Primary key at table level:

[CONSTRAINT constraint_name] PRIMARY KEY (column_name1,column_name2,..)

column_name1, column_name2 are the names of the columns which define the primary Key.

The syntax within the bracket i.e. [CONSTRAINT constraint_name] is optional.

For Example: To create an employee table with Primary Key constraint, the query would be like.

Primary Key at column level:

CREATE TABLE employee

( id INT PRIMARY KEY, 

name char(20),

dept char(10),

age INT,

salary INT,

location char(10)

);

or

CREATE TABLE employee

( id INT CONSTRAINT emp_id_pk PRIMARY KEY,

name char(20),

dept char(10),

age INT,

salary INT,

location char(10)

);

Primary Key at column level:

CREATE TABLE employee

( id INT,

name char(20),

dept char(10),

age INT,

salary INT,

location char(10)  ,

CONSTRAINT emp_id_pk PRIMARY KEY (id)

);

Primary Key at table level:

CREATE TABLE employee

(id INT NOT NULL,

name char(20),

dept char(10),

age INT,

salary INT,

location char(10)

);

ALTER TABLE employee ADD CONSTRAINT PK_EMPLOYEE_ID PRIMARY KEY (id)

2) SQL Foreign key or Referential Integrity:

This constraint identifies any column referencing the PRIMARY KEY in another table. It establishes a relationship between two columns in the same table or between different tables.

Syntax to define a Foreign key at column level:

[CONSTRAINT constraint_name] REFERENCES Referenced_Table_name(column_name)

Syntax to define a Foreign key at table level:

[CONSTRAINT constraint_name] FOREIGN KEY(column_name) REFERENCES referenced_table_name(column_name);

For Example:

1) Lets use the "product" table and "order_items".

CREATE TABLE product

( product_id INT CONSTRAINT pd_id_pk PRIMARY KEY,

product_name char(20),

supplier_name char(20),

unit_price INT);

CREATE TABLE order_items

( order_id INT CONSTRAINT od_id_pk PRIMARY KEY,

product_id INT CONSTRAINT pd_id_fk REFERENCES product(product_id),

product_name char(20),

supplier_name char(20),

unit_price INT

);

Foreign Key at table level:

CREATE TABLE order_items

( order_id INT,

product_id INT,

product_name char(20),

supplier_name char(20),

unit_price INT,

CONSTRAINT od_id_pk PRIMARY KEY(order_id),

CONSTRAINT pd_id_fk FOREIGN KEY(product_id) REFERENCES product(product_id)

);

2) If the employee table has a 'mgr_id' i.e, manager id as a foreign key which references primary key 'id' within the same table, the query would be like.

CREATE TABLE employee

(id INT PRIMARY KEY,

name char(20),

dept char(10),

age INT,

mgr_id INT REFERENCES employee(id),

salary INT,

location char(10)

);

3) SQL Not Null Constraint:

This constraint ensures all rows in the table contain a definite value for the column which is specified as not null. Which means a null value is not allowed.

Syntax to define a Not Null constraint:

[CONSTRAINT constraint name] NOT NULL

For Example: To create a employee table with Null value, the query would be like

CREATE TABLE employee

( id INT,

name char(20) CONSTRAINT nm_nn NOT NULL,

dept char(10),

age INT,

salary INT,

location char(10)

);

ALTER COLUMN DATA TYPE: -

ALTER TABLE employee ALTER COLUMN name char(20) NOT NULL

4) SQL Unique Key:

This constraint ensures that a column or a group of columns in each row have a distinct value. A column(s) can have a null value but the values cannot be duplicated.

Syntax to define a Unique key at column level:

[CONSTRAINT constraint_name] UNIQUE

Syntax to define a Unique key at table level:

[CONSTRAINT constraint_name] UNIQUE(column_name)

For Example: To create an employee table with Unique key, the query would be like,

Unique Key at column level:

CREATE TABLE employee

( id INT PRIMARY KEY,

name char(20),

dept char(10),

age INT,

salary INT,

location char(10) UNIQUE

);

Unique Key at table level:

CREATE TABLE employee

( id INT PRIMARY KEY,

name char(20),

dept char(10),

age INT,

salary INT,

location char(10) CONSTRAINT loc_un UNIQUE

);

5) SQL Check Constraint:

This constraint defines a business rule on a column. All the rows must satisfy this rule. The constraint can be applied for a single column or a group of columns.

The CHECK constraint is used to limit the value range that can be placed in a column.

Syntax to define a Check constraint:

[CONSTRAINT constraint_name] CHECK (condition)

For Example: In the employee table to select the gender of a person, the query would be like

Check Constraint at column level:

CREATE TABLE employee

( id INT PRIMARY KEY,

name char(20),

dept char(10),

age INT,

gender char(1) CHECK (gender in ('M','F')),

salary INT,

location char(10)

);

Check Constraint at table level:

CREATE TABLE employee

( id INT PRIMARY KEY,

name char(20),

dept char(10),

age INT,

gender char(1),

salary INT,

location char(10),

CONSTRAINT gender_ck CHECK (gender in ('M','F'))

);

To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:

CREATE TABLE Persons

(

P_Id int NOT NULL,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

)

SQL CHECK Constraint on ALTER TABLE

ALTER TABLE Persons

ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')

To DROP a CHECK Constraint

ALTER TABLE Persons

DROP CONSTRAINT chk_Person

Read More »

Exception Handling In SQL Server 2008 R2 (SQL Server Transactions and Error Handling)

Exception Handling In SQL Server 2008 R2 (SQL Server Transactions and Error Handling)

Errors in Transact-SQL code can be processed by using a TRY…CATCH construct similar to the exception-handling features of the Microsoft Visual C++ and Microsoft Visual C# languages. A TRY…CATCH construct consists of two parts: a TRY block and a CATCH block. When an error condition is detected in a Transact-SQL statement that is inside a TRY block, control is passed to a CATCH block where the error can be processed.

After the CATCH block handles the exception, control is then transferred to the first Transact-SQL statement that follows the END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is returned to the code that invoked the stored procedure or trigger. Transact-SQL statements in the TRY block following the statement that generates an error will not be executed.

If there are no errors inside the TRY block, control passes to the statement immediately after the associated END CATCH statement. If the END CATCH statement is the last statement in a stored procedure or trigger, control is passed to the statement that invoked the stored procedure or trigger.

A TRY block starts with the BEGIN TRY statement and ends with the END TRY statement. One or more Transact-SQL statements can be specified between the BEGIN TRY and END TRY statements.

A TRY block must be followed immediately by a CATCH block. A CATCH block starts with the BEGIN CATCH statement and ends with the END CATCH statement. In Transact-SQL, each TRY block is associated with only one CATCH block.

Transactions

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails, the transaction fails. Therefore, a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction.

Users can group two or more Transact-SQL statements into a single transaction using the following statements:

1. Begin Transaction

2. Rollback Transaction

3. Commit Transaction

ROLLBACK - If anything goes wrong with any of the grouped statements, all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology.

COMMIT - If everything is in order with all statements within a single transaction, all changes are recorded together in the database. In SQL Server terminology, we say that these changes are committed to the database.

Here is an example of a transaction:

USE example

DECLARE @intErrorCode INT

BEGIN TRAN

    UPDATE Authors

    SET Phone = '415 354-9866'

    WHERE au_id = '724-80-9391'

    SELECT @intErrorCode = @@ERROR

    IF (@intErrorCode <> 0) GOTO PROBLEM

    UPDATE Publishers

    SET city = 'Calcutta', country = 'India'

    WHERE pub_id = '9999'

    SELECT @intErrorCode = @@ERROR

    IF (@intErrorCode <> 0) GOTO PROBLEM

COMMIT TRAN

PROBLEM:

IF (@intErrorCode <> 0) BEGIN

PRINT 'Unexpected error occurred!'

    ROLLBACK TRAN

END

Before the real processing starts, the BEGIN TRAN statement notifies SQL Server to treat all of the following actions as a single transaction. It is followed by two UPDATE statements. If no errors occur during the updates, all changes are committed to the database when SQL Server processes the COMMIT TRAN statement, and finally the stored procedure finishes. If an error occurs during the updates, it is detected by if statements and execution is continued from the PROBLEM label. After displaying a message to the user, SQL Server rolls back any changes that occurred during processing. Note: Be sure to match BEGIN TRAN with either COMMIT or ROLLBACK.

Nested Transactions

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.

A COMMIT issued against any transaction except the outermost one doesn't commit any changes to disk - it merely decrements the@@TRANCOUNT automatic variable. A ROLLBACK, on the other hand, works regardless of the level at which it is issued, but rolls back all transactions, regardless of the nesting level. Though this is counterintuitive, there's a very good reason for it. If a nested COMMIT actually wrote changes permanently to disk, an outer ROLLBACK wouldn't be able to reverse those changes since they would already be recorded permanently.

When you explicitly begin a transaction, the @@TRANCOUNT automatic variable count increases from 0 to 1; when you COMMIT, the count decreases by one; when you ROLLBACK, the count is reduced to 0. As you see, the behavior of COMMIT and ROLLBACK is not symmetric. If you nest transactions, COMMIT always decreases the nesting level by 1, as you can see illustrated in Figure 1. The ROLLBACK command, on the other hand, rolls back the entire transaction, illustrated in Figure 2. This asymmetry between COMMIT and ROLLBACK is the key to handling errors in nested transactions.

Figure 1: A COMMIT always balances a BEGIN TRANSACTION by reducing the transaction count by one.

Figure 2: A single ROLLBACK always rolls back the entire transaction.

As you can see from Figure 1 and Figure 2, you can nest transactions and use the @@TRANCOUNT automatic variable to detect the level. You also learned that COMMIT and ROLLBACK do not behave symmetrically; COMMIT just decreases the value of @@TRANCOUNT, while ROLLBACK resets it to 0. The implication is that a transaction is never fully committed until the last COMMIT is issued. No matter how deeply you nest a set of transactions, only the last COMMIT has any effect.

Here is an example of a nested transaction:

USE example

SELECT 'Before BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0

BEGIN TRAN

    SELECT 'After BEGIN TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 1

    DELETE Authors

    BEGIN TRAN nested

        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT

                   -- The value of @@TRANCOUNT is 2

        DELETE Publishers

    COMMIT TRAN nested

                   -- Does nothing except decrement the value of @@TRANCOUNT

    SELECT 'After COMMIT TRAN nested', @@TRANCOUNT

                   -- The value of @@TRANCOUNT is 1

ROLLBACK TRAN

SELECT 'After ROLLBACK TRAN', @@TRANCOUNT  -- The value of @@TRANCOUNT is 0

-- because ROLLBACK TRAN always rolls back all transactions and sets

-- @@TRANCOUNT to 0.

SELECT TOP 5 au_id FROM Authors

SAVE TRAN and Save Points

Savepoints offer a mechanism to roll back portions of transactions. A user can set a savepoint, or marker, within a transaction. The savepoint defines a location to which a transaction can return if part of the transaction is conditionally canceled. SQL Server allows you to use savepoints via the SAVE TRAN statement, which doesn't affect the @@TRANCOUNT value. A rollback to a savepoint (not a transaction) doesn't affect the value returned by@@TRANCOUNT, either. However, the rollback must explicitly name the savepoint: using ROLLBACK TRAN without a specific name will always roll back the entire transaction.

The following script demonstrates how savepoints can be used :

USE example

SELECT 'Before BEGIN TRAN main', @@TRANCOUNT

   -- The value of @@TRANCOUNT is 0

BEGIN TRAN main

    SELECT 'After BEGIN TRAN main', @@TRANCOUNT

   -- The value of @@TRANCOUNT is 1

    DELETE Authors

    SAVE TRAN Authors  -- Mark a save point

    SELECT 'After SAVE TRAN Authors', @@TRANCOUNT

   -- The value of @@TRANCOUNT is still 1

    BEGIN TRAN nested

        SELECT 'After BEGIN TRAN nested', @@TRANCOUNT

        -- The value of @@TRANCOUNT is 2

        DELETE Publishers

        SAVE TRAN Publishers  -- Mark a save point

        SELECT 'After SAVE TRAN Publishers', @@TRANCOUNT

        -- The value of @@TRANCOUNT is still 2

    ROLLBACK TRAN Authors

    SELECT 'After ROLLBACK TRAN Authors', @@TRANCOUNT

     -- The value of @@TRANCOUNT is still 2

    SELECT TOP 5 au_id FROM Authors

IF (@@TRANCOUNT > 0) BEGIN

    ROLLBACK TRAN

    SELECT 'AFTER ROLLBACK TRAN', @@TRANCOUNT

    -- The value of @@TRANCOUNT is 0 because

    -- ROLLBACK TRAN always rolls back all transactions and sets @@TRANCOUNT

    -- to 0.

END

   

SELECT TOP 5 au_id FROM Authors 

Error Handling

Working with TRY…CATCH

When you use the TRY…CATCH construct, consider the following guidelines and suggestions:

1. Each TRY…CATCH construct must be inside a single batch, stored procedure, or trigger. For example, you cannot place a TRY block in one batch and the associated CATCH block in another batch. The following script would generate an error:

BEGIN TRY

    SELECT * FROM sys.messages WHERE message_id = 21;

END TRY

GO

-- The previous GO breaks the script into two batches,

-- generating syntax errors. The script runs if this GO

-- is removed.

BEGIN CATCH

    SELECT ERROR_NUMBER() AS ErrorNumber;

END CATCH;

GO

2. A TRY block must be immediately followed by a CATCH block.

3. TRY…CATCH constructs can be nested. This means that TRY…CATCH constructs can be placed inside other TRY and CATCH blocks. When an error occurs within a nested TRY block, program control is transferred to the CATCH block that is associated with the nested TRY block.

4. To handle an error that occurs within a given CATCH block, write a TRY…...CATCH block within the specified CATCH block.

5. Errors that have a severity of 20 or higher that cause the Database Engine to close the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not closed.

6. Errors that have a severity of 10 or lower are considered warnings or informational messages, and are not handled by TRY…CATCH blocks.

7. Attentions will terminate a batch even if the batch is within the scope of a TRY…CATCH construct. This includes an attention sent by the Microsoft Distributed Transaction Coordinator (MS DTC) when a distributed transaction fails. MS DTC manages distributed transactions.

@@ERROR

The @@ERROR automatic variable is used to implement error handling code. It contains the error ID produced by the last SQL statement executed during a client’s connection. When a statement executes successfully, @@ERROR contains 0. To determine if a statement executes successfully, an IF statement is used to check the value of @@ERROR immediately after the target statement executes. It is imperative that @@ERROR be checked immediately after the target statement, because its value is reset to 0 when the next statement executes successfully. If a trappable error occurs, @@ERROR will have a value greater than 0. SQL Server resets the @@ERROR value after every successful command, so you must immediately capture the @@ERROR value. Most of the time, you'll want to test for changes in @@ERROR right after any INSERT, UPDATE, or DELETE statement.

The RAISERROR function is a mechanism for returning to calling applications errors with your own message.  It can use system error messages or custom error messages. The  basic syntax is easy:

RAISERROR ('You made a HUGE mistake',10,1)

I would do a stored procedure based on this template for SQL Server 2005 and newer:

BEGIN TRANSACTION

BEGIN TRY

    -- put your T-SQL commands here   

    -- if successful - COMMIT the work

    COMMIT TRANSACTION

END TRY

BEGIN CATCH

    -- handle the error case (here by displaying the error)

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() AS ErrorState,

        ERROR_PROCEDURE() AS ErrorProcedure,

        ERROR_LINE() AS ErrorLine,

        ERROR_MESSAGE() AS ErrorMessage

    -- in case of an error, ROLLBACK the transaction   

    ROLLBACK TRANSACTION

    -- if you want to log this error info into an error table - do it here

    -- *AFTER* the ROLLBACK

END CATCH

List of Errors and severity level in SQL Server with catalog view sysmessages

Error Functions

TRY…CATCH uses the following error functions to capture error information:

• 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.

Error functions can be referenced inside a stored procedure and can be used to retrieve error information when the stored procedure is executed in the CATCH block. By doing this, you do not have to repeat the error handling code in every CATCH block. In the follow code example, the SELECT statement in the TRY block will generate a divide-by-zero error. The error will be handled by the CATCH block, which uses a stored procedure to return error information.

USE YourDatabaseBName;

GO

-- Verify that the stored procedure does not exist.

IF OBJECT_ID ('usp_GetErrorInfo', 'P') IS NOT NULL

    DROP PROCEDURE usp_GetErrorInfo;

GO

-- Create a procedure to retrieve error information.

CREATE PROCEDURE usp_GetErrorInfo

AS

    SELECT

        ERROR_NUMBER() AS ErrorNumber,

        ERROR_SEVERITY() AS ErrorSeverity,

        ERROR_STATE() as ErrorState,

        ERROR_PROCEDURE() as ErrorProcedure,

        ERROR_LINE() as ErrorLine,

        ERROR_MESSAGE() as ErrorMessage;

GO

BEGIN TRY

    -- Generate divide-by-zero error.

    SELECT 1/0;

END TRY

BEGIN CATCH

    -- Execute the error retrieval routine.

    EXECUTE usp_GetErrorInfo;

END CATCH;

GO

Compile and Statement-level Recompile Errors

1. Compile errors, such as syntax errors that prevent a batch from executing.

2. Errors that occur during statement-level recompilation, such as object name resolution errors that happen after compilation due to deferred name resolution.

Uncommittable Transactions

Inside a TRY…CATCH construct, transactions can enter a state in which the transaction remains open but cannot be committed. The transaction cannot perform any action that would generate a write to the transaction log, such as modifying data or trying to roll back to a savepoint. However, in this state, the locks acquired by the transaction are maintained, and the connection is also kept open. The effects of the transaction are not reversed until a ROLLBACK statement is issued, or until the batch ends and the transaction is automatically rolled back by the Database Engine. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back.

A transaction enters an uncommittable state inside a TRY block when an error occurs that would otherwise have ended the transaction. For example, most errors from a data definition language (DDL) statement (such as CREATE TABLE), or most errors that occur when SET XACT_ABORT is set to ON, terminate the transaction outside a TRY block but make a transaction uncommittable inside a TRY block.

The code in a CATCH block should test for the state of a transaction by using the XACT_STATE function. XACT_STATE returns a -1 if the session has an uncommittable transaction. The CATCH block must not perform any actions that would generate writes to the log if XACT_STATE returns a -1. The following code example generates an error from a DDL statement and uses XACT_STATE to test the state of a transaction in order to take the most appropriate action.

USE example;

GO

-- Verify that the table does not exist.

IF OBJECT_ID (N'my_books', N'U') IS NOT NULL                  

    DROP TABLE my_books;

GO

-- Create table my_books.

CREATE TABLE my_books

    (

    Isbn        int PRIMARY KEY,

    Title       NVARCHAR(100)

    );

GO

BEGIN TRY

    BEGIN TRANSACTION;

        -- This statement will generate an error because the

        -- column author does not exist in the table.

        ALTER TABLE my_books

            DROP COLUMN author;

    -- If the DDL statement succeeds, commit the transaction.

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    SELECT

        ERROR_NUMBER() as ErrorNumber,

        ERROR_MESSAGE() as ErrorMessage;

    -- Test XACT_STATE for 1 or -1.

    -- XACT_STATE = 0 means there is no transaction and

    -- a commit or rollback operation would generate an error.

    -- Test whether the transaction is uncommittable.

    IF (XACT_STATE()) = -1

    BEGIN

        PRINT

            N'The transaction is in an uncommittable state. ' +

            'Rolling back transaction.'

        ROLLBACK TRANSACTION;

    END;

    -- Test whether the transaction is active and valid.

    IF (XACT_STATE()) = 1

    BEGIN

        PRINT

            N'The transaction is committable. ' +

            'Committing transaction.'

        COMMIT TRANSACTION;  

    END;

END CATCH;

GO

Handling Deadlocks

First of all let’s learn something about the Deadlocks ?

Deadlocking  A deadlock occurs when two or more tasks permanently block each other by each task having a lock on a resource which the other tasks are trying to lock.

TRY…CATCH can be used to handle deadlocks. The 1205 deadlock victim error can be caught by the CATCH block and the transaction can be rolled back until the threads become unlocked.

The following example shows how TRY…CATCH can be used to handle deadlocks. This first section creates a table that will be used to demonstrate a deadlock state and a stored procedure that will be used to print error information.

-- Verify that the table does not exist.

IF OBJECT_ID (N'my_sales',N'U') IS NOT NULL

    DROP TABLE my_sales;

GO

-- Create and populate the table for deadlock simulation.

CREATE TABLE my_sales

    (

    Itemid       INT PRIMARY KEY,

    Sales        INT not null

    );

GO

INSERT my_sales (itemid, sales) VALUES (1, 1);

INSERT my_sales (itemid, sales) VALUES (2, 1);

GO

 

-- Verify that the stored procedure for error printing

-- does not exist.

IF OBJECT_ID (N'usp_MyErrorLog',N'P') IS NOT NULL

    DROP PROCEDURE usp_MyErrorLog;

GO

-- Create a stored procedure for printing error information.

CREATE PROCEDURE usp_MyErrorLog

AS

    PRINT

        'Error ' + CONVERT(VARCHAR(50), ERROR_NUMBER()) +

        ', Severity ' + CONVERT(VARCHAR(5), ERROR_SEVERITY()) +

        ', State ' + CONVERT(VARCHAR(5), ERROR_STATE()) +

        ', Line ' + CONVERT(VARCHAR(5), ERROR_LINE());

    PRINT

        ERROR_MESSAGE();

GO

The following code scripts for session 1 and session 2 run simultaneously in two separate SQL Server Management Studio connections. Both sessions try to update the same rows in the table. One of the sessions will succeed with the update operation during the first attempt, and the other session will be selected as the deadlock victim. The deadlock victim error will cause execution to jump to the CATCH block and the transaction will enter an uncommittable state. Inside the CATCH block, the deadlock victim can roll back the transaction and retry updating the table until the update succeeds or the retry limit is reached, whichever happens first.

Session 1

-- Declare and set variable

-- to track number of retries

-- to try before exiting.

DECLARE @retry INT;

SET @retry = 5;

-- Keep trying to update

-- table if this task is

-- selected as the deadlock

-- victim.

WHILE (@retry > 0)

BEGIN

    BEGIN TRY

        BEGIN TRANSACTION;

   

        UPDATE my_sales

        SET sales = sales + 1

        WHERE itemid = 1;

        WAITFOR DELAY '00:00:13';

   

        UPDATE my_sales

        SET sales = sales + 1

        WHERE itemid = 2;

        SET @retry = 0;

        COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        -- Check error number.

        -- If deadlock victim error,

        -- then reduce retry count

        -- for next update retry.

        -- If some other error

        -- occurred, then exit

        -- retry WHILE loop.

        IF (ERROR_NUMBER() = 1205)

            SET @retry = @retry - 1;

        ELSE

            SET @retry = -1;

        -- Print error information.

        EXECUTE usp_MyErrorLog;

 

        IF XACT_STATE() <> 0

            ROLLBACK TRANSACTION;

    END CATCH;

END; -- End WHILE loop.

GO

Session 2

-- Declare and set variable

-- to track number of retries

-- to try before exiting.

DECLARE @retry INT;

SET @retry = 5;

--Keep trying to update

-- table if this task is

-- selected as the deadlock

-- victim.

WHILE (@retry > 0)

BEGIN

    BEGIN TRY

       BEGIN TRANSACTION;

   

        UPDATE my_sales

        SET sales = sales + 1

        WHERE itemid = 2;

        WAITFOR DELAY '00:00:07';

   

        UPDATE my_sales

        SET sales = sales + 1

        WHERE itemid = 1;

        SET @retry = 0;

        COMMIT TRANSACTION;

    END TRY

    BEGIN CATCH

        -- Check error number.

        -- If deadlock victim error,

        -- then reduce retry count

        -- for next update retry.

        -- If some other error

        -- occurred, then exit

        -- retry WHILE loop.

        IF (ERROR_NUMBER() = 1205)

            SET @retry = @retry - 1;

        ELSE

            SET @retry = -1;

        -- Print error information.

        EXECUTE usp_MyErrorLog;

 

        IF XACT_STATE() <> 0

            ROLLBACK TRANSACTION;

    END CATCH;

END; -- End WHILE loop.

GO

TRY…CATCH with RAISERROR

RAISERROR can be used in either the TRY or CATCH block of a TRY…CATCH construct to affect error-handling behavior.

RAISERROR can be used to return information to the caller about the error that caused the CATCH block to execute. Error information provided by the TRY…CATCH error functions can be captured in the RAISERROR message, including the original error number; however, the error number for RAISERROR must be >= 50000.

RAISERROR that has a severity 10 or lower returns an informational message to the calling batch or application without invoking a CATCH block.

RAISERROR that has a severity 20 or higher closes the database connection without invoking the CATCH block.

NOTE * RAISERROR can generate errors with state from 1 through 127 only. Because the Database Engine might raise errors with state 0, we recommend that you check the error state returned by ERROR_STATE before passing it as a value to the state parameter of RAISERROR.

-- Verify that stored procedure does not exist.

IF OBJECT_ID (N'usp_RethrowError',N'P') IS NOT NULL

    DROP PROCEDURE usp_RethrowError;

GO

-- Create the stored procedure to generate an error using

-- RAISERROR. The original error information is used to

-- construct the msg_str for RAISERROR.

CREATE PROCEDURE usp_RethrowError AS

    -- Return if there is no error information to retrieve.

    IF ERROR_NUMBER() IS NULL

        RETURN;

    DECLARE

        @ErrorMessage    NVARCHAR(4000),

        @ErrorNumber     INT,

        @ErrorSeverity   INT,

        @ErrorState      INT,

        @ErrorLine       INT,

        @ErrorProcedure  NVARCHAR(200);

    -- Assign variables to error-handling functions that

    -- capture information for RAISERROR.

    SELECT

        @ErrorNumber = ERROR_NUMBER(),

        @ErrorSeverity = ERROR_SEVERITY(),

        @ErrorState = ERROR_STATE(),

        @ErrorLine = ERROR_LINE(),

        @ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

    -- Build the message string that will contain original

    -- error information.

    SELECT @ErrorMessage =

        N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +

            'Message: '+ ERROR_MESSAGE();

    -- Raise an error: msg_str parameter of RAISERROR will contain

    -- the original error information.

    RAISERROR

        (

        @ErrorMessage,

        @ErrorSeverity,

        1,              

        @ErrorNumber,    -- parameter: original error number.

        @ErrorSeverity,  -- parameter: original error severity.

        @ErrorState,     -- parameter: original error state.

        @ErrorProcedure, -- parameter: original error procedure name.

        @ErrorLine       -- parameter: original error line number.

        );

GO

-- Verify that stored procedure does not exist.

IF OBJECT_ID (N'usp_GenerateError',N'P') IS NOT NULL

    DROP PROCEDURE usp_GenerateError;

GO

-- Create a stored procedure that generates a constraint violation

-- error. The error is caught by the CATCH block where it is

-- raised again by executing usp_RethrowError.

CREATE PROCEDURE usp_GenerateError

AS

    BEGIN TRY

        -- A FOREIGN KEY constraint exists on the table. This

        -- statement will generate a constraint violation error.

        DELETE FROM Authors

            WHERE au_id = 980;

    END TRY

    BEGIN CATCH

        -- Call the procedure to raise the original error.

        EXEC usp_RethrowError;

    END CATCH;

GO

-- In the following batch, an error occurs inside

-- usp_GenerateError that invokes the CATCH block in

-- usp_GenerateError. RAISERROR inside this CATCH block

-- generates an error that invokes the outer CATCH

-- block in the calling batch.

BEGIN TRY  -- outer TRY

    -- Call the procedure to generate an error.

    EXECUTE usp_GenerateError;

END TRY

BEGIN CATCH  -- Outer CATCH

    SELECT

        ERROR_NUMBER() as ErrorNumber,

        ERROR_MESSAGE() as ErrorMessage;

END CATCH;

GO

Changing the Flow of Execution

To change the flow of execution, GOTO can be used within a TRY block or a CATCH block. GOTO can also be used to exit a TRY block or a CATCH block; however, GOTO cannot be used to enter a TRY block or a CATCH block.

CREATE PROCEDURE [dbo].[uspLogError]

    @ErrorLogID [int] = 0 OUTPUT  -- Contains the ErrorLogID of the row inserted

                                  -- by uspLogError in the ErrorLog table.

AS

BEGIN

    SET NOCOUNT ON;

    -- Output parameter value of 0 indicates that error

    -- information was not logged.

    SET @ErrorLogID = 0;

    BEGIN TRY

        -- Return if there is no error information to log.

        IF ERROR_NUMBER() IS NULL

            RETURN;

        -- Return if inside an uncommittable transaction.

        -- Data insertion/modification is not allowed when

        -- a transaction is in an uncommittable state.

        IF XACT_STATE() = -1

        BEGIN

            PRINT 'Cannot log error since the current transaction is in an uncommittable state. '

                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';

            RETURN;

        END;

        INSERT [dbo].[ErrorLog]

            (

            [UserName],

            [ErrorNumber],

            [ErrorSeverity],

            [ErrorState],

            [ErrorProcedure],

            [ErrorLine],

            [ErrorMessage]

            )

        VALUES

            (

            CONVERT(sysname, CURRENT_USER),

            ERROR_NUMBER(),

            ERROR_SEVERITY(),

            ERROR_STATE(),

            ERROR_PROCEDURE(),

            ERROR_LINE(),

            ERROR_MESSAGE()

            );

        -- Pass back the ErrorLogID of the row inserted

        SELECT @ErrorLogID = @@IDENTITY;

    END TRY

    BEGIN CATCH

        PRINT 'An error occurred in stored procedure uspLogError: ';

        EXECUTE [dbo].[uspPrintError];

        RETURN -1;

    END CATCH

END;

CREATE PROCEDURE [dbo].[uspPrintError]

AS

BEGIN

    SET NOCOUNT ON;

    -- Print error information.

    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +

          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +

          ', State ' + CONVERT(varchar(5), ERROR_STATE()) +

          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +

          ', Line ' + CONVERT(varchar(5), ERROR_LINE());

    PRINT ERROR_MESSAGE();

END;

Error-handling Example

Inside the CATCH block, the following actions occur:

1.  uspPrintError prints the error information.

2.  After the transaction is rolled back, uspLogError enters the error information in the ErrorLog table and returns the ErrorLogID of the inserted row into the @ErrorLogID OUTPUT parameter.

----- Error Handling Exmple -----

-- Variable to store ErrorLogID value of the row

-- inserted in the ErrorLog table by uspLogError

DECLARE @ErrorLogID INT;

BEGIN TRY

    BEGIN TRANSACTION;

    -- A FOREIGN KEY constraint exists on this table. This

    -- statement will generate a constraint violation error.

    DELETE FROM Authors

        WHERE au_id = 980;

    -- If the delete operation succeeds, commit the transaction.

    COMMIT TRANSACTION;

END TRY

BEGIN CATCH

    -- Call procedure to print error information.

    EXECUTE dbo.uspPrintError;

    -- Roll back any active or uncommittable transactions before

    -- inserting information in the ErrorLog.

    IF XACT_STATE() <> 0

    BEGIN

        ROLLBACK TRANSACTION;

    END

    EXECUTE dbo.uspLogError @ErrorLogID = @ErrorLogID OUTPUT;

END CATCH;

-- Retrieve logged error information.

SELECT * FROM dbo.ErrorLog WHERE ErrorLogID = @ErrorLogID;

GO

Nested Error-handling Example

 BEGIN TRY

    BEGIN TRY

        SELECT CAST('invalid_date' AS datetime)

    END TRY

    BEGIN CATCH

        PRINT 'Inner TRY error number: ' +   

            CONVERT(varchar,ERROR_NUMBER()) + ' on line: ' +

            CONVERT(varchar, ERROR_LINE())

    END CATCH

    SELECT CAST('invalid_int' AS int)

END TRY

BEGIN CATCH

    PRINT 'Outer TRY error mumber: ' + CONVERT(varchar,ERROR_NUMBER())+

            ' on line: ' + CONVERT(varchar, ERROR_LINE())

END CATCH

 

SEVERITY - When an error is raised by the SQL Server Database Engine, the severity of the error indicates the type of problem encountered by SQL Server.

16 is a default severity level and used for most user exception and that was the reason I have used it. 

You can get a list of error messages along with its severity level and error number from following catalog view.

Generally we have sixteen different severity levels in SQL Server 2012 and each severity represents criticalness of an error. You can get a list of severity from the following TSQL.

SELECT DISTINCT severity FROM master.dbo.sysmessages order by severity

Here is the brief description of different severity.

Severity level 0-10: These are just information message not actual error.

Severity level 11 to 16: These are errors caused due to user mistakes. We have tried to divide value by 0 and hence we got severity error 16.

Severity Level 17: This severity indicates that an operation making SQL Server out of resources or exceeding defined limit. That may be disk space or lock limit.

Severity Level 18: This error represents nonfatal internal software error.

Severity Level 19: This error represents some non-configurable internal limit has been exceeded and the current batch process is terminated. To be very frank, I have not seen this severity practically in my life.

Severity Level 20: This severity indicates current statement has encountered a problem and because of this severity level client connection with SQL Server will be disconnected.

Severity Level 21: This severity indicates that you have encountered a problem that affects all processes in the current database.

Severity Level 22: This error indicates problem with database table or index. It may be corrupt or damaged.

Severity Level 23: This error indicates problem with database integrity which may be fixed by DBCC command.

Severity Level 24: This error indicates problem with the hardware of SQL Server. Need to check disk drive and related hardware extensively.

Error handling with “THROW” command in SQL Server 2012

Error handling is one of the essential skill developer should have. It was very difficult to handle error efficiently till SQL Server 2000. After SQL Server 2000, we have get TRY…CATCH in SQL Server along with RAISERROR in SQL Server 2005. RAISERROR is even improved in form of “THROW” in SQL Server 2012.

It is not possible/feasible, sometime, in big production environment to replicate same issue in development database, at the sametime, we can’t execute some command/query on live environment if it is affecting client’s data so efficient error handling is required so that you can log proper error along with its message, error number and other important things.

Let us create sample temporary table in AdventureWorks2012 database, if you don’t have Adventureworks2012 database with you, you can use your own database as I will be having temp table for this demonstration.

IF OBJECT_ID('tempdb..#TestingTHROWCommand') IS NOT NULL

DROP TABLE #TestingTHROWCommand

CREATE TABLE #TestingTHROWCommand

(

ID INT IDENTITY(1,1)

,Name VARCHAR(50)

,OvertimeAmount INT

)

INSERT INTO #TestingTHROWCommand

SELECT 'Ritesh Shah',15 UNION ALL

SELECT 'Teerth Shah',0 UNION ALL

SELECT 'Rajan Jain',9

GO

select * from #TestingTHROWCommand

Now let us try to execute one UPDATE statement where we will have one division operator which divide on “OvertimeAmount” field. We have value “0” with row number 2 so obviously we will be facing an error.

BEGIN TRY

BEGIN TRANSACTION

UPDATE #TestingTHROWCommand

SET OvertimeAmount=95/OvertimeAmount

COMMIT TRANSACTION

END TRY

BEGIN CATCH

SELECT ERROR_NUMBER(),ERROR_MESSAGE(),ERROR_LINE()

ROLLBACK TRANSACTION

END CATCH

GO

As soon as we will execute above code, we will be greeted with an error because on row # 2, we have 0 which will try to divide 95 and 0 can’t be used to divide anything. Here is the error message we will get:

(0 row(s) affected)

8134   Divide by zero error encountered. 3

Look at the error number, message and error line number given above. Now, we will try to handle error with different way as follow:

BEGIN TRY

BEGIN TRANSACTION

UPDATE #TestingTHROWCommand

SET OvertimeAmount=95/OvertimeAmount

COMMIT TRANSACTION

END TRY

BEGIN CATCH

DECLARE @ErrorMsg NVARCHAR(MAX) = ERROR_MESSAGE()

ROLLBACK TRANSACTION

RAISERROR(@ErrorMsg,16,1)

END CATCH

GO

We have same UPDATE statement so obviously we will be getting error message again but this time, it will come by “RAISERROR” command. Here is the output of RAISERROR.

(0 row(s) affected)

Msg 50000, Level 16, State 1, Line 10

Divide by zero error encountered.

Look at the error number and error line. It seems wrong because of “RAISERROR”. Let us now try to handle the error with “THROW” command.

BEGIN TRY

BEGIN TRANSACTION

UPDATE #TestingTHROWCommand

SET OvertimeAmount=95/OvertimeAmount

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION;

THROW

END CATCH

GO

we will again receive proper error message, number and line with “THROW” command. Have a look:

(0 row(s) affected)

Msg 8134, Level 16, State 1, Line 3

Divide by zero error encountered.

I have recently seen many of the developer who are using SQL Server 2012, still uses “RAISERROR” as against “THROW”. I would highly recommend start using “THROW” command. 

ERROR_LINE () Returns the line number at which an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_MESSAGE()  Returns the message text of the error that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_NUMBER() Returns the error number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_PROCEDURE() Returns the name of the stored procedure or trigger where an error occurred that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_SEVERITY() Returns the severity of the error that caused the CATCH block of a TRY…CATCH construct to be run.

ERROR_STATE() Returns the state number of the error that caused the CATCH block of a TRY…CATCH construct to be run.

XACT_STATE() Is a scalar function that reports the user transaction state of a current running request. XACT_STATE indicates whether the request has an active user transaction, and whether the transaction is capable of being committed.

XACT_STATE returns the following values.

Return Value  Meaning

1- The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction.

0 - There is no active user transaction for the current request.

-1 - The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. 

The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.

When a batch finishes running, the Database Engine will automatically roll back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This message indicates that an uncommittable transaction was detected and rolled back.

Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction. @@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction. XACT_STATE cannot be used to determine whether there are nested transactions.

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.

RAISERROR ( { msg_id | msg_str | @local_variable }

    { ,severity ,state }

    [ ,argument [ ,...n ] ] )

    [ WITH option [ ,...n ] ]

References: https://msdn.microsoft.com/en-IN/library/ms175976.aspx

Read More »