FORMATMESSAGE() maximum output length

FORMATMESSAGE() is a quite useful competitor to RAISERROR() function, mainly in the case we won’t print the message directly but prepare it for further processing. One important limitation is there we must remember: The maximum output character length is limited to 2047 characters. When the final message is longer then it will be shortened to 2044 characters and three dots ellipsis is added to the end of the message string.

Let’s see it in the action:

DECLARE @Message NVARCHAR(MAX) = 'String1:%s, String2:%s, String3:%s'
DECLARE @StringA NVARCHAR(MAX)
DECLARE @StringB NVARCHAR(MAX)
DECLARE @StringC NVARCHAR(MAX)

SET @StringA = REPLICATE('A', 1000)
SET @StringB = REPLICATE('B', 1000)
SET @StringC = REPLICATE('C', 1000)

-- print the message
PRINT FORMATMESSAGE(@Message, @StringA, @StringB, @StringC)

-- get message string length
SELECT LEN(FORMATMESSAGE(@Message, @StringA, @StringB, @StringC))

-- compare to RAISERROR()
RAISERROR(@Message, 10, 1, @StringA, @StringB, @StringC)
GO

It is pretty well visible how the message was shortened from expected more than 3k characters to the 2044 characters maximum and ellipsis added. RAISERROR() returns exactly the same result.

Why does this matter? Consider an example where you would like to build i.e. JSON string and you will decide to build individual keys like this:

USE [msdb]
GO

DECLARE @Pattern NVARCHAR(MAX)

SET @Pattern = '{"Table_Id": %i, "Table_Name": "%s", "Columns": [ %s ] }'

SELECT 
	[a].[JSON_Row], LEN([a].[JSON_Row]) Characters
FROM (
		SELECT 
			FORMATMESSAGE(@Pattern, t.[object_id], t.[name], STRING_AGG('{ "Name": "' + c.[name] + '", "Datatype": "' + tp.[name] + '" }', ',')) JSON_Row
		FROM sys.[tables] t
			INNER JOIN sys.[columns] c ON [c].[object_id] = [t].[object_id]
			INNER JOIN sys. tp ON [tp].[system_type_id] = [c].[system_type_id] AND [tp].[user_type_id] = [c].[user_type_id]
		GROUP BY t.[object_id], t.[name]
	) a
ORDER BY Characters DESC
GO

Two rows marked exceeded the maximum 2044 allowed length for FORMATMESSAGE() output and their value was silently shortened in the background and if you will try to parse the JSON later you might get an error but not all the time, only when you hit the invalid part of the JSON (which makes things getting much worst then it looks like):

DECLARE @Pattern NVARCHAR(MAX)

SET @Pattern = '{"Table_Id": %i, "Table_Name": "%s", "Columns": [ %s ] }'

SELECT 
	-- JSON_VALUE([a].[JSON_Row], '$.Table_Id') -- This will work
	JSON_VALUE([a].[JSON_Row], '$.Columns') -- This will fail
FROM (
		SELECT 
			FORMATMESSAGE(@Pattern, t.[object_id], t.[name], STRING_AGG('{ "Name": "' + c.[name] + '", "Datatype": "' + tp.[name] + '" }', ',')) JSON_Row
		FROM sys.[tables] t
			INNER JOIN sys.[columns] c ON [c].[object_id] = [t].[object_id]
			INNER JOIN sys. tp ON [tp].[system_type_id] = [c].[system_type_id] AND [tp].[user_type_id] = [c].[user_type_id]
		GROUP BY t.[object_id], t.[name]
	) a
--WHERE LEN([a].[JSON_Row]) <= 2047 -- This will remove invalid rows and keep it finish
GO

It’s more than obvious that ignoring basic facts highlighted in the official SQL Server documentation may let you scratch the head for several hours:)