I just came across very simple error and the solution was even simpler. While concatenating NEWID to another varchar string, I had to CONVERT/CAST it to VARCHAR and I accidentally put length of VARCHAR to 10 instead of 36. It displayed following error.
Msg 8170, Level 16, State 2, Line 1
Insufficient result space to convert uniqueidentifier value to char.
The code which I had ran earlier was as following.
SELECT 'GeneratedID:'+CAST(NEWID() AS VARCHAR(10)) AS NEW_ID
Solution:
The solution of above error is extremely simple. I just increased the length from VARCHAR(10) to VARCHAR(36) and it worked fine.
SELECT 'GeneratedID:'+CAST(NEWID() AS VARCHAR(36)) AS NEW_ID
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
I recently found out you don’t have supply a varchar length when using a convert and love the idea of not ensure one has the right length. Any reason why not to use the following?
SELECT ‘GeneratedID:’+CONVERT(VARCHAR,NEWID()) AS NEW_ID
If you leave length out of CAST or CONVERT it uses default length value which is 30. So GUID can’t be converted this way. You have to specify length at least 32 characters.
I must add that I really hate this kind of optional things which you can leave out but which can lead to undesired results. Better that length be mandatory between 1 and MAX. Ugh. I have spoken.
The default length depends on the usage. Make sure to read this blog post
Hi Erik,
If you do not supply length, then it would be default to 30 in case of CAST and CONVERT and you need 36 chars for GUID.
Also, in case when we do variable decalration as VARCHAR, without lenght the default lengh is 1 char.
So, you can not use VARCHAR without using lenght in this case
-Chintak.
Using default length has caused me issues before, it is good to be explicit while specifying lengths in CONVERT/CAST function. Couldn’t agree with MArko more.
These comments really helped me to solve a problem at work. Much thanks.
Thank you very much for your post. Saved me a lot of time.
This doesn’t always work – it depends on the source column and for some reason CONVERT works different than CAST – in my case my column is bigint and when applying isnull with CAST varchar(36) it still gives error.
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