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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,940)