Handling Errors in SQL Server (Try Catch & Transactions)

During development of any application, one of the most common things we need to take care of is Exception and Error handling.

TRY…CATCH blocks are the standard approach to exception handling in modern programming languages. Use and syntax are likely the same as normal programming language. Like Exception Handling in Programming Language, we can use nested Try-Catch block in SQL Server also.

Try block will catch the error and will throw it in the Catch block. Catch block then handles the scenario.

trycatch

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:

  • Begin Transaction
  • Rollback Transaction
  • Commit Transaction

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

Example :

CREATE PROCEDURE UpdateSales
  @SalesPersonID INT,
  @SalesAmt MONEY = 0
AS
BEGIN

  BEGIN TRY
    BEGIN TRANSACTION;
      UPDATE SalesData
      SET SalesLastYear = SalesLastYear + @SalesAmt
      WHERE SalesPersonID = @SalesPersonID;
    COMMIT TRANSACTION;
  END TRY

  BEGIN CATCH
    IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION;

    -- @@TRANCOUNT returns number of active Transactions,
    -- BEGIN TRANSACTION will increment the transaction count by 1 & 
    -- COMMIT TRANSACTION will decrement the transaction count by 1
    DECLARE @ErrorNumber INT = ERROR_NUMBER();
    DECLARE @ErrorLine INT = ERROR_LINE();
    DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
    DECLARE @ErrorSeverity INT = ERROR_SEVERITY();
    DECLARE @ErrorState INT = ERROR_STATE();

    PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
    PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
  END CATCH

END

DEFAULT Constraint – Adding Column Defaulting in Sql Server Table

The DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new records, if no other value is specified.

Rather than providing a value into a column manually during insert operation, by using default option we can auto-populate specific value and update in column.

in the below example, no need to provide CreatedDate and Freezed values during Insertion .

Example :

create table TestTable
(
Id int IDENTITY(1,1) NOT NULL,
UserId int,

—–

—–
CreatedDate datetime default (getdate()),
Freezed int default (0)
)

Comma separated / delimited string to a table in SQL Server

Recently, I came across a piece of TSQL code that would take a comma separated string as an input and parse it to return a single column table from it.
CREATE FUNCTION [dbo].[UDF_string_to_table]

(
@string VARCHAR(8000),
@delimiter CHAR(1)
)
RETURNS @output TABLE(CommaSeparatedString VARCHAR(256))

BEGIN

DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)

WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1

INSERT INTO @output (CommaSeparatedString) VALUES(SUBSTRING(@string, @start, @end – @start))

SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)
END

RETURN
END

Output:

select * from UDF_string_to_table(‘1,2,3′,’,’)

Bulk Insert into SQL Server using SqlBulkCopy

Transferring data from one source to another is common practice in software development. This operation is preformed in many different scenarios which includes migration of the old system to the new system, backing up the data and collecting data from different publishers. ASP.NET 2.0 includes the SqlBulkCopy class that helps to copy the data from different data sources to the SQL SERVER database.

Reliable transaction are maintained by using SqlTransaction object to BulkCopy. In-case of error whole transaction will be role-backed.

Example :

SqlTransaction myTransaction;

DataTable FinalTable = (DataTable)Session[“Data”];

using (SqlConnection cn = Get_Connection())

{

myTransaction = cn.BeginTransaction();

try

{

using (SqlBulkCopy copy = new SqlBulkCopy(cn, SqlBulkCopyOptions.Default, myTransaction))

{

copy.ColumnMappings.Add(0, 1); //(SOURCE TABLE,DESTINATION TABLE)

copy.ColumnMappings.Add(2, 2); // (“Product Name”,”Name”)

copy.ColumnMappings.Add(3, 3);

copy.ColumnMappings.Add(4, 4);

copy.ColumnMappings.Add(5, 5);

copy.ColumnMappings.Add(6, 6);

copy.ColumnMappings.Add(7, 7);

copy.ColumnMappings.Add(8, 8);

copy.ColumnMappings.Add(9, 9);

//copy.BatchSize = 1000;

copy.DestinationTableName = “dbo.Products”;

copy.WriteToServer(FinalTable); // writing Source Data to sqlserver table

myTransaction.Commit();

}

}

catch (Exception ex)

{

myTransaction.Rollback();

}

finally

{

cn.Close();

}

}

(or)

public bool BulkInsert(DataTable dataTable)
{

if (dataTable != null && dataTable.Rows.Count > 0)
{
using (SqlConnection sqlConnection = new SqlConnection(Database.Connection_String))
{
sqlConnection.Open();

SqlBulkCopy objSqlBulkCopy = new SqlBulkCopy(sqlConnection) { DestinationTableName = “DestinationTableName” };

foreach (DataColumn dataColumn in dataTable.Columns)
objSqlBulkCopy.ColumnMappings.Add(dataColumn.ColumnName, dataColumn.ColumnName);

objSqlBulkCopy.WriteToServer(dataTable);
return true;
}
}
return false;
}

Know Unknown of our Databases

Some times it is necessary to know database related details, such as Database Status, DataSize, Compatability, Creation Date, Lastbackup Details.

While browsing on internet I got this query, which is useful to know – unknown of our databases.

SELECT database_id[Database ID],

CONVERT(VARCHAR(25), DB.name)AS DatabaseName,

CONVERT(VARCHAR(10),DATABASEPROPERTYEX(name,‘status’))AS [Status],

state_desc[Status Description],

(SELECTCOUNT(1)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘rows’)AS DataFiles,

(SELECTSUM((size*8)/1024)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘rows’)AS [Data MB],

(SELECTCOUNT(1)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘log’)AS LogFiles,

(SELECTSUM((size*8)/1024)FROMsys.master_filesWHEREDB_NAME(database_id)= DB.nameAND type_desc =‘log’)AS [Log MB],

user_access_desc AS [User access],

recovery_model_desc AS [Recovery model],

CASE compatibility_level

WHEN 60 THEN’60 (SQL Server 6.0)’

WHEN 65 THEN’65 (SQL Server 6.5)’

WHEN 70 THEN’70 (SQL Server 7.0)’

WHEN 80 THEN’80 (SQL Server 2000)’

WHEN 90 THEN’90 (SQL Server 2005)’

WHEN 100 THEN‘100 (SQL Server 2008)’

ENDAS [compatibility level],

CONVERT(VARCHAR(20), create_date, 103)+‘ ‘+CONVERT(VARCHAR(20), create_date, 108)AS [Creation date],

— last backup

ISNULL((SELECTTOP 1

CASETYPEWHEN‘D’THEN‘Full’WHEN‘I’THEN‘Differential’WHEN‘L’THEN‘Transaction log’END++

LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY,GETDATE(),Backup_finish_date)))+‘ days ago’,‘NEVER’))++

CONVERT(VARCHAR(20), backup_start_date, 103)+‘ ‘+CONVERT(VARCHAR(20), backup_start_date, 108)++

CONVERT(VARCHAR(20), backup_finish_date, 103)+‘ ‘+CONVERT(VARCHAR(20), backup_finish_date, 108)+

(‘+CAST(DATEDIFF(second, BK.backup_start_date,BK.backup_finish_date)ASVARCHAR(4))+‘ ‘+‘seconds)’

FROM msdb..backupset BK WHERE BK.database_name = DB.nameORDERBY backup_set_id DESC),‘-‘)

AS [Last Backup Date],

CASEWHEN is_fulltext_enabled = 1 THEN‘Fulltext enabled’ELSEENDAS [FullText],

CASEWHEN is_auto_close_on = 1 THEN‘autoclose’ELSEENDAS [Autoclose],

page_verify_option_desc AS [page verify option],

CASEWHEN is_read_only = 1 THEN‘read only’ELSEENDAS [Read Only],

CASEWHEN is_auto_shrink_on = 1 THEN‘autoshrink’ELSEENDAS [Auto Shrink],

CASEWHEN is_auto_create_stats_on = 1 THEN‘auto create statistics’ELSEENDAS [auto create statistics],

CASEWHEN is_auto_update_stats_on = 1 THEN‘auto update statistics’ELSEENDAS [auto update statistics],

CASEWHEN is_in_standby = 1 THEN‘standby’ELSEENDAS [Standby],

CASEWHEN is_cleanly_shutdown = 1 THEN‘cleanly shutdown’ELSEENDAS [Cleanly Shutdown]

FROMsys.databases DB

ORDERBY DatabaseName

Enterprise Applications Integration -Overview

E A I – Breaking down information barriers across the Enterprise

Image

Overview :

As enterprises spread their boundaries across different business areas, they acquire disparate technology solutions, technologies and applications. However, these applications are often unable to communicate and share information with each other due to several reasons like lack of interoperability, variable formats and dissimilar standards of operations.

Supply chain management applications (for managing inventory and shipping), customer relationship management applications (for managing current and potential customers), business intelligence applications (for finding patterns from existing data from operations), and other types of applications (for managing data such as human resources data, health care, internal communications, etc.), ERP (For Resource Planning), Portals (for Operations) typically cannot communicate with one another in order to share data or business rules. For this reason, such applications are sometimes referred to as islands of automation or information silos. This lack of communication leads to inefficiencies, wherein identical data are stored in multiple locations, or straightforward processes are unable to be automated.

Even in our enlightened times, great amounts enterprise data lies locked away in monolithic applications. Whether they be purchased, home-grown, or a combination, the realities of business demand that the information contained in those vertical ‘silos’ be used in ways that the original architects never intended.

Enterprises are also increasingly looking forward to reconfigure their business processes through tighter integration of applications via rich frameworks, by opting for Enterprise Application Integration (EAI). This is with the intention to achieve effective communication across various business applications and delivery channels, thereby, ensuring seamless data integration from disparate systems

 Enterprise Application Integration:

 Enterprise application integration is an integration framework composed of a collection of technologies and services which form a middleware (Remote Procedure Call, Object Request Broker, Batch Data Communication, Business Application Programming Interface, Service Oriented Architectures) to enable integration of systems and applications across the enterprise.

Enterprise Application Integration practice expertise focuses on:

Opening up ways for the information to flow from one application to another,

Letting applications to reach other sources of data outside themselves,

and Allowing other programs to peer into the innards of formerly closed programs.

Sharing data between divisions or even between separate corporations

Image

and i wanted to discuss more about this challenge architecture in next couple of articles along with Implementations too which are observed by me since long time.

How to Commit and Rollback Transaction in .NET & Sql Server

If you are sending several commands to SQL Server database, you should make it a habit to build your application to be rollback capable in order to recover all commands that were already executed. In ADO.NET, we can achieve this recovery process by using the SqlTransaction object. A SqlTransaction’s ROLLBACK method can undo any work performed since the BeginTransaction method was executed while the COMMIT method ends a transaction and makes all changes visible to other users.

Example C#:

SqlTransaction trans = null;

try
{
con.Open();
// BeginTransaction() Requires Open Connection
trans = con.BeginTransaction();

// Assign Transaction to Command
cmd.Transaction = trans;

//1st command to execute. This will be rollback if 2nd command fails
cmd.CommandText = “INSERT INTO HeaderTab (Description) VALUES (‘Inserted New Category’)”;
cmd.ExecuteNonQuery();

//2nd command to execute
cmd.CommandText = “INSERT INTO DetailedTab (Description) VALUES (‘TEST CATEGORY’)”;
cmd.ExecuteNonQuery();

trans.Commit();
}
catch
{
trans.Rollback();
Console.WriteLine(“Error while processing command. Previous execution was rollbacked”);
}
finally
{
con.Close();
}

Example SqlServer:

CREATE PROCEDURE DeleteEmployee
( @EmployeeID int )
AS
BEGIN
BEGIN TRY
 BEGIN TRANSACTION TraDel— Start the transaction

— Delete the Employee record
DELETE FROM Employees
WHERE EmployeeID = @EmployeeID

— If we reach here, success!
COMMIT TRANSACTION TraDel
END TRY
BEGIN CATCH
— Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION TraDel

— Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(),
@ErrSeverity = ERROR_SEVERITY()

RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH
END