[Translation] Handling errors and transactions in SQL Server. Part 1. Error handling - quick start
Hi, Habr! I present to you the translation of the article “Error and Transaction Handling in SQL Server. Part One - Jumpstart Error Handling " by Erland Sommarskog.
This article is the first in a series of three articles on error and transaction handling in SQL Server. Its goal is to give you a quick start to the error handling thread by setting a basic example that fits most of your code. This part is written for an inexperienced reader, and for this reason I deliberately keep silent about many details. At the moment, the task is to tell how, without emphasis on why . If you take my words on faith, you can read only this part and put the other two aside for further steps in your career.
On the other hand, if you question my recommendations, you definitely need to read the other two parts, where I dive into the details much more deeply, exploring the very tangled world of error and transaction processing in SQL Server. The second and third parts, as well as the three applications, are intended for readers with deeper experience. The first article is short, the second and third are much longer.
')
All articles describe error and transaction handling in SQL Server for version 2005 and later.
Why do we handle errors in our code? There are many reasons for this. For example, on the forms in the application, we check the data entered and inform users about errors made during the input. User errors are foreseeable errors. But we also need to handle unexpected errors. That is, errors may occur due to the fact that we have missed something when writing code. A simple approach is to interrupt the execution or at least return to the stage in which we have complete control over what is happening. It will not be enough just to emphasize that it is completely impermissible to ignore unforeseen errors. This is a flaw that can cause disastrous consequences: for example, cause the application to provide incorrect information to the user or, even worse, store incorrect data in the database. It is also important to report the occurrence of an error so that the user does not think that the operation was successful, while your code actually did not perform anything.
We often want changes in the database to be atomic . For example, the task of transferring money from one account to another. To this end, we need to change two entries in the CashHoldings table and add two entries to the Transactions table. It is absolutely unacceptable that errors or failure led to the fact that the money will be transferred to the beneficiary’s account, and it will not be debited from the sender’s account. For this reason, error handling also applies to transaction processing. In the example above, we need to wrap the operation in BEGIN TRANSACTION and COMMIT TRANSACTION, but not only this: in case of an error, we have to make sure that the transaction has been rolled back.
We will begin with an overview of the most important commands that are required for error handling. In the second part, I will describe all the commands related to error handling and transactions.
The main error handling mechanism is the TRY-CATCH construct, very much like similar constructions in other languages. The structure is as follows:
If any error appears in
As a rule, in CATCH any open transaction is rolled back and re-cause an error. Thus, the calling client program understands that something went wrong. Recalling the error will be discussed later in this article.
Here is a very quick example:
Result of performance:
We will return to the error_message () function later. It should be noted that the use of PRINT in the CATCH handler is provided only as part of experiments and should not be done in the real application code.
If
There is one very important limitation in the TRY-CATCH construct that needs to be known: it does not catch compilation errors that occur in the same scope. Consider an example:
Output:
As you can see, the TRY block is present, but when an error occurs, execution is not transferred to the CATCH block, as expected. This applies to all compilation errors, such as column skipping, invalid aliases, and the like that occur during execution. (Compilation errors may occur in SQL Server at runtime due to deferred name resolution — a feature that allows SQL Server to create a procedure that refers to non-existent tables.)
These errors are not completely elusive; you cannot catch them in the area in which they arise, but you can catch them in the outer area. Add this code to the previous example:
Now we get this output:
This time the error was intercepted because the external CATCH handler worked.
You should always add this expression to the beginning of your stored procedures:
It activates two session parameters that are turned off by default for compatibility with previous versions, but experience proves that the best approach is to have these parameters always on. The default behavior of SQL Server in the situation when TRY-CATCH is not used is that some errors interrupt execution and roll back any open transactions, while with other errors the execution of subsequent instructions continues. When you turn on XACT_ABORT ON, almost all errors begin to cause the same effect: any open transaction is rolled back, and the execution of the code is interrupted. There are a few exceptions, among which the most noticeable expression is RAISERROR.
The XACT_ABORT parameter is required for more reliable error handling and transaction processing. In particular, with the default settings, there are several situations where execution can be interrupted without any rollback of the transaction, even if you have a TRY-CATCH. We saw this example in the previous section, where we found out that TRY-CATCH does not intercept compilation errors that occurred in the same area. An open transaction that was not rolled back due to an error can cause serious problems if the application continues to work without completing the transaction or rolling back.
For reliable error handling in SQL Server, you need both TRY-CATCH and SET XACT_ABORT ON. Among them, the instruction SET XACT_ABORT ON is the most important. If it is not worth relying on it for code on an industrial environment, it is quite suitable for quick and simple solutions.
The NOCOUNT parameter has nothing to do with error handling, but including it in the code is a good practice. NOCOUNT suppresses messages of the form (1 row (s) affected), which you can see in the Message panel in SQL Server Management Studio. While these messages can be useful when working with SSMS, they can negatively affect application performance as they increase network traffic. The message about the number of rows can also lead to an error in poorly written client applications, which may think that this is the data that the query returned.
Above, I used a syntax that is a bit unusual. Most people would write two separate expressions:
There is no difference between them. I prefer the version with SET and the comma, since This reduces the noise level in the code. Since these expressions should appear in all your stored procedures, they should take up as little space as possible.
After we looked at TRY-CATCH and SET XACT_ABORT ON, let's connect them together in an example that we can use in all of our stored procedures. First, I will show an example in which an error is generated in a simple form, and in the next section I will look at solutions better.
For example, I will use this simple table.
Here is a stored procedure that demonstrates how you should work with errors and transactions.
The first line in the procedure includes XACT_ABORT and NOCOUNT in one expression, as I showed above. This line is the only one before BEGIN TRY. Everything else in the procedure should be placed after BEGIN TRY: declaration of variables, creation of temporary tables, table variables, everything. Even if you have other SET-commands in the procedure (although the reasons for this are rare), they should go after BEGIN TRY.
The reason I prefer to specify SET XACT_ABORT and NOCOUNT before BEGIN TRY is that I see it as one line of noise: it should always be there, but I don’t want it to interfere with the look. Of course, this is a matter of taste, and if you prefer to put SET-commands after BEGIN TRY, do not worry. The important thing is that you should not put anything else in front of BEGIN TRY.
The part between BEGIN TRY and END TRY is the main component of the procedure. Since I wanted to use a user-defined transaction, I introduced a rather contrived business rule, which says that if you insert a pair, then the reverse pair must also be inserted. The two INSERT statements are inside BEGIN and COMMIT TRANSACTION. In many cases, you will have many lines of code between BEGIN TRY and BEGIN TRANSACTION. Sometimes you will also have code between COMMIT TRANSACTION and END TRY, although this is usually just a final SELECT that returns data or assigns values to output parameters. If your procedure does not perform any changes or has only one INSERT / UPDATE / DELETE / MERGE statement, then usually you don’t need to explicitly specify the transaction at all.
While the TRY block will look different from procedure to procedure, the CATCH block should be more or less the result of copying and pasting. So you do something short and simple and then use it everywhere, without really thinking. The CATCH handler above performs three actions:
These three actions should always be there. We can argue that the string
not needed if there is no explicit transaction in the procedure, but this is absolutely wrong. Perhaps you are calling a stored procedure that opens a transaction, but which cannot roll it back due to TRY-CATCH restrictions. Perhaps you or someone else will add an explicit transaction after two years. Do you remember then that you need to add a line with a rollback? Do not count on it. I also hear readers who object that if the person calling the procedure has opened a transaction, we should not roll it back ... No, we should, and if you want to know why, you need to read the second and third parts. Rolling back a transaction in the CATCH handler is a categorical imperative that has no exceptions.
The error re-generation code includes the following line:
The built-in error_message () function returns the text of the error that occurred. The next line reruns the error using the RAISERROR expression. This is not the easiest way to call an error, but it works. We will look at other ways in the next chapter.
Note : The syntax for assigning the initial value of a variable in DECLARE was implemented in SQL Server 2008. If you have SQL Server 2005, you need to break the string into DECLARE and a SELECT statement.
The final expression RETURN is insurance. RAISERROR never aborts its execution, so the execution of the next expression will continue. While all procedures use TRY-CATCH, and also all client code handles exceptions, there is no cause for concern. But your procedure can be called from old code written before SQL Server 2005 and before the TRY-CATCH implementation. In those days, the best we could do was look at the returned values. What you return with RETURN does not really matter if it is not a null value (zero usually indicates a successful completion).
The last expression in the procedure is END CATCH. You should never put any code after END CATCH. Someone reading the procedure may not see this piece of code.
After reading the theory, let's try a test example:
Result of performance:
Let's add an external procedure to see what happens when the error is recalled:
Result of work:
We received a correct error message, but if you look at the headers of this message and take a closer look at the previous one, you may notice a problem:
The error message displays the location of the final RAISERROR expression. In the first case, only the line number is incorrect. In the second case, the procedure name is also incorrect. For simple procedures, such as our test case, this is not a big problem. But if you have several levels of nested complex procedures, the presence of an error message with no indication of the place of its origin will make the search and elimination of the error a much more complicated matter. For this reason, it is desirable to generate an error so that you can determine if an erroneous piece of code is found quickly, and this is what we will look at in the next chapter.
We looked at the error_message () function, which returns the text of the error message. The error message consists of several components, and there is a function error_xxx () for each of them. We can use them to re-generate a complete message that contains the original information, albeit in a different format. If you do this in every CATCH handler, this will be a big disadvantage - duplication of code. You do not need to be in the CATCH block to call error_message () and other similar functions, and they will return the same information if they are called from a stored procedure that the CATCH block performs.
Let me introduce error_handler_sp:
The first of what error_handler_sp does is that it saves the value of all error_xxx () functions into local variables. I will return to the IF statement in a second. Instead, let's look at the SELECT statement inside the IF:
The purpose of this SELECT is to format the error message that is sent to RAISERROR. It includes all the information from the original error message, which we cannot insert directly into RAISERROR. We need to handle the name of the procedure, which may be NULL for errors in regular scripts or in dynamic SQL. Therefore, the COALESCE function is used. (If you do not understand the form of the expression RAISERROR, I tell about it in more detail in the second part.)
The formatted error message starts with three asterisks. This achieves two goals: 1) We can immediately see that this message is called from the CATCH handler. 2) This makes it possible for error_handler_sp to filter errors that have already been generated one or more times, using the NOT LIKE '***%' condition in order to avoid changing the message a second time.
Here is what the CATCH handler should look like when you use error_handler_sp:
Let's try some test scripts.
Result of performance:
The message headers indicate that the error occurred in the error_handler_sp procedure, but the text of the error messages gives us the real location of the error — both the name of the procedure and the line number.
I will show two more methods for calling errors. However, error_handler_sp is my main recommendation for readers who read this part. This is a simple option that works on all versions of SQL Server since 2005. There is only one drawback: in some cases, SQL Server generates two error messages, but the error_xxx () functions return only one of them, and therefore one of the messages is lost. This can be inconvenient when dealing with administrative commands like BACKUP \ RESTORE, but the problem rarely occurs in code that is purely for applications.
In SQL Server 2012, Microsoft introduced the expression; THROW for easier error handling. Unfortunately, Microsoft made a serious mistake when designing this team and created a dangerous trap.
With the expression; THROW you do not need any stored procedures. Your CATCH handler becomes as simple as this:
The advantage of THROW is that the error message is generated exactly the same as the original message. If there were initially two error messages, both messages are reproduced, which makes this expression even more attractive. As with all other error messages, errors generated by THROW can be intercepted by an external CATCH handler and replayed. If there is no CATCH handler, execution is interrupted, therefore the RETURN statement is not needed in this case. (I still recommend leaving it, in case you change your attitude towards; THROW later).
If you have SQL Server 2012 or later, change the definition of insert_data and outer_sp and try running the tests again. The result this time will be:
The procedure name and line number are correct and there is no other procedure name that can confuse us. Also saved the original error numbers.
At this point, you can tell yourself: did Microsoft really call the team; THROW? Isn't it just THROW? In fact, if you look in Books Online, there will not be a semicolon. But the semicolon should be. Officially, they separate the previous expression, but this is optional, and not everyone uses a semicolon in T-SQL expressions. More importantly, if you miss the semicolon before THROW, there will be no syntax error. But this will affect the behavior when the expression is executed, and this behavior will be incomprehensible to the uninitiated. If there is an active transaction, you will receive an error message that will be completely different from the original one. And even worse, in the absence of an active transaction, the error will be silently output without processing. Such a thing as the omission of a semicolon should not have such absurd consequences. To reduce the risk of such behavior, always think of a team as; THROW (with a semicolon).
There is no denying that; THROW has its advantages, but the semicolon is not the only trap of this command. If you want to use it, I encourage you to read at least the second part of this series, where I reveal more details about the team; THROW. Up to this point, use error_handler_sp.
The third way to handle errors is to use SqlEventLog, which I describe in great detail in the third part. Here I will only make a short review.
SqlEventLog provides a slog.catchhandler_sp stored procedure that works just like error_handler_sp: it uses the error_xxx () functions to collect information and displays an error message, storing all the information about it. In addition to this, it logs the error to the splog.sqleventlog table. Depending on the type of application that you have, this table can be a very valuable object.
To use SqlEventLog, your CATCH handler must be:
@@ procid returns the object ID of the current stored procedure. , SqlEventLog . , catchhandler_sp:
, , error_handler_sp, . , slog.sqleventlog:
SqlEventLog, sqleventlog.zip. , SqlEventLog.
. , 90-95% . , :
.
, .
, , : RETURN ( RETURN ).
, , , , BEGIN TRANSACTION.
, , , . : , . , , . .
, . , - . , .
: , SQL Server, :
Timeout expired ( SQL Server, API).
. , . , , SQL Server -.
… :
1. Introduction
This article is the first in a series of three articles on error and transaction handling in SQL Server. Its goal is to give you a quick start to the error handling thread by setting a basic example that fits most of your code. This part is written for an inexperienced reader, and for this reason I deliberately keep silent about many details. At the moment, the task is to tell how, without emphasis on why . If you take my words on faith, you can read only this part and put the other two aside for further steps in your career.
On the other hand, if you question my recommendations, you definitely need to read the other two parts, where I dive into the details much more deeply, exploring the very tangled world of error and transaction processing in SQL Server. The second and third parts, as well as the three applications, are intended for readers with deeper experience. The first article is short, the second and third are much longer.
')
All articles describe error and transaction handling in SQL Server for version 2005 and later.
1.1 Why do you need error handling?
Why do we handle errors in our code? There are many reasons for this. For example, on the forms in the application, we check the data entered and inform users about errors made during the input. User errors are foreseeable errors. But we also need to handle unexpected errors. That is, errors may occur due to the fact that we have missed something when writing code. A simple approach is to interrupt the execution or at least return to the stage in which we have complete control over what is happening. It will not be enough just to emphasize that it is completely impermissible to ignore unforeseen errors. This is a flaw that can cause disastrous consequences: for example, cause the application to provide incorrect information to the user or, even worse, store incorrect data in the database. It is also important to report the occurrence of an error so that the user does not think that the operation was successful, while your code actually did not perform anything.
We often want changes in the database to be atomic . For example, the task of transferring money from one account to another. To this end, we need to change two entries in the CashHoldings table and add two entries to the Transactions table. It is absolutely unacceptable that errors or failure led to the fact that the money will be transferred to the beneficiary’s account, and it will not be debited from the sender’s account. For this reason, error handling also applies to transaction processing. In the example above, we need to wrap the operation in BEGIN TRANSACTION and COMMIT TRANSACTION, but not only this: in case of an error, we have to make sure that the transaction has been rolled back.
2. Basic commands
We will begin with an overview of the most important commands that are required for error handling. In the second part, I will describe all the commands related to error handling and transactions.
2.1 TRY-CATCH
The main error handling mechanism is the TRY-CATCH construct, very much like similar constructions in other languages. The structure is as follows:
BEGIN TRY < > END TRY BEGIN CATCH < > END CATCH If any error appears in
< > , the execution will be transferred to the CATCH block, and the error handling code will be executed.As a rule, in CATCH any open transaction is rolled back and re-cause an error. Thus, the calling client program understands that something went wrong. Recalling the error will be discussed later in this article.
Here is a very quick example:
BEGIN TRY DECLARE @x int SELECT @x = 1/0 PRINT 'Not reached' END TRY BEGIN CATCH PRINT 'This is the error: ' + error_message() END CATCH Result of performance:
This is the error: Divide by zero error encountered.We will return to the error_message () function later. It should be noted that the use of PRINT in the CATCH handler is provided only as part of experiments and should not be done in the real application code.
If
< > calls a stored procedure or triggers triggers, then any error that occurs in them will pass the execution to the CATCH block. More specifically, when an error occurs, SQL Server spins the stack until it finds a CATCH handler. And if there is no such handler, SQL Server sends the error message directly to the client.There is one very important limitation in the TRY-CATCH construct that needs to be known: it does not catch compilation errors that occur in the same scope. Consider an example:
CREATE PROCEDURE inner_sp AS BEGIN TRY PRINT 'This prints' SELECT * FROM NoSuchTable PRINT 'This does not print' END TRY BEGIN CATCH PRINT 'And nor does this print' END CATCH go EXEC inner_sp Output:
This prints Msg 208, Level 16, State 1, Procedure inner_sp, Line 4 Invalid object name 'NoSuchTable' As you can see, the TRY block is present, but when an error occurs, execution is not transferred to the CATCH block, as expected. This applies to all compilation errors, such as column skipping, invalid aliases, and the like that occur during execution. (Compilation errors may occur in SQL Server at runtime due to deferred name resolution — a feature that allows SQL Server to create a procedure that refers to non-existent tables.)
These errors are not completely elusive; you cannot catch them in the area in which they arise, but you can catch them in the outer area. Add this code to the previous example:
CREATE PROCEDURE outer_sp AS BEGIN TRY EXEC inner_sp END TRY BEGIN CATCH PRINT 'The error message is: ' + error_message() END CATCH go EXEC outer_sp Now we get this output:
This prints The error message is: Invalid object name 'NoSuchTable'. This time the error was intercepted because the external CATCH handler worked.
2.2 SET XACT_ABORT ON
You should always add this expression to the beginning of your stored procedures:
SET XACT_ABORT, NOCOUNT ON It activates two session parameters that are turned off by default for compatibility with previous versions, but experience proves that the best approach is to have these parameters always on. The default behavior of SQL Server in the situation when TRY-CATCH is not used is that some errors interrupt execution and roll back any open transactions, while with other errors the execution of subsequent instructions continues. When you turn on XACT_ABORT ON, almost all errors begin to cause the same effect: any open transaction is rolled back, and the execution of the code is interrupted. There are a few exceptions, among which the most noticeable expression is RAISERROR.
The XACT_ABORT parameter is required for more reliable error handling and transaction processing. In particular, with the default settings, there are several situations where execution can be interrupted without any rollback of the transaction, even if you have a TRY-CATCH. We saw this example in the previous section, where we found out that TRY-CATCH does not intercept compilation errors that occurred in the same area. An open transaction that was not rolled back due to an error can cause serious problems if the application continues to work without completing the transaction or rolling back.
For reliable error handling in SQL Server, you need both TRY-CATCH and SET XACT_ABORT ON. Among them, the instruction SET XACT_ABORT ON is the most important. If it is not worth relying on it for code on an industrial environment, it is quite suitable for quick and simple solutions.
The NOCOUNT parameter has nothing to do with error handling, but including it in the code is a good practice. NOCOUNT suppresses messages of the form (1 row (s) affected), which you can see in the Message panel in SQL Server Management Studio. While these messages can be useful when working with SSMS, they can negatively affect application performance as they increase network traffic. The message about the number of rows can also lead to an error in poorly written client applications, which may think that this is the data that the query returned.
Above, I used a syntax that is a bit unusual. Most people would write two separate expressions:
SET NOCOUNT ON SET XACT_ABORT ON There is no difference between them. I prefer the version with SET and the comma, since This reduces the noise level in the code. Since these expressions should appear in all your stored procedures, they should take up as little space as possible.
3. Basic Error Handling Example
After we looked at TRY-CATCH and SET XACT_ABORT ON, let's connect them together in an example that we can use in all of our stored procedures. First, I will show an example in which an error is generated in a simple form, and in the next section I will look at solutions better.
For example, I will use this simple table.
CREATE TABLE sometable(a int NOT NULL, b int NOT NULL, CONSTRAINT pk_sometable PRIMARY KEY(a, b)) Here is a stored procedure that demonstrates how you should work with errors and transactions.
CREATE PROCEDURE insert_data @a int, @b int AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY BEGIN TRANSACTION INSERT sometable(a, b) VALUES (@a, @b) INSERT sometable(a, b) VALUES (@b, @a) COMMIT TRANSACTION END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH The first line in the procedure includes XACT_ABORT and NOCOUNT in one expression, as I showed above. This line is the only one before BEGIN TRY. Everything else in the procedure should be placed after BEGIN TRY: declaration of variables, creation of temporary tables, table variables, everything. Even if you have other SET-commands in the procedure (although the reasons for this are rare), they should go after BEGIN TRY.
The reason I prefer to specify SET XACT_ABORT and NOCOUNT before BEGIN TRY is that I see it as one line of noise: it should always be there, but I don’t want it to interfere with the look. Of course, this is a matter of taste, and if you prefer to put SET-commands after BEGIN TRY, do not worry. The important thing is that you should not put anything else in front of BEGIN TRY.
The part between BEGIN TRY and END TRY is the main component of the procedure. Since I wanted to use a user-defined transaction, I introduced a rather contrived business rule, which says that if you insert a pair, then the reverse pair must also be inserted. The two INSERT statements are inside BEGIN and COMMIT TRANSACTION. In many cases, you will have many lines of code between BEGIN TRY and BEGIN TRANSACTION. Sometimes you will also have code between COMMIT TRANSACTION and END TRY, although this is usually just a final SELECT that returns data or assigns values to output parameters. If your procedure does not perform any changes or has only one INSERT / UPDATE / DELETE / MERGE statement, then usually you don’t need to explicitly specify the transaction at all.
While the TRY block will look different from procedure to procedure, the CATCH block should be more or less the result of copying and pasting. So you do something short and simple and then use it everywhere, without really thinking. The CATCH handler above performs three actions:
- Rolls back any open transactions.
- Repeats an error.
- Ensures that the value returned by the procedure is non-zero.
These three actions should always be there. We can argue that the string
IF @@trancount > 0 ROLLBACK TRANSACTION not needed if there is no explicit transaction in the procedure, but this is absolutely wrong. Perhaps you are calling a stored procedure that opens a transaction, but which cannot roll it back due to TRY-CATCH restrictions. Perhaps you or someone else will add an explicit transaction after two years. Do you remember then that you need to add a line with a rollback? Do not count on it. I also hear readers who object that if the person calling the procedure has opened a transaction, we should not roll it back ... No, we should, and if you want to know why, you need to read the second and third parts. Rolling back a transaction in the CATCH handler is a categorical imperative that has no exceptions.
The error re-generation code includes the following line:
DECLARE @msg nvarchar(2048) = error_message() The built-in error_message () function returns the text of the error that occurred. The next line reruns the error using the RAISERROR expression. This is not the easiest way to call an error, but it works. We will look at other ways in the next chapter.
Note : The syntax for assigning the initial value of a variable in DECLARE was implemented in SQL Server 2008. If you have SQL Server 2005, you need to break the string into DECLARE and a SELECT statement.
The final expression RETURN is insurance. RAISERROR never aborts its execution, so the execution of the next expression will continue. While all procedures use TRY-CATCH, and also all client code handles exceptions, there is no cause for concern. But your procedure can be called from old code written before SQL Server 2005 and before the TRY-CATCH implementation. In those days, the best we could do was look at the returned values. What you return with RETURN does not really matter if it is not a null value (zero usually indicates a successful completion).
The last expression in the procedure is END CATCH. You should never put any code after END CATCH. Someone reading the procedure may not see this piece of code.
After reading the theory, let's try a test example:
EXEC insert_data 9, NULL Result of performance:
Msg 50000, Level 16, State 1, Procedure insert_data, Line 12 Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Let's add an external procedure to see what happens when the error is recalled:
CREATE PROCEDURE outer_sp @a int, @b int AS SET XACT_ABORT, NOCOUNT ON BEGIN TRY EXEC insert_data @a, @b END TRY BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION DECLARE @msg nvarchar(2048) = error_message() RAISERROR (@msg, 16, 1) RETURN 55555 END CATCH go EXEC outer_sp 8, 8 Result of work:
Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9 Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8). We received a correct error message, but if you look at the headers of this message and take a closer look at the previous one, you may notice a problem:
Msg 50000, Level 16, State 1, Procedure insert_data, Line 12 Msg 50000, Level 16, State 1, Procedure outer_sp, Line 9 The error message displays the location of the final RAISERROR expression. In the first case, only the line number is incorrect. In the second case, the procedure name is also incorrect. For simple procedures, such as our test case, this is not a big problem. But if you have several levels of nested complex procedures, the presence of an error message with no indication of the place of its origin will make the search and elimination of the error a much more complicated matter. For this reason, it is desirable to generate an error so that you can determine if an erroneous piece of code is found quickly, and this is what we will look at in the next chapter.
4. Three ways to generate errors
4.1 Using error_handler_sp
We looked at the error_message () function, which returns the text of the error message. The error message consists of several components, and there is a function error_xxx () for each of them. We can use them to re-generate a complete message that contains the original information, albeit in a different format. If you do this in every CATCH handler, this will be a big disadvantage - duplication of code. You do not need to be in the CATCH block to call error_message () and other similar functions, and they will return the same information if they are called from a stored procedure that the CATCH block performs.
Let me introduce error_handler_sp:
CREATE PROCEDURE error_handler_sp AS DECLARE @errmsg nvarchar(2048), @severity tinyint, @state tinyint, @errno int, @proc sysname, @lineno int SELECT @errmsg = error_message(), @severity = error_severity(), @state = error_state(), @errno = error_number(), @proc = error_procedure(), @lineno = error_line() IF @errmsg NOT LIKE '***%' BEGIN SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + ', Line ' + ltrim(str(@lineno)) + '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg END RAISERROR('%s', @severity, @state, @errmsg) The first of what error_handler_sp does is that it saves the value of all error_xxx () functions into local variables. I will return to the IF statement in a second. Instead, let's look at the SELECT statement inside the IF:
SELECT @errmsg = '*** ' + coalesce(quotename(@proc), '<dynamic SQL>') + ', Line ' + ltrim(str(@lineno)) + '. Errno ' + ltrim(str(@errno)) + ': ' + @errmsg The purpose of this SELECT is to format the error message that is sent to RAISERROR. It includes all the information from the original error message, which we cannot insert directly into RAISERROR. We need to handle the name of the procedure, which may be NULL for errors in regular scripts or in dynamic SQL. Therefore, the COALESCE function is used. (If you do not understand the form of the expression RAISERROR, I tell about it in more detail in the second part.)
The formatted error message starts with three asterisks. This achieves two goals: 1) We can immediately see that this message is called from the CATCH handler. 2) This makes it possible for error_handler_sp to filter errors that have already been generated one or more times, using the NOT LIKE '***%' condition in order to avoid changing the message a second time.
Here is what the CATCH handler should look like when you use error_handler_sp:
BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC error_handler_sp RETURN 55555 END CATCH Let's try some test scripts.
EXEC insert_data 8, NULL EXEC outer_sp 8, 8 Result of performance:
Msg 50000, Level 16, State 2, Procedure error_handler_sp, Line 20 *** [insert_data], Line 5. Errno 515: Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Msg 50000, Level 14, State 1, Procedure error_handler_sp, Line 20 *** [insert_data], Line 6. Errno 2627: Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8). The message headers indicate that the error occurred in the error_handler_sp procedure, but the text of the error messages gives us the real location of the error — both the name of the procedure and the line number.
I will show two more methods for calling errors. However, error_handler_sp is my main recommendation for readers who read this part. This is a simple option that works on all versions of SQL Server since 2005. There is only one drawback: in some cases, SQL Server generates two error messages, but the error_xxx () functions return only one of them, and therefore one of the messages is lost. This can be inconvenient when dealing with administrative commands like BACKUP \ RESTORE, but the problem rarely occurs in code that is purely for applications.
4.2. Usage; THROW
In SQL Server 2012, Microsoft introduced the expression; THROW for easier error handling. Unfortunately, Microsoft made a serious mistake when designing this team and created a dangerous trap.
With the expression; THROW you do not need any stored procedures. Your CATCH handler becomes as simple as this:
BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION ;THROW RETURN 55555 END CATCH The advantage of THROW is that the error message is generated exactly the same as the original message. If there were initially two error messages, both messages are reproduced, which makes this expression even more attractive. As with all other error messages, errors generated by THROW can be intercepted by an external CATCH handler and replayed. If there is no CATCH handler, execution is interrupted, therefore the RETURN statement is not needed in this case. (I still recommend leaving it, in case you change your attitude towards; THROW later).
If you have SQL Server 2012 or later, change the definition of insert_data and outer_sp and try running the tests again. The result this time will be:
Msg 515, Level 16, State 2, Procedure insert_data, Line 5 Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Msg 2627, Level 14, State 1, Procedure insert_data, Line 6 Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8). The procedure name and line number are correct and there is no other procedure name that can confuse us. Also saved the original error numbers.
At this point, you can tell yourself: did Microsoft really call the team; THROW? Isn't it just THROW? In fact, if you look in Books Online, there will not be a semicolon. But the semicolon should be. Officially, they separate the previous expression, but this is optional, and not everyone uses a semicolon in T-SQL expressions. More importantly, if you miss the semicolon before THROW, there will be no syntax error. But this will affect the behavior when the expression is executed, and this behavior will be incomprehensible to the uninitiated. If there is an active transaction, you will receive an error message that will be completely different from the original one. And even worse, in the absence of an active transaction, the error will be silently output without processing. Such a thing as the omission of a semicolon should not have such absurd consequences. To reduce the risk of such behavior, always think of a team as; THROW (with a semicolon).
There is no denying that; THROW has its advantages, but the semicolon is not the only trap of this command. If you want to use it, I encourage you to read at least the second part of this series, where I reveal more details about the team; THROW. Up to this point, use error_handler_sp.
4.3. Using SqlEventLog
The third way to handle errors is to use SqlEventLog, which I describe in great detail in the third part. Here I will only make a short review.
SqlEventLog provides a slog.catchhandler_sp stored procedure that works just like error_handler_sp: it uses the error_xxx () functions to collect information and displays an error message, storing all the information about it. In addition to this, it logs the error to the splog.sqleventlog table. Depending on the type of application that you have, this table can be a very valuable object.
To use SqlEventLog, your CATCH handler must be:
BEGIN CATCH IF @@trancount > 0 ROLLBACK TRANSACTION EXEC slog.catchhandler_sp @@procid RETURN 55555 END CATCH @@ procid returns the object ID of the current stored procedure. , SqlEventLog . , catchhandler_sp:
Msg 50000, Level 16, State 2, Procedure catchhandler_sp, Line 125 {515} Procedure insert_data, Line 5 Cannot insert the value NULL into column 'b', table 'tempdb.dbo.sometable'; column does not allow nulls. INSERT fails. Msg 50000, Level 14, State 1, Procedure catchhandler_sp, Line 125 {2627} Procedure insert_data, Line 6 Violation of PRIMARY KEY constraint 'pk_sometable'. Cannot insert duplicate key in object 'dbo.sometable'. The duplicate key value is (8, 8). , , error_handler_sp, . , slog.sqleventlog:
| logid | logdate | errno | severity | logproc | linenum | msgtext |
| one | 2015-01-25 22:40:24.393 | 515 | sixteen | insert_data | five | Cannot insert ... |
| 2 | 2015-01-25 22:40:24.395 | 2627 | 14 | insert_data | 6 | Violation of ... |
SqlEventLog, sqleventlog.zip. , SqlEventLog.
5.
. , 90-95% . , :
- , , , .
- , TRY-CATCH, RAISERROR .
- Linked Server , .
- INSERT-EXEC, , ROLLBACK TRANSACTION .
- , error_handler_sp SqlEventLog, , SQL Server . ;THROW .
.
, .
, , : RETURN ( RETURN ).
, , , , BEGIN TRANSACTION.
, , , . : , . , , . .
, . , - . , .
: , SQL Server, :
IF @@trancount > 0 ROLLBACK TRANSACTION Timeout expired ( SQL Server, API).
6.
. , . , , SQL Server -.
… :
SET XACT_ABORT, NOCOUNT ON Source: https://habr.com/ru/post/358936/
All Articles