Casting UniqueIdentifier to Varchar

I had the following code which gave an error message:

BEGIN TRAN

declare @machienid varchar
set @machienid = cast(NEWID() as varchar)
select @machienid

ROLLBACK

 

The error message was:

Msg 8170, Level 16, State 2, Line 4
Insufficient result space to convert uniqueidentifier value to char.

The default length for varchar is 30, and there is a chance of data overflow.  UniqueIdentifer has length of 36 characters.  So by specifying the length of the varchar variable we can overcome this issue.

Thanks goes to Pinal Dave’s excellent blog about SQL:

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

Advertisements

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