Configuration Variables


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.

@@ERROR

This variable contains the last error number for the current connection. The default value for @@ERROR is zero. Errors are raised by the database engine when standard error conditions occur. All of the standard error numbers and messages are stored in the sysmessages table and can be queried using the following script:

 SELECT * FROM master.dbo.sysmessages 

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 zero. This causes the database engine to raise the standard error number 8134.

 SELECT 5 / 0 SELECT @@ERROR 

Successfully retrieving the value of @@ERROR causes the value of @@ERROR to return to zero. This because @@ERROR only holds the error number for the previously executed statement. If I want to retrieve additional error information, I can get it from the sysmessages table (or view it in SQL Server 2005) using the following script:

 SELECT 5 / 0 SELECT * FROM master.dbo.sysmessages WHERE error = @@ERROR 

Executing this script returns more detailed error information from the sysmessages table shown in Figure 6-1.

image from book
Figure 6-1:

If I had installed SQL Server with languages in addition to U.S. English, additional messages would be listed. Each language-specific error message has a language identifier (mslangid), which corresponds to a language in the syslanguages table.

@@SERVICENAME

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. Non-default instances (if you were to install SQL Server more than once or choose to install it as a named instance) have uniquely named service names.

@@TOTAL_ERRORS

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

@@TOTAL_READ

This is a count of the total read operations that have occurred since the current connection was opened.

@@VERSION

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

 SELECT @@VERSION 

For example, for an instance of SQL Server 2000, this script returns the following:

Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05  Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1  (Build 2600: Service Pack 2)

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 2000 is really version 8. Windows XP Professional shows up as Windows NT version 5.1. The build number is used for internal control and reflects changes made in beta and preview product releases, and post-release service packs. Here is an example of the output for SQL Server 2005 running on Windows Server 2003:

Microsoft SQL Server 2005 - 9.00.1090 (Intel X86)     Feb 21 2005 03:39:52     Copyright (c) 1988-2004 Microsoft Corporation     Enterprise Edition on Windows NT 5.2 (Build 3790: )




Beginning Transact-SQL with SQL Server 2000 and 2005
Beginning Transact-SQL With SQL Server 2000 and 2005
ISBN: 076457955X
EAN: 2147483647
Year: 2006
Pages: 131
Authors: Paul Turley

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net