Recently when I was trying to convert a UNIQUEIDENTIFIER to CHAR, I got an error like
Insufficient result space to convert uniqueidentifier value to char
My statement was something like
SELECT CONVERT(CHAR, NEWID())
I have faced this issue many times and resolved it too but keep on forgetting the fix. And every time I had to surf the web for fix. So thought of logging it in my blog.
When you convert from one data type to another (whether you use CAST or CONVERT), most of the time no need to specify the length of the data type.
Example when you convert a DATE to CHAR, no need to mention the length. Simply say which data type you want to convert. In the present case, CHAR.
SELECT CONVERT(CHAR, GETDATE(), 110)
This is same when you convert from numeric to char etc..
But when UNIQUEIDENTIFIER is converted to string, then you need to mention the length explicitly. The length of the UNIQUEIDENTIFIER after conversion is 36 bytes.
So our statement should like
SELECT CONVERT(CHAR(36), NEWID())