How to handle all the error in sql server database, it is very easy, you will say the same after reading this article. If I ask the same question right now many of us start thinking how we can use it, do we need to create a table, procedure and what would be useful values which should be in this table and how to call the procedure from every procedure, how to page the parameter etc. stop thinking for now.
Here is the steps:
So what are the information we need to log into the table, let's create the table:
CREATE TABLE [ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorMessage] [varchar](8000) NULL,
[Severity] [int] NULL,
[State] [int] NULL,
[Procedure] [varchar](200) NULL,
[LineNo] [int] NULL,
[UserName] [varchar](128) NULL,
[Errortime] [datetime] NULL
) ON [PRIMARY]
If you can see closely we have everything here, now let's create a procedure to insert all these values without passing a single parameter, What? Without passing a any parameter? yes, see this:
Create Proc LogError
AS
Insert Into ErrorLog(
[ErrorNumber],
[ErrorMessage],
[Severity],
[State],
[LineNo],
[Procedure],
[UserName],
[Errortime])
SELECT error_number()
, error_message()
, error_severity()
, error_state()
, error_line()
, error_procedure()
, suser_sname()
, GETDATE()
See the procedure, do you think we need to pass any value for this procedure, no because I took everything from the different different functions.
Time to use what we have created otherwise it's use less, let try to create procedure simply divide any number by zero(0) and check the table.
Create Proc testlog
AS
Begin Try
select 10/0
End Try
Begin Catch
EXEC Logerror
End Catch
In this procedure i try to divide 10 by 0, now execute the procedure test log
EXEC testlog
Now let's check our log table now
Select * From ErrorLog
and here is the log detail
ErrorLogID ErrorNumber ErrorMessage Severity State Procedure LineNo UserName Errortime
1 8134 Divide by zero error encountered. 16 1 spTest 4 sa 2014-02-22 18:51:19.347
![]() |
Having 13+ years of experience in Microsoft Technologies (C#, ASP.Net, MVC and SQL Server). Worked with Metaoption LLC, for more than 9 years and still with the same company. Always ready to learn new technologies and tricks.
|
By Ali Adravi On 23 Feb, 14 Viewed: 756 |
We can not directly change a column to identity columns, say we have a primary key column or any normal column in our table and want to make it identity column then we cannot do it directly by altering the table but there are only two way to change column to identity column 1. Create a new... By Ali Adravi On 02 May 2013 Viewed: 1,096
There are many ways which can help us to save our time to write some repeated code or to do some repeated action, in this post we will see some very common code which we need very frequently in our development life. I am not saying that after this post you will save your half say work every day but... By Ali Adravi On 31 Mar 2013 Viewed: 936
In SQL server there are decimal and money data type to store precision and scale both together or say decimal values. Suppose we want to get precision and scale separately then how to get it. There are many ways to do this but there is a function in SQL named PARSENAME which can be used to get... By Myghty On 26 Mar 2013 Viewed: 3,394
To write a good performing procedure or function we should be aware about available wildcards in SQL Server. I found most developer depends on ‘%’ wildcard only while we have others as well. In this article we will discuss all the available wildcards in detail with examples. First of all let’s see... By Dev D On 25 Feb 2013 Viewed: 678
When we need to concatenate two columns simply we can use `+` sign and that is correct, but what if any of them is null, Will it return what we want, NO, it will return null. So let's discuss how we should concatenate two or more columns without creating extra space or extra comma problem. Let's... By Ali Adravi On 13 Feb 2013 Viewed: 12,778