SQLTech Consulting

I received this error today while updating an extended property so I thought it would be a good little post to help others in the event that they come accross the same error as well.   Extended properties can be used for a great deal more then just for basic data dictionaries.  I’m currently working on a project where they are extensively using SMO for code generation and they store and engineer their systems information about the sql objects they generate on the extended properties of those sql objects.  While I was deploying a change today I got the following error

“Operand type clash: nvarchar(max) is incompatible with sql_variant ” 

This error seemed to have came about due to an upper size limit on the extended properties.   The default way of passing strings to the parameters in Unicode format as follows:

EXEC sys.sp_addextendedproperty
@name = N'MS_DescriptionExample',
@value = N'AdventureWorks Sample OLTP Database';

While this is pretty much how you will see all of the examples, you can convert the variant to pretty much any datatype you like except N)TEXT, IMAGE, TIMESTAMP and SQL_VARIANT.  Since the extended property I was using was passed as Unicode or NVARCHAR(MAX)  it was doubling the size and I ran into the 7,500 byte limit which was 3750 characters while in UNICODE.  To solve the error I  shrunk down the extended property to  a smaller character limit.    So if you get this type of error while working with extended properties take a look at your variables and rememebr that SQL converts them to Unicode which doubles the size of your strings and there is a limit of 7500 bytes.