Ошибка conversion failed when converting from a character string to uniqueidentifier

First published on MSDN on Dec 07, 2017
Customer was receiving the following error:

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.

Here are all the ways that you can recreate this error:

use tempdb

create table t1 (cuid uniqueidentifier default NEWID(), cint int)

create table t2 (cuid_char varchar (20), cint int)

insert into t1 (cint) values (110)

insert into t2 values (‘asdfadfadsfadsfdsa’, 110)

—Straight convert/cast. You can see that passing an incorrect GUID fails

select convert(uniqueidentifier, ‘8EA7ADFA-F68D-49DC-A679-81D8D70E1497’) —this works just fine

select convert(uniqueidentifier, ‘8EA7ADFA-F68D-49DC-A679-81D8D70E14’) —this fails with the error

—In the WHERE clause. Comparing a non-GUID string to a uniqueidentifier raises the error

select * from t1

where cuid = ‘asdfadsfdsa’

—In a Join. Joining a uniqueidentifier column value to a char/varchar value fails

select * from t1 join t2 on

t1.cuid = t2.cuid_char

Based on the scenario that matches your case, troubleshoot accordingly. Essentially, need to fix the query, the data or the data type of your column.

Can you think of any other ways to cause this?

Namaste,

Joseph

Источник

Remove From My Forums

Question
I have an issue where a UNIQUEIDENTIFIER field contains a NULL value.

Essentially, I have a custom Scalar-Valued Function that accepts an NVARCHAR(50) parameter, and when attempting to use this function in a SELECT Statement I’m getting the error noted in the Title.

Example:

SELECT dbo.ListManual(CONVERT(NVARCHAR(50), ISNULL(LookupID, »))) AS Listing FROM EnrolleeExtensionBase

If the LookupID field (UNIQUEIDENTIFIER) contains a valid GUID, this works fine — however if it is NULL it is unable to do a conversion to a blank string.

I have tried to use COALESCE as follows:

SELECT COALESCE(CONVERT(NVARCHAR(50), LookupID), ») FROM EnrolleeExtensionBase

Which, returns a wonderful valid result set of GUIDs and blanks.

However, if I try to put the same logic as a parameter to my Function it again gives problems with converting:

SELECT dbo.ListManual(COALESCE(CONVERT(NVARCHAR(50), LookupID), »)) AS Listing FROM EnrolleeExtensionBase

Attempting to CAST or CONVERT the COALESCE results to a string does not have any effect on the resulting data type and still throws the same error with conversion.

Basically, this seems to be an issue with attempting to convert a GUID field with possible blank values to a string as an inline process.

Any ideas?

Answers

How about another guess. Is there somewhere in your function that you convert the uniqueidentifier (either explicitly or implicitly — for example if you compare the nvarchar(50) parameter to a uniqueidentifier variable or column, SQL will attempt to
convert the nvarchar to a uniqueidentifier (because uniqueidentifier has a higher precedence). If that is true, you are not getting the error in the function call, but somewhere in the function. For example, if I run
```
Create Table dbo.FooTable(FooGuid uniqueidentifier default NewID());
Insert dbo.FooTable(FooGuid) Default Values;
Insert dbo.FooTable(FooGuid) Values (Null);
go

Create Function dbo.FooFunction (@Input nvarchar(50)) Returns nvarchar(50) As
Begin
 Return @Input;
End
go

Select FooGuid, dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), '')) AS Listing 
From dbo.FooTable;

Select FooGuid, dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), '')) AS Listing 
From dbo.FooTable
Where FooGuid = dbo.FooFunction(COALESCE(CONVERT(NVARCHAR(50), FooGuid), ''));


go
Drop Function dbo.FooFunction;
Drop Table dbo.FooTable;
```
the first select runs fine, but the second select gets a conversion error because the nvarchar(50) result of an empty string is implicitly converted in the WHERE clause.

Tom
- Marked as answer by
  
  Thursday, March 3, 2011 7:17 AM

Источник

Remove From My Forums

Question
Hello,

I have to admit I am bad with SQL. I have a scenario where one of the TFS 2015 view stores the team project name as GUID which is of type nvarchar. Now I need to provide users name of the projects rather than ID in my report. I have another view in
TFS 2015 which gives me the name of the projects. I thought of writing a join statement for this two views and get the project names. So I went with this below query and just to keep you on the same page, team project name in the first view has GUID and name
as well. As the older projects are migrated from TFS 2012.

System.TeamProject has team project name (older project name from TFS 2012) and id for new projects (TFS 2015). So in short it has names and Id’s stored in it. I want to get only names as an output in my report to get further report set up.

SELECT * FROM [dbo].[vw_denorm_WorkItemCoreLatest] wi
INNER JOIN [dbo].vw_projects p ON wi.[System.TeamProject] = p.project_id
UNION ALL
SELECT * FROM [dbo].[vw_denorm_WorkItemCoreLatest] wi
INNER JOIN [dbo].vw_projects p ON wi.[System.TeamProject] = p.project_name

For now I have * in my query. I know the second part of the query works fine and return data for projects which has name stored. However those with guid id return the error.

Msg 8169, Level 16, State 2, Line 1 Conversion failed when converting from a character string to uniqueidentifier.

I have checked this issue online and saw many suggestions. However I am still not able to solve it. I understood one is nvarchar and project_id is uniqueidentifier. However what modification should I make in SQL query ?

First view:

Second view:

Thanks & Regards,

Ahetejazahmad Khan.

Ahetejazahmad Khan.

Answers

Ok, I have resolved this issue based on Vishe and Hilary inputs. Below is the correct query and correct type casting.

select distinct p.project_name, p.project_id from [dbo].[vw_projects] p
INNER JOIN [dbo].[vw_denorm_WorkItemCoreLatest] wi
on wi.[System.TeamProject] = convert(nvarchar(36), p.project_id)
OR wi.[System.TeamProject] = p.project_name

Hopefully it will be useful for others.

Thanks & Regards,

Ahetejazahmad Khan.

Ahetejazahmad Khan.
- Marked as answer by
  
  Monday, November 7, 2016 7:06 AM
- Unmarked as answer by
  Ahetejaz
  Monday, November 7, 2016 8:08 AM
- Marked as answer by
  Ahetejaz
  Monday, November 7, 2016 8:08 AM

Источник

This article describes the cause of the error as well as the solution.

You may face this error when exporting/importing a site or when working with web parts / widgets that list more than one page type. The full error contains an SQL query that joins all the page types in question, for example, like this eventdetails.txt.

This error is caused by at least two different page types (for example A and B) having a field named in the same way (for example FieldName) but in each page type the field data is stored as a different data type (for example for A it is ‘Text’ and for B it is ‘GUID’).

The best practice in this case is to use the page type code name as the Field name prefix. For example:

CustomPageTypeA_FieldName

How to find which fields in which page types are causing this conflict?

Update: new module «Page type fields data type mismatch» is available in the Kinspector since v3.3.0 that detects this mismatch (sample screen shot).

Executing this SQL query over your Kentico database you will get the list of field names and their count. This sample query is checking page types tables with prefix custom_ only and excluding custom tables. In the results, we are interested in those fields which count is higher than 1:

Copy the fields with countX > 2 and paste them into the second SQL query’s WHERE condition. This query is slightly modified to return the page type coupled data table and also data type of given fields:

As you can see in the image above, the title field is OK – the data type in both cases is the same. However, the teaserimage field has different data type. One is nvarchar (text field) and second is unique identifier (GUID field). From the query result you can see in which page types’ tables is this field defined so you can either rename the field or change its data type in the admin UI to fix the error.

Applies to Kentico 8.x and newer

Источник