cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Zhangzhonghao
Helper II
Helper II

Action 'Execute_stored_procedure_(V2)' failed due to the SQL conversion from a character string to uniqueidentifier

I have a stored procedure, which accepts a parameter. The parameter's type is an uniqueidentifier. I've pass the correct format, why did it always failed with the error conversion failure? Anyone can help!!!

Zhangzhonghao_0-1617427921467.png

{
  "status"400,
  "message""Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier.\r\n     inner exception: Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier.\r\nclientRequestId: a866fd12-c4cf-4f04-b203-e86de3ee59c8",
  "error": {
    "message""Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier.\r\n     inner exception: Microsoft SQL: Conversion failed when converting from a character string to uniqueidentifier."
  },
  "source""sql-ea.azconn-ea.p.azurewebsites.net"
}
1 ACCEPTED SOLUTION

Accepted Solutions

First, my apologies.  I should read documentation more closely.  You can't use Guid() to cast a string to a guid.  It always produces a new guid.  But I did go back and try your scenario.  SQL should accept a straight string in the right format as a guid.  Can you share how the parameter is defined in the stored procedure and what you are using it for?  I built a simple table with a uniqueIdentifier as a Key and then executed the following stored procedure feeding the Guid in as a string.  It works.

 

ALTER PROCEDURE [dbo].[selectbyGuid]
	@GuidId UNIQUEIDENTIFIER
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
    SELECT FieldA,FieldB from dbo.SQL
	Where Id = @GuidId 
END

I also ran it with the GUID you are using and it worked correctly.  No data was returned but it did accept the GUID as a valid parameter.

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

4 REPLIES 4
Pstork1
Dual Super User III
Dual Super User III

Unique Identifiers are GUIDs, not strings, and there is no automatic conversion.  Try using GUID() to cast the string to a GUID and use that for your parameter.



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Hi @Pstork1 , thanks for your replying! I just tried to use guid() function to convert, but it still failed. Any idea for this? 

Zhangzhonghao_0-1617461234094.pngZhangzhonghao_1-1617461293632.png

 

 

First, my apologies.  I should read documentation more closely.  You can't use Guid() to cast a string to a guid.  It always produces a new guid.  But I did go back and try your scenario.  SQL should accept a straight string in the right format as a guid.  Can you share how the parameter is defined in the stored procedure and what you are using it for?  I built a simple table with a uniqueIdentifier as a Key and then executed the following stored procedure feeding the Guid in as a string.  It works.

 

ALTER PROCEDURE [dbo].[selectbyGuid]
	@GuidId UNIQUEIDENTIFIER
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    -- Insert statements for procedure here
    SELECT FieldA,FieldB from dbo.SQL
	Where Id = @GuidId 
END

I also ran it with the GUID you are using and it worked correctly.  No data was returned but it did accept the GUID as a valid parameter.

 



-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

@Pstork1 thanks for your solution! It turned out that we have incorrect type define of UNIQUEIDENTIFIER (We defined it varchar)...

Helpful resources

Announcements
MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

Top Solution Authors
Users online (79,412)