La Conversion a échoué lors de la conversion d'une chaîne de caractères en erreur uniqueidentifier dans le serveur SQL
j'ai obtenu le message d'erreur "échec de la Conversion lors de la conversion d'une chaîne de caractères de type uniqueidentifier" et je suis enfin à la fin de ma corde. J'ai réduit mon problème à la plus petite possible tout en gardant l'erreur de tact. Installez D'abord le séparateur CSV d'ici si vous voulez reproduire:
http://www.sqlservercentral.com/articles/Tally+Table / 72993/
voici le code du test. Je suis sur SQL 2008R2 mais dans une base de données qui est compatible SQL 2005:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ZZZTESTTABLE]') AND type in (N'U'))
DROP TABLE [dbo].[ZZZTESTTABLE]
GO
CREATE TABLE [dbo].[ZZZTESTTABLE](
[Col1] [uniqueidentifier] NOT NULL,
CONSTRAINT [PK_ZZZTESTTABLE] PRIMARY KEY CLUSTERED
(
[Col1] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
-- Test table that I would like to check my values against
insert dbo.ZZZTESTTABLE(Col1) values('85B049B7-CDD0-4995-B582-5A74523039C0')
-- Test string that will be split into table in the DelimitedSplit8k function
declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'
-- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error
delete dbo.ZZZTESTTABLE
where Col1 not in
(
-- ERROR OCCURS HERE
select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)
voici la source de la fonction DelimitedSplit8K pour que vous n'ayez pas à aller la chercher:
CREATE FUNCTION dbo.DelimitedSplit8K
--===== Define I/O parameters
(@pString VARCHAR(8000), @pDelimiter CHAR(1))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000...
-- enough to cover VARCHAR(8000)
WITH E1(N) AS (
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS (--==== This provides the "zero base" and limits the number of rows right up front
-- for both a performance gain and prevention of accidental "overruns"
SELECT 0 UNION ALL
SELECT TOP (DATALENGTH(ISNULL(@pString,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
FROM cteStart s
;
4 réponses
l'utilisation de cet UDF pose en effet des hypothèses procédurales sur l'ordre d'exécution. Il suppose que la clause WHERE à l'intérieur de L'UDF sera évaluée avant le cast(item as uniqueidentifier) . Cette hypothèse est erronée car l'optimiseur est libre de changer le plan pour déplacer la clause WHERE au-dessus de la fonte et l'effet net est que la fonte est invitée à convertir un jeton partiel en un guid (i.e.. une chaîne comme 18E809E-EA7A-44B5-B230-776C42594D91 ).
pour un plus réponse détaillée lire les fonctions T-SQL n'impliquent pas un certain ordre d'exécution .
comme solution de contournement, vous pouvez forcer NULL dans les valeurs projetées de L'UDF pour les lignes qui ne répondent pas à la clause où:
CREATE FUNCTION dbo.DelimitedSplit8K
...
cteStart(N1, nullify) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
SELECT t.N+1,
case when (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0) then 1 else 0 end
FROM cteTally t
WHERE (SUBSTRING(@pString,t.N,1) = @pDelimiter OR t.N = 0)
)
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY s.N1),
Item = case s.nullify
when 1 then SUBSTRING(@pString,s.N1,ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000))
else null
end
FROM cteStart s;
go
parce que l'expression du cas est garantie d'être évaluée avant la fonte (puisque l'entrée de la fonte est la sortie du cas) la réorganisation de la clause WHERE est sûre.
pas sûr de ce qui se passe ici, mais le problème ne semble pas être le format des guids ou la sortie de la fonction. L'exécution de ce travail:
declare @temp varchar(max) = '918E809E-EA7A-44B5-B230-776C42594D91,6F8DBB54-5159-4C22-9B0A-7842464360A5'
select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
peut-être que le processeur de requête regarde le schéma de retour de la fonction et dit qu'il ne peut pas être moulé à uniqueidentifier ? J'espère que quelqu'un d'autre peut fournir une réponse précise.
sélectionner la sortie de la fonction split dans une table temp fonctionnera:
select cast(Item as uniqueidentifier) as Item into #temp from dbo.DelimitedSplit8K(@temp, ',')
-- I'm trying to delete all data in the ZZZTESTTABLE that is not in my string but I get the error
delete dbo.ZZZTESTTABLE
where Col1 not in
(
-- ERROR OCCURS HERE
--select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
select Item from #temp
)
pourquoi lancer L'article à uniqueidentifier quand vous pouvez le faire l'inverse.
au lieu de
where Col1 not in
(
-- ERROR OCCURS HERE
select cast(Item as uniqueidentifier) from dbo.DelimitedSplit8K(@temp, ',')
)
vous pouvez essayer ceci:
where cast(Col1 as varchar(64)) not in
(
select Item
from dbo.DelimitedSplit8K(@temp, ',')
)
on dirait que j'ai mal interprété la question la première fois. Bon travail produire un script de test qui reproduit l'erreur. Les œuvres suivantes pour moi:
delete dbo.ZZZTESTTABLE
WHERE Col1 in
(
select Z.Col1
from dbo.ZZZTESTTABLE Z
LEFT JOIN dbo.DelimitedSplit8K(@temp, ',') S on S.Item = Z.Col1
where S.Item is null
)
OPTION (force order)