Configuration Variables - T-SQL

These aren't really functions, but they can be used in much the same way as system functions. Each global variable returns scalar information about the SQL Server execution environment. Following are some common examples.

The @@ERROR Variable

This variable contains the last error number for the current connection. The value for @@ERROR is 0 when a statement is executed with no errors. Errors are raised by the database engine when standard error conditions occur. All the standard error numbers and messages are viewable from the sys.messages system view and can be queried using the following script:

Custom errors can be raised manually using the RAISERROR statement and can be added to the sysmessages table using the sp_addmessage system stored procedure.

Following is a simple example of the @@ERROR variable. First I try to divide a number by 0. This causes the database engine to raise the standard error number 8134. Make sure to look at the Results tab to see the results of the query. By default, when an error occurs the Messages tab of Management studio is shown on top of the Results tab.

Successfully retrieving the value of @@ERROR causes the value of @@ERROR to return to 0 because @@ERROR holds only the error number for the previously executed statement. If I want to retrieve additional error information, I could get it from the sys.messages view using the following script:

Later in this section I'll show you how to use error functions for a more efficient way of returning error data.

SQL Server is installed by default with languages in addition to U.S. English. Each language-specific error message has a language identifier (msglangid) that corresponds to a language in the sys languages table, as shown in Figure (Show In Below).

In case you were wondering, the attribute name msglangid has been unofficially defined as "Microsoft Global Language Identifier.” Microsoft uses this identifier to identify a language or a combination of a language and a country, which Microsoft defines as a locale. For instance, the English language installed with SQL Server is United States English with amsglangid of 1033, as opposed to United Kingdom English with a msglangid of 2057. The following table describes the language identifiers installed by default with SQL Server 2008. The only difference in SQL Server 2005 is the absence of msglangid 1046 (Portuguese).

To retrieve a list of all installed and supported languages, execute the following query:

The @@SERVICENAME Variable

This is the name of the Windows service used to execute and maintain the current instance of SQL Server. This will typically return the value MSSQLSERVER for the default instance of SQL Server. However, named instances of SQL Server have uniquely named service names. For example, on my computer named WoodVista, I have two instances of SQL Server: a default instance and a named instance called AughtEight. Retrieving the contents of the @@SERVICENAME global variable on the default instance returns MSSQLSERVER, but on the named instance it returns AUGHTEIGHT

The @@TOTAL_ERRORS Variable

This is the total number of errors that have occurred since the current connection was opened. As with the @@ERROR variable, this is unique for each user session and is reset when each connection closes.

The @@TOTAL_READ Variable

This is a count of the total disk read operations that have occurred since the current connection was opened. This variable is interesting to the DBA to see disk read activity over time.

The @@VERSION Variable

This variable contains the complete version information for the current instance of SQL Server.

SELECT @@VERSION

For example, for an instance of SQL Server 2005 Developer Edition running on Windows XP, this script returns the following:

Microsoft SQL Server 2005 - 9.00.3050.00 (Intel X86) Mar 2 2007 20:01:28 Copyright (c) 19 88-2 005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

For an instance of SQL Server 2008 (RTM) Developer Edition running on Windows Vista, the script returns the following:

Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 6.0 <X86> (Build 6000: )

The actual version number, used internally at Microsoft, is a simple integer value, although released products may have other branded names. In this case, SQL Server 2005 is really version 9, and SQL Server 2008 is version 10. Windows XP Professional shows up as Windows NT version 5.1 and Vista shows up as 6.0. The build number is used for internal control and reflects changes made in beta and preview product releases, and post-release service packs.

Error Functions

Previously, you learned how to use the @@ERROR global variable to retrieve error information. However, a better way of returning all the available error data is by using error functions. The information returned by these functions can be returned and stored in an error tracking table for error auditing. The error functions are nested inside an error-handling routine. We'll discuss error handling in more detail Chapter(Advanced Capabilities), but it is implemented by using a block of code nested inside a TRY and END TRY statement, followed by a block of code inside a CATCH and END CATCH statement.

Be advised that when we speak of error catching, that is exactly what is meant. Running the previous example causes no discernable error because the error was caught and "handled" within the catch block. It is imperative that when writing error handling code that the SQL programmer includes in the catch block code that will raise a system error, if that is appropriate. Error handling is covered in detail in Chapter (T-SQL Programming Objects).

There are several error functions that return specific information about the error:

• ERROR_MESSAGE() — Returns the description of the error.
• ERROR_NUMBER() — Returns the number of the error.
• ERROR_SEVERITY() — Returns the error severity. Error severity is an integer value ranging from 0 to 25.The following table briefly describes the severity levels:



• ERROR_STATE() — Returns the error state number. The error state is an integer value that can be used to uniquely identify the cause of a system error.
• 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.

The following script uses T-SQL's built-in error handling to capture and print the error data returned when a divide by 0 is encountered. The results of the PRINT command are shown in the Messages tab of Management Studio.

As you can see, executing this script returns more detailed error information in the Messages tab than just the error number.

The ERROR_PROCEDURE() function did not return the procedure name because the error was generated in an ad-hoc query.