different state on the same error message

Question

different state on the same error message

DamianC

SSCrazy Eights

Points: 8023
More actions
October 3, 2019 at 9:10 am

#3685598

Hello

On nearly all of our servers if I generate a string or binary data would be truncated error (Msg 8152), it returns a State of 14.
I now have one server where it returns 30

Msg 8152, Level 16, State 30, Line 108
String or binary data would be truncated.
The statement has been terminated.

What causes this difference as this does not appear in sys.messages?
Is it something we can easily change?

Thanks
- Damian

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 1

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.

https://docs.microsoft.com/en-us/sql/t-sql/functions/error-state-transact-sql?view=sql-server-2017

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

DamianC SSCrazy Eights Points: 8023 More actions · Answer 2

Thanks

Does it store these states anywhere though?

I am using unit tests, specifically negative unit tests (trip the catch part of an SP)

The unit test code has been editted to look for State 14 but this is now 30 so the test fails

Is it a server setting, a table setting or ... i.e. can I amend this to 14 in one place

- Damian

Brahmanand Shukla SSC Eights! Points: 990 More actions · Answer 3

http://www.sommarskog.se/error_handling/Part2.html

A value between 0 and 255 that may give more information about the underlying cause for the error. An important example is message 18456, Login failed for user '%s'. In the message displayed to the user, the state is always 1, but in the SQL Server error log you can find other state values, which explains why the login failed, as discussed in this blog post from Il-Sung Lee at Microsoft, and further elaborated in this blog post from SQL Server MVP Aaron Bertrand. However, for most other SQL Server errors, state numbers are usually not documented, so it is not that often you have use for them. But as we shall see later, you can use them for your own error messages as well.

https://blogs.msdn.microsoft.com/sql_protocols/2006/02/21/understanding-login-failed-error-18456-error-messages-in-sql-server-2005/

SQL Server Carpenter - Consultant & Trainer
Author of Learn T-SQL from Scratch

Sue_H SSC Guru Points: 90860 More actions · Answer 4

DamianC wrote:

Thanks
Does it store these states anywhere though?
I am using unit tests, specifically negative unit tests (trip the catch part of an SP)
The unit test code has been editted to look for State 14 but this is now 30 so the test fails
Is it a server setting, a table setting or ... i.e. can I amend this to 14 in one place

No it's not exposed anywhere or something where you can change the state. It's happening at different points for different reasons. One option with SQL Server 2016 is that if you are on SP2 CU 6 or higher, you can enable a trace flag that would give you more information on the truncation. That may give you a better idea of what is going on with the error. The following article describes this change:

Improvement: Optional replacement for "String or binary data would be truncated" message with extended information in SQL Server 2016 and 2017

Sue

DamianC SSCrazy Eights Points: 8023 More actions · Answer 5

DamianC

SSCrazy Eights

Points: 8023

October 3, 2019 at 1:53 pm

#3685708

Thanks Sue

I'll take a look

- Damian

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 6

DamianC wrote:

Does it store these states anywhere though?

State is not really a part of the error itself, but extra information that is added when RAISERROR is called that can help show where in code it was called.

If it's your stored procedure that's calling RAISERROR or THROW, then that call can have different states, but if your call is just an INSERT or UPDATE and you are using default error handling, then you won't be able to control the state value.

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-2017

DamianC SSCrazy Eights Points: 8023 More actions · Answer 7

DamianC

SSCrazy Eights

Points: 8023

March 10, 2020 at 10:44 am

#3732742

Thanks Chris

- Damian

OrangeBronco Newbie Points: 1 More actions · Answer 8

Chris Harshman wrote:

DamianC wrote:
Does it store these states anywhere though?
State is not really a part of the error itself, but extra information that is added when RAISERROR is called that can help show where in code it was called.
If it's your stored procedure that's calling RAISERROR or THROW, then that call can have different states, but if your call is just an INSERT or UPDATE and you are using default error handling, then you won't be able to control the state value.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2018
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-2017-link

Thank you for posting something like this

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 9

OrangeBronco wrote:

Chris Harshman wrote:
DamianC wrote:
Does it store these states anywhere though?
State is not really a part of the error itself, but extra information that is added when RAISERROR is called that can help show where in code it was called.
If it's your stored procedure that's calling RAISERROR or THROW, then that call can have different states, but if your call is just an INSERT or UPDATE and you are using default error handling, then you won't be able to control the state value.
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql?view=sql-server-2018
https://docs.microsoft.com/en-us/sql/t-sql/language-elements/throw-transact-sql?view=sql-server-2017-link
Thank you for posting something like this

Yeah... like what? I'm thinking that you just completed a spam test.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)