Conversion Failed When Converting from a Character String to Uniqueidentifier Error in SQL Server

Conversion failed when converting from a character string to uniqueidentifier - Two GUIDs

The problem was that the ID column wasn't getting any value. I saw on @Martin Smith SQL Fiddle that he declared the ID column with DEFAULT newid and I didn't..

Conversion failed error is showing for uniqueidentifier in SQL query

According to Microsoft documentation:

The uniqueidentifier type is considered a character type for the
purposes of conversion from a character expression, and therefore is
subject to the truncation rules for converting to a character type.
That is, when character expressions are converted to a character data
type of a different size, values that are too long for the new data
type are truncated. See the Examples section.

That explains why it works fine if you append characters after the 36th position.

When you prepend characters to the guid, you are breaking the formatting rules for the guid and then the conversion fails.

In a stored procedure you can validate the guid by using TRY_CONVERT. It will return NULL if the conversion is not possible:

IF TRY_CONVERT(UNIQUEIDENTIFIER,@userId) IS NULL
   BEGIN
      .... report error ...
   END

TRY_CONVERT is only available from SQL Server 2012. If you need to validate a string before conversion to UNIQUEIDENTIFIER on older versions, you can use the following code:

IF NOT @userId LIKE REPLACE('00000000-0000-0000-0000-000000000000', '0', '[0-9a-fA-F]')+'%'
    BEGIN
          .... report error ...
    END

Conversion failed when converting from a character string to uniqueidentifier in SQL SERVER select statement

Error is pretty clear, that value isn't a uniqueidentifier.

If you check against a proper GUID (i.e. SELECT NEWID() -> 919053E6-7CE6-4324-9A58-A2EACA5E0F5F) notice that the first "block" has 8 characters (919053E6). Yours, however, has 7 characters (1822CBE).

On consideration, this is probably simply a typographical error.

Conversion failed when converting from string to uniqueidentifier - loading DataTable with ExecuteReader

You should always parameterise your SQL queries to help prevent SQL injection and avoid problems like you're facing now. See Why do we always prefer using parameters in SQL statements?.

Use SqlParameter to add the parameters to the SqlCommand.

string invoiceStatusSQL = @"select status, invoice_id from invoices where acct_id = @accountId";
command = new SqlCommand(invoiceStatusSQL, cnn);

SqlParameter idParam = new SqlParameter("@accountId", accountid);
command.Parameters.Add(idParam);

da.Load(command.ExecuteReader());

---

You can also specify the actual database type when creating the parameter, which will reduce any issues you might have with the framework inferring the type incorrectly (although I don't think that would happen in your case for a Guid/UniqueIdentifier). One way to specify the type is shown below.

var p = new SqlParameter 
            { 
                ParameterName = "@accountId", 
                SqlDbType = SqlDbType.UniqueIdentifier,  
                Value = accountid  
            };

Conversion failed when converting from a character string to uniqueidentifier

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!

Conversion failed when converting character string to uniqueidentifier error from asp.net application but not from SQL Server Management Studio

1) I assume that this error is generated during evaluation of following predicate where t2.guid in (select t3.guid ...). According to data type precedence, UNIQUEIDENTIFIER (t2.guid) has higher precedence than NVARCHAR (t3.guid). Because of this reason, SQL Server will execute an implicit conversion from of t3.guid values from NVARCHAR (lower precedence) to UNIQUEIDENTIFIER (higher precedence) which is the same as CONVERT(UNIQUEIDENTIFIER, t3.guid). If t3.guid / NVARCHAR column contain invalid UNIQUEIDENTIFIER values (ex. empty strings) then an error will be raised. Why this query performs totally different on SSMS ? One simple explanation come from execution plan: the execution plans could be different thus:

a) The execution plan associated with query sent from WebApp avoids reading bad NVARCHAR values from t3.guid column.

b) Execution plan associated with query sent from SSMS have a diff. data access path. This time, the XP forces SQL Server to read bad values (invalid UNIQUEIDENTIFIERs) from t3.column.

2) Why there are diff. execution plan for the "same" query ? Two reasons: (2.1) the SET sessions settings are different for WepApp connection compared with settings for SSMS. Diff. settings and/or (2) parameter sniffing. Because of one of these reasons, execution plans are different.

3) Following query return these settings:

SELECT s.session_id, s.program_name, s.login_name, s.nt_domain, s.nt_user_name, s.quoted_identifier, s.ansi_nulls, s.ansi_warnings, s.* -- see other SETttings 
FROM sys.dm_exec_sessions s
WHERE s.session_id IN (<WebApp spid>, <SSMS spid>)

4) To check if there are one or more XP for the same SP you could use following query:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT  pln.query_plan, ps.*
FROM    sys.dm_exec_procedure_stats ps
OUTER APPLY sys.dm_exec_query_plan(ps.plan_handle) pln
WHERE   ps.database_id = DB_ID() -- Check DB for current session
AND     ps.object_id = OBJECT_ID('dbo.ProcName')

or

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT *
FROM (
SELECT 
    SUBSTRING(qt.text,qs.statement_start_offset/2 +1, 
                 (CASE WHEN qs.statement_end_offset = -1 
                       THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 
                       ELSE qs.statement_end_offset END -
                            qs.statement_start_offset
                 )/2
             ) AS query_source_code, 
     qp.query_plan AS query_xplan
FROM sys.dm_exec_query_stats AS qs 
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt 
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS qp
) x
WHERE x.query_source_code LIKE '%select 
    [computed column]
from 
    [externaldb].dbo.[table1] t1
where 
    t1.userid in (select t2.userid 
                  from [synextdbusers] t2 
                  where t2.guid in (select t3.guid 
                                    from [mydb].dbo.[users] t3 
                                    where t3.guid is not NULL)
                    and t2.guid is not NULL)%'

5) Quick demo:

USE tempdb
GO
DROP TABLE dbo.InvoiceDetail
DROP TABLE dbo.Invoice 
GO
CREATE TABLE dbo.Invoice (ID INT PRIMARY KEY, InvoiceDate DATE)
CREATE TABLE dbo.InvoiceDetail(ID INT PRIMARY KEY, InvoiceID INT REFERENCES dbo.Invoice(ID), Qty INT, UnitPrice INT, ProductID NVARCHAR(36))
GO

CREATE INDEX OX_InvoiceDetail_InvoiceID_#_ProductID
ON dbo.InvoiceDetail (InvoiceID) 
INCLUDE (ProductID)

INSERT dbo.Invoice VALUES 
(101, '2016-01-01'), 
(102, '2016-02-02'),
(103, '2016-03-03')
GO

INSERT dbo.InvoiceDetail (ID, InvoiceID, Qty, UnitPrice, ProductID)
VALUES 
(1, 101, 11, 10, ''),
(2, 102, 22, 20, '00000000-0000-0000-0000-000000000000'), 
(3, 103, 33, 30, '00000000-0000-0000-0000-000000000000'),
(4, 103, 44, 40, '00000000-0000-0000-0000-000000000000')
GO
--DELETE dbo.InvoiceDetail WHERE ID = 0

SELECT  i.InvoiceDate, id.ProductID
FROM    dbo.Invoice i INNER JOIN dbo.InvoiceDetail id ON i.ID = id.InvoiceID
WHERE   i.InvoiceDate >= '2016-02-02'
AND     id.ProductID = NEWID()
OPTION (FORCE ORDER)
GO

SELECT  i.InvoiceDate, id.ProductID
FROM    dbo.Invoice i INNER JOIN dbo.InvoiceDetail id ON i.ID = id.InvoiceID
WHERE   i.InvoiceDate >= '2016-02-02'
AND     id.ProductID = NEWID()
OPTION (HASH JOIN)
GO

Results:

InvoiceDate ProductID
----------- ------------------------------------

(0 row(s) affected)

InvoiceDate ProductID
----------- ------------------------------------
Msg 8169, Level 16, State 2, Line 36
Conversion failed when converting from a character string to uniqueidentifier.

Note: last two queries are similar but because I used those query hints the XP will be also different (in this case).

---

Related Topics