|
|
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:
SELECT * FROM sys.messagesCustom 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.
SELECT 5 / 0 SELECT @@ERRORSuccessfully 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:
SELECT 5 / 0 SELECT * FROM master.dbo.sysmessages WHERE error = @@ERRORLater 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:
SELECT alias, name, msglangid FROM sys.syslanguagesThe @@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.
--Try to do something BEGIN TRY SELECT 5 / 0 END TRY --If it causes an error, do this BEGIN CATCH PRINT ERROR_MESSAGE() END CATCHBe 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:
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.
BEGIN TRY SELECT 5/0 END TRY BEGIN CATCH PRINT 'Error Message=' PRINT ERROR_MESSAGE() PRINT 'Error Number=' PRINT ERROR_NUMBER() PRINT ‘Error Severity=’ PRINT ERROR_SEVERITY() PRINT ‘Error State=’ PRINT ERROR_STATE() PRINT ‘Error Line=’ PRINT ERROR_LINE() PRINT ‘Error Procedure=’ PRINT ERROR_PROCEDURE() END CATCHAs you can see, executing this script returns more detailed error information in the Messages tab than just the error number.
(0 row(s) affected) Error Message= Divide by zero error encountered. Error Number= 8134 Error Severity= 16 Error State= 1 Error Line= 2 Error Procedure=The ERROR_PROCEDURE() function did not return the procedure name because the error was generated in an ad-hoc query.
|
|
T-SQL Related Tutorials |
|
---|---|
SQL Database Tutorial | MySQL Tutorial |
DB2 Using SQL Tutorial | DocumentDB SQL Tutorial |
All rights reserved © 2020 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.