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 (http://blog.sqlauthority.com)

About these ads

7 thoughts on “SQL SERVER – FIX: ERROR: 8170 Insufficient result space to convert uniqueidentifier value to char

  1. 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

  2. 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.

  3. 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.

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