For ERROR_NUMBER 515, how to get the column name from ERROR_MESSAGE

Question

In many SQL error messages, SQL Server does not mention column name. But in messages such as the one shown below, the error message does include the column name.

Question: How can we extract the column name from the following error message?

When inserting data into a row in SQL Server table, you get following error number 515 if you are not using a NOT NULL column name in the insert statement:

Msg 515:

Cannot insert the value NULL into column 'LastName', table 'CustomerDB.dbo.CustomerTable'; column does not allow nulls. INSERT fails.

Goal: To log the above error in a log table as: LastName is required

the Log table has Primary Key column and an Error Description column. The requirement is to make the Error description brief, user friendly, and more focused for a non-technical person.

SELECT SUBSTRING(ERROR_MESSAGE, 43, CHARINDEX('''', ERROR_MESSAGE, 43) - 43)????? — Jonathan Willcock
– Jonathan Willcock, Commented Aug 9 at 4:27
How is the non-technical person interfacing with the DB? (SSMS ,Wordpress or something else?) — P.Salmon
– P.Salmon, Commented Aug 9 at 6:15
To make it user friendly, why do you want to allow the customer to insert nulls at all? The best way is to validate ahead of time — siggemannen
– siggemannen, Commented Aug 9 at 7:51

Martin Smith · Accepted Answer · 2025-08-09 13:56:35Z

5

This needs to be parsed out of the error text.

This may or may not be localised to the language of the logged in user depending on platform (Azure SQL database doesn't bother with this)

You can review the versions of this message available with

SELECT text,
       l.name as language
FROM sys.messages m
JOIN sys.syslanguages l ON l.msglangid = m.language_id
WHERE m.message_id = 515

to see the message text around the place holders in each language.

In SQL Server 2025 this will probably be easier with Regex.

An example with a deliberately perversely named column is below

SET NOCOUNT ON;

IF @@LANGUAGE NOT IN ('us_english', 'British') AND @@VERSION NOT LIKE 'Microsoft SQL Azure%'
    THROW 50000, 'Parsing expects English Language message', 1;


DECLARE @Tbl TABLE
(
[Cannot insert the value NULL into column 'foo', table 'bar'; 
column does not allow nulls. INSERT fails.] INT NOT NULL
)

BEGIN TRY
INSERT @Tbl VALUES (NULL)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() <> 515
    THROW;

PRINT REGEXP_REPLACE(ERROR_MESSAGE(), 
       '\ACannot insert the value NULL into column ''(.+)'', table ''(.+)''; column does not allow nulls\. (.+) fails\.\z', 
       '\1',
        1,
        1,
        's'
       ) 
END CATCH

In Azure SQL database this outputs the expected column name element

Cannot insert the value NULL into column 'foo', table 'bar';
column does not allow nulls. INSERT fails.

For SQL Server 2022 you will need to consider whether you care about the possibility of column names with single quotes or other elements of the message or not.

Maybe the suggestion already given (by @Jonathan Willcock) in the comments of

SUBSTRING(ERROR_MESSAGE(), 43, CHARINDEX('''', ERROR_MESSAGE(), 43) - 43)

would be "good enough"

edited Aug 9 at 13:56

answered Aug 9 at 8:12

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jonathan Willcock Aug 9 at 10:06

You are right. I didn't take the idiots, who put single quotes in column names, into account! I tend to shoot such people; the only sure way to safeguard the survival of the human race. 😁

Michał Turczyn · Accepted Answer · 2025-08-10 07:04:38Z

You could do some basic string manipulation in order to get the failed column name by looking for a column word and extracting based on found indexes.

Below is sample T-SQL script that handles that:

CREATE TABLE CustomerTable
(
    FirstName MVARCHAR(100) NOT NULL,
    LastName MVARCHAR(100) NOT NULL
)
GO

BEGIN TRY
    -- Force the error for demo
    INSERT INTO CustomerTable (FirstName)
    VALUES ('John');
END TRY
BEGIN CATCH
    DECLARE @FullMsg NVARCHAR(MAX) = ERROR_MESSAGE();
    DECLARE @ColName NVARCHAR(255);

    -- Find starting position of column name
    DECLARE @Start INT = CHARINDEX('column ''', @FullMsg) + LEN('column ''');
    -- Find ending position after the column name
    DECLARE @End INT = CHARINDEX('''', @FullMsg, @Start);

    -- Extract the column name
    SET @ColName = SUBSTRING(@FullMsg, @Start, @End - @Start);

    -- Insert into your log table in user-friendly form
    SELECT (@ColName + ' is required');
END CATCH;

Then you can even try creating function with that logic and use as one liner.

Collectives™ on Stack Overflow

For ERROR_NUMBER 515, how to get the column name from ERROR_MESSAGE

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related