1. 2014-01-08 18:11:22.87 spid3s SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file C:Program FilesMicrosoft SQL ServerMSSQL11.MSSQLSERVERMSSQLDATAPROD.mdf in database PROD (6). The OS file handle is 0x00098C.
  2. Nov 22, 2016 Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 12 SQL Server database books, 35Pluralsight courses and has written over 5200 articles on the database technology on his blog at a Along with 17+ years of hands-on experience, he holds a Masters of Science degree and a number of database certifications.
  3. Jan 10, 2018  This instance of SQL Server has been using a process ID of 1448 since 3:55:38 AM (local) 10:25:38 PM (UTC).

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

-->

Applies to:SQL Server (all supported versions) Azure SQL Managed InstanceAzure Synapse AnalyticsParallel Data Warehouse

Returns the state number of the error that caused the CATCH block of a TRY..CATCH construct to be run.

Syntax

Note

To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

Return Types

Sql

int

Return Value

When called in a CATCH block, returns the state number of the error message that caused the CATCH block to be run.

Returns NULL if called outside the scope of a CATCH block.

Retrieved 18 February 2012. Gunfighter songs by marty robbins.

Remarks

Some error messages can be raised at multiple points in the code for the Microsoft SQL Server Database Engine. For example, an '1105' error can be raised for several different conditions. Each specific condition that raises the error assigns a unique state code.

When viewing databases of known issues, such as the Microsoft Knowledge Base, you can use the state number to determine if the recorded issue might be the same as the error you have encountered. For example, if a Knowledge Base article discusses an 1105 error message with a state of 2, and the 1105 error message you received had a state of 3, your error probably had a different cause than the one reported in the article.

A SQL Server support engineer can also use the state code from an error to find the location in the source code where that error is being raised, which may provide additional ideas on how to diagnose the problem.

Sql Server Error 1474 State 1 3

ERROR_STATE may be called anywhere within the scope of a CATCH block.

ERROR_STATE returns the error state regardless of how many times it is run, or where it is run within the scope of the CATCH block. This is in contrast to functions like @@ERROR, which only returns the error number in the statement immediately after the one that causes an error, or in the first statement of a CATCH block.

In nested CATCH blocks, ERROR_STATE returns the error state specific to the scope of the CATCH block in which it is referenced. For example, the CATCH block of an outer TRY..CATCH construct could have a nested TRY..CATCH construct. Within the nested CATCH block, ERROR_STATE returns the state from the error that invoked the nested CATCH block. If ERROR_STATE is run in the outer CATCH block, it returns the state from the error that invoked that CATCH block.

Examples

A. Using ERROR_STATE in a CATCH block

The following example shows a SELECT statement that generates a divide-by-zero error. The state of the error is returned.

Kindle

Sql Server Error 1474 State 1 Key

B. Using ERROR_STATE in a CATCH block with other error-handling tools

The following example shows a SELECT statement that generates a divide-by-zero error. Along with the error state, information that relates to the error is returned.

Examples: Azure Synapse Analytics (SQL Data Warehouse) and Parallel Data Warehouse

C. Using ERROR_STATE in a CATCH block with other error-handling tools

The following example shows a SELECT statement that generates a divide-by-zero error. Along with the error state, information that relates to the error is returned.

See Also

sys.messages (Transact-SQL)
TRY..CATCH (Transact-SQL)
ERROR_LINE (Transact-SQL)
ERROR_MESSAGE (Transact-SQL)
ERROR_NUMBER (Transact-SQL)
ERROR_PROCEDURE (Transact-SQL)
ERROR_SEVERITY (Transact-SQL)
RAISERROR (Transact-SQL)
@@ERROR (Transact-SQL)
Errors and Events Reference (Database Engine)