Ошибка при преобразовании строки символов в тип uniqueidentifier - ErrorsMaster.ru

Created a stored procedure in SQL 9 (2005) and have since upgraded to SQL 10 (2008). Since then, the following stored procedure has stopped working and thrown up the above error:

ALTER PROCEDURE [dbo].[GetModifiedPages] 
    @vPortalUID         nvarchar(32) = ''
AS
BEGIN
    -- Convert GUID to UI
    DECLARE @nPortalUID AS uniqueidentifier
    SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)

The passed in param @vPortalUID contains: 2A66057D-F4E5-4E2B-B2F1-38C51A96D385. I execute the stored proc like this:

EXEC GetModifiedPages '2A66057D-F4E5-4E2B-B2F1-38C51A96D385'

It falls over. I have tried Convert aswell. Still no joy. Have also had the value going in with { } around it. I removed these programatically and manually as above.

If you are interested I am running the SP from an ASP Classic page, although that should not affect this as the above code was run using SSMS.

Thanks in advance for your help.
James

asked Sep 25, 2009 at 11:49

jamesmhaleyjamesmhaley

44.4k11 gold badges35 silver badges49 bronze badges

this fails:

 DECLARE @vPortalUID NVARCHAR(32)
 SET @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
 DECLARE @nPortalUID AS UNIQUEIDENTIFIER
 SET @nPortalUID = CAST(@vPortalUID AS uniqueidentifier)
 PRINT @nPortalUID

this works

 DECLARE @vPortalUID NVARCHAR(36)
 SET @vPortalUID='2A66057D-F4E5-4E2B-B2F1-38C51A96D385'
 DECLARE @nPortalUID AS UNIQUEIDENTIFIER
 SET @nPortalUID = CAST(@vPortalUID AS UNIQUEIDENTIFIER)
 PRINT @nPortalUID

the difference is NVARCHAR(36), your input parameter is too small!

Emma Thapa

7471 gold badge7 silver badges14 bronze badges

answered Sep 25, 2009 at 12:03

Источник

You have to check unique identifier column and you have to give a diff value to that particular field if you give the same value it will not work. It enforces uniqueness of the key.

Here is the code:

Insert into production.product 
(Name,ProductNumber,MakeFlag,FinishedGoodsFlag,Color,SafetyStockLevel,ReorderPoint,StandardCost,ListPrice,Size
,SizeUnitMeasureCode,WeightUnitMeasureCode,Weight,DaysToManufacture,
    ProductLine, 
    Class, 
    Style ,
    ProductSubcategoryID 
    ,ProductModelID 
    ,SellStartDate 
,SellEndDate 
    ,DiscontinuedDate 
    ,rowguid
    ,ModifiedDate 
  )
  values ('LL lemon' ,'BC-1234',0,0,'blue',400,960,0.00,100.00,Null,Null,Null,null,1,null,null,null,null,null,'1998-06-01 00:00:00.000',null,null,'C4244F0C-ABCE-451B-A895-83C0E6D1F468','2004-03-11 10:01:36.827')

Источник

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

Источник

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

Источник

If you are a developer, you might have encountered the error message «Conversion failed when converting from a character string to a uniqueidentifier» while working with SQL Server. This error message is usually associated with the conversion of a character string into a uniqueidentifier. The conversion failed due to the invalid format of the uniqueidentifier value.

In this guide, we will provide you with troubleshooting tips to fix the «Conversion failed when converting from a character string to a uniqueidentifier» error in SQL Server.

Troubleshooting Tips

Check the Value Type

The first and foremost thing to do is to check the value type of the uniqueidentifier column. Ensure that the value type of the uniqueidentifier column is a uniqueidentifier data type. If it is not, then you need to convert it to a uniqueidentifier data type.

Check the Value Format

Make sure that the value format of the uniqueidentifier column is correct. A uniqueidentifier column value should be in the format of XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX, where X can be a hexadecimal digit. If the format doesn’t match, then you need to convert it to the correct format.

Check the Value Length

Ensure that the value length of the uniqueidentifier column is correct. A uniqueidentifier column value should be 36 characters long, including hyphens. If the length is incorrect, then you need to adjust it to the correct length.

Use TRY_CAST or TRY_CONVERT

You can use the TRY_CAST or TRY_CONVERT function to convert a character string to a uniqueidentifier. These functions will return null if the conversion fails, which allows you to handle the error gracefully.

SELECT TRY_CAST('ABC12345-6789-0123-ABCD-1234567890AB' AS uniqueidentifier) AS result;

Use ISNULL Function

If the uniqueidentifier column contains null values, then you can use the ISNULL function to replace the null values with a default value.

SELECT ISNULL(uniqueidentifier_column, '00000000-0000-0000-0000-000000000000') AS result;

FAQ

Q1. What is a uniqueidentifier?

A uniqueidentifier is a 16-byte binary value that is represented as a string of 36 characters. It is used to store a globally unique identifier (GUID) in SQL Server.

Q2. Why do I get «Conversion failed when converting from a character string to a uniqueidentifier» error?

You might get this error message when you try to insert or update a uniqueidentifier column with an invalid format or length.

Q3. How do I convert a character string to a uniqueidentifier?

You can use the TRY_CAST or TRY_CONVERT function to convert a character string to a uniqueidentifier.

Q4. How do I handle null values in a uniqueidentifier column?

You can use the ISNULL function to replace the null values with a default value.

Q5. How do I check the format and length of a uniqueidentifier column?

You can use the following query to check the format and length of a uniqueidentifier column:

SELECT uniqueidentifier_column, 
       LEN(uniqueidentifier_column) AS length,
       CASE WHEN uniqueidentifier_column LIKE '[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9]-[A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9][A-F0-9]' THEN 'Valid' ELSE 'Invalid' END AS format
FROM table_name;

Conclusion

In conclusion, the «Conversion failed when converting from a character string to a uniqueidentifier» error is a common issue when working with SQL Server. However, with the troubleshooting tips provided in this guide, you can quickly fix the issue and continue with your work. Remember to always check the value type, format, and length of the uniqueidentifier column, and use the TRY_CAST or TRY_CONVERT function to handle any conversion errors.

Источник

Question

Answers

Question

Answers

Troubleshooting Tips

Check the Value Type

Check the Value Format

Check the Value Length

Use TRY_CAST or TRY_CONVERT

Use ISNULL Function

FAQ

Q1. What is a uniqueidentifier?

Q2. Why do I get «Conversion failed when converting from a character string to a uniqueidentifier» error?

Q3. How do I convert a character string to a uniqueidentifier?

Q4. How do I handle null values in a uniqueidentifier column?

Q5. How do I check the format and length of a uniqueidentifier column?

Conclusion