SQL SERVER – FIX: ERROR: 8170 Insufficient result space to convert uniqueidentifier value to char

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)

SQL Error Messages, SQL Scripts, SQL Server
Previous Post
SQLAuthority News – Last 2 Day to Win MSDN Subscription – Total 2 to Win
Next Post
SQL SERVER – Computed Column – PERSISTED and Storage

Related Posts

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

    Reply
    • Marko Parkkola
      July 26, 2010 12:26 pm

      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.

      Reply
    • The default length depends on the usage. Make sure to read this blog post

      Reply
  • 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.

    Reply
  • 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.

    Reply
  • These comments really helped me to solve a problem at work. Much thanks.

    Reply
  • pimrampai vannacharoen
    December 25, 2013 1:06 pm

    Thank you very much for your post. Saved me a lot of time.

    Reply
  • 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

    Reply

Leave a Reply

Menu
Exit mobile version