How Would You Handle Error In Sql Server 2008
Contents |
BloeschMarch 10, 20099 0 0 0 Error handling in SQL Server 2008 needs careful implementation. The Microsoft “Oslo” Repository’s API has the further problem that we cannot mandate the error handling logic in our try catch in sql server stored procedure callers. Thus a stored procedure call could be in a transaction or not and sql server error handling in a try-catch block or not. Below is the pattern we have chosen based on experimentation and best practice guidance sql try catch throw from the SQL Server team and other SQL Server experts. A test script for all the interesting cases can be found at http://blogs.msdn.com/cfs-file.ashx/__key/communityserver-components-postattachments/00-09-46-95-77/Error-Handling.sql. For a good overview of SQL Server error handling see http://www.sommarskog.se/error-handling-I.html
Sql Server Try Catch Transaction
and http://www.sommarskog.se/error-handling-II.html. In our design, we had the following main issues and mitigations: · A transaction rollback will rollback to the outermost transaction but if there is an outer transaction we would like to only rollback the changes to the module’s inner transaction. The solution is to use SQL Server save points if there is an outer transaction. · A sufficiently severe raiserror will terminate a module sql server stored procedure error handling best practices if there is an outer try-catch block but if there is none then execution will continue in the module. The solution is to always explicitly return after raising an error. · Some developers like to use stored procedure return values to encode error states. The solution is to return appropriate error codes. · SQL Server will raise warnings if the transaction depth entering and leaving a transaction do not match. The solution is to be careful. · Triggers have an implicit transaction. The solution is to use a simplified pattern for triggers where a transaction is never started. · Save points need unique names if modules can nest otherwise you can rollback to the wrong save point. The solution is to use a GUID to name the save points. Here is the pattern for stored procedures (eliding our special error reporting routines): ifparameter errorbegin raiserror(N'…', 16, 0);
return -1;endelsebegin begin try declare @hasOuterTransaction bit = case when @@trancount > 0 then 1 else 0 end; declare @rollbackPoint nchar(32) = replace(convert(nchar(36), newid()), N'-‘, N"); if @hasOuterTransaction = 1 begin save transaction @rollbackPoint; end else begin begin transaction @rollbackPoint; end; Do work; if @hasOuterTransaction = 0 begin commit transaction; en2011 Arunraj Leave a comment Go to comments With reference to the Question posted by Hima Bindu Vejella on http://himabinduvejella.blogspot.com/2011/06/how-did-you-handle-errors-in-sqlserver.html, I have posted these answers, I hope error handling in sql server 2012 you all find this usefull How did you handle errors in SqlServer
T-sql Raiserror
2008 R2? We handle errors using BEGIN TRY BEGIN TRANSACTION /* Insert or Update or Delete statements */ COMMIT
Sql Server Error_message
TRANSACTION END TRY BEGIN CATCH INSERT INTO ErrorLog (ErrorNumber, ErrorSeverity, ErrorState, ErrorLine, ErrorProcedure, ErrorRelease) SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() as ErrorState, ERROR_LINE () as ErrorLine, ERROR_PROCEDURE() as ErrorProcedure, ERROR_MESSAGE() https://blogs.msdn.microsoft.com/anthonybloesch/2009/03/10/sql-server-2008-error-handling-best-practice/ as ErrorMessage IF (XACT_STATE()) = -1 BEGIN ROLLBACK TRAN END ELSE IF (XACT_STATE()) = 1 BEGIN COMMIT TRAN END RAISEERROR(Application Error Occurred. Please contact administrator, 16, -1) END CATCH()
What are the different ways of handling errors in SQL Server? 1. We can handle errors by checking @@ERROR, if it’s not equal to 0, then we can use RAISERRROR to return https://sqlxpertise.com/2011/06/03/error-handling-in-sql-server-2008-r2questions-answered/ error message to application 2. We can handle error by checking @@ERROR and @@ROWCOUNT in combination and then use RAISERROR or RETURN to return error message or code to application 3. We can use TRY..CATCH method Which is the optimized way to handle errors? The optimized way to handle errors is to create Error Log table with following columns (ErrorNumber, ErrorMessage, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, UserName, HostName, ErrorDateTime, ApplicationName) and then create a Stored Procedure to get these columns as Input Parameter except ErrorDateTime, for which we can use GetDate() and then call this Stored Procedure inside BEGIN CATCH..END CATCH block What are the Built-In Objects available related to error handling? The following are the built in objects related to Error Handling and Information ERROR_NUMBER() – Returns Error Number of the Error that caused the CATCH block of TRY…CATCH to run ERROR_SEVERITY() – Returns severity of Error that caused the CATCH block of TRY…CATCH to run ERROR_STATE() – Returns State Number of the Error that caused the CATCH block of TRY…CATCH to run ERROR_LINE () – Returns the line number at which an error occurred that| Tags: Stored Procedures, SQL Server 2005, Transactions, SQL Server 2008 Features, Error Handling This article covers the basics of TRY CATCH error handling in http://www.sqlteam.com/article/handling-sql-server-errors T-SQL introduced in SQL Server 2005. It includes the usage of common functions to return information about the error and using the TRY CATCH block in stored procedures and transactions. SQL Server uses the following syntax to capture errors in Transact-SQL statements: BEGIN TRY SELECT [First] = 1 SELECT [Second] = 1/0 SELECT [Third] = 3 END TRY BEGIN CATCH PRINT 'An error occurred' sql server END CATCH This returns the following output: First ----------- 1 (1 row(s) affected) Second ----------- (0 row(s) affected) An error occurred A key difference from SQL Server 2000 is that execution is halted when SQL Server encounters an error. At that point execution transfers to the CATCH block. This error isn't returned to the client application or calling program. The TRY CATCH block consumes the in sql server error. Error Functions Inside the CATCH block there are a number of specialized functions to return information about the error. BEGIN TRY SELECT [Second] = 1/0 END TRY BEGIN CATCH SELECT [Error_Line] = ERROR_LINE(), [Error_Number] = ERROR_NUMBER(), [Error_Severity] = ERROR_SEVERITY(), [Error_State] = ERROR_STATE() SELECT [Error_Message] = ERROR_MESSAGE() END CATCH Second ----------- (0 row(s) affected) Error_Line Error_Number Error_Severity Error_State ----------- ------------ -------------- ----------- 2 8134 16 1 (1 row(s) affected) Error_Message --------------------------------------------------- Divide by zero error encountered. (1 row(s) affected) These are the functions you can use inside a CATCH block. These functions all return NULL if they are called from outside a CATCH block. ERROR_NUMBER. The number of the error that occurred. This is similar to @@ERROR except that it will return the same number for the duration of the CATCH block. ERROR_MESSAGE. The complete text of the error message including any substiture parameters such as object names. ERROR_LINE. This is the line number of the batch or stored procedure where the error occured. ERROR_SEVERITY. This is the severity of the error. The CATCH block only fires for errors with severity 11 or higher. Error severities from 11 to 16 are typically use