Sql Server – When ISNULL results in “Insufficient result space to convert” …

This is a common error when doing null handling when replacing incoming value with something larger since the datatype defaults based on the incoming value.

You need to not just CAST or CONVERT to a different larger datatype but once but you have do it twice – both on the incoming column and then on the result. This can also happen when doing aggregations as well as it may demand overriding the default decimal or integer size for the resulting value.

Below demonstrates a further complication where CAST doesn’t work but CONVERT does…

Following doesn’t work…
SELECT CAST(ISNULL(CAST([Person_Acxiom_Abilitec_Id] AS VARCHAR(36)), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id…

Following does…
CAST(ISNULL(CONVERT(VARCHAR(36),[Person_Acxiom_Abilitec_Id]), NEWID()) AS VARCHAR(36)) AS Person_Acxiom_Abilitec_Id

The only difference is using CONVERT instead of CAST.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s