sql - caracteres - nvarchar(max) sigue siendo truncado
nvarchar max cuantos caracteres (9)
Así que estoy escribiendo un procedimiento almacenado en MS SQL Server 2008. Es una consulta realmente larga y tengo que escribirla dinámicamente, así que creo una variable llamada @Query y la hago de tipo NVARCHAR(MAX) . Ahora, me han dicho que en las versiones modernas de SQL Server, NVARCHAR(MAX) puede contener una cantidad ridícula de datos, mucho más que el máximo original de 4000 caracteres. Sin embargo, @Query aún se trunca a 4000 caracteres cuando intento imprimirlo.
DECLARE @Query NVARCHAR(max);
SET @Query = ''SELECT...'' -- some of the query gets set here
SET @Query = @Query + ''...'' -- more query gets added on, etc.
-- later on...
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
PRINT @Query -- Truncates value to 4000 characters
EXEC sp_executesql @Query -- totally crashes due to malformed (truncated) query
¿Estoy haciendo algo incorrectamente o estoy completamente equivocado sobre cómo funciona NVARCHAR(MAX) ?
El problema es con la conversión implícita.
Si tiene valores Unicode / nChar / nVarChar concatenados, entonces SQL Server convertirá implícitamente su cadena a nVarChar (4000), y desafortunadamente es demasiado tonto como para darse cuenta de que truncará su cadena o incluso le dará una Advertencia de que los datos han sido truncado para el caso!
Al concatenar cadenas largas (o cadenas que crees que podrían ser largas) siempre concatena previamente la construcción de cadenas con CAST ('''' como nVarChar (MAX)) así:
SET @Query = CAST('''' as nVarChar(MAX))--Force implicit conversion to nVarChar(MAX)
+ ''SELECT...''-- some of the query gets set here
+ ''...''-- more query gets added on, etc.
Qué dolor y miedo pensar que esto es exactamente como funciona SQL Server. :(
Sé que otras soluciones en la web dicen dividir el código en múltiples asignaciones SET / SELECT usando múltiples variables, pero esto es innecesario dada la solución anterior.
Para aquellos que alcanzaron un máximo de 8000 caracteres, probablemente fue porque no tenían Unicode, por lo que se convirtió implícitamente en VarChar (8000).
Explicación:
Lo que sucede detrás de las escenas es que, aunque la variable que está asignando usa (MAX), SQL Server evaluará el lado derecho del valor que está asignando primero y de forma predeterminada a nVarChar (4000) o VarChar (8000) (dependiendo en lo que estás concatenando). Después de que termine de calcular el valor (y después de truncarlo), entonces lo convierte en (MAX) cuando lo asigne a su variable, pero para entonces ya es demasiado tarde.
El problema con la creación de SQL dinámico mediante la expresión de cadenas es que SQL limita la evaluación de las expresiones de cadenas a 4.000 caracteres. Puede asignar una cadena más larga a una variable nvarchar (max), pero tan pronto como incluya + en la expresión (como + CASE ... END +), el resultado de la expresión estará limitado a 4.000 caracteres.
Una forma de solucionar esto es usar CONCAT en lugar de +. Por ejemplo:
SET @sql = CONCAT(@sql, N''
... dynamic SQL statements ...
'', CASE ... END, N''
... dynamic SQL statements ...
'')
Donde @sql se declara como nvarchar (max).
El problema parece estar asociado con la declaración SET. Creo que la expresión no puede tener más de 4.000 bytes de tamaño. No es necesario realizar ningún cambio en ninguna configuración si todo lo que intenta hacer es asignar una declaración generada dinámicamente que tenga más de 4.000 caracteres. Lo que debes hacer es dividir tu tarea. Si su enunciado tiene 6.000 caracteres de longitud, encuentre un punto de quiebre lógico y luego concatene la segunda mitad con la misma variable. Por ejemplo:
SET @Query = ''SELECT ....'' [Up To 4,000 characters, then rest of statement as below]
SET @Query = @Query + [rest of statement]
Ahora ejecute su consulta de manera normal, es decir, EXEC ( @Query )
He encontrado el mismo problema hoy y descubrí que más allá de ese límite de 4000 caracteres, tuve que dividir la consulta dinámica en dos cadenas y concatenarlas al ejecutar la consulta.
DECLARE @Query NVARCHAR(max);
DECLARE @Query2 NVARCHAR(max);
SET @Query = ''SELECT...'' -- some of the query gets set here
SET @Query2 = ''...'' -- more query gets added on, etc.
EXEC (@Query + @Query2)
Imprimir trunca el varchar (MAX) a 8000, nvarchar (MAX) a 4000 caracteres.
Pero;
PRINT CAST(@query AS NTEXT)
imprimirá toda la consulta.
Los resultados para el texto solo permiten un máximo de 8192 caracteres.
Yo uso este enfoque
DECLARE @Query NVARCHAR(max);
set @Query = REPLICATE(''A'',4000)
set @Query = @Query + REPLICATE(''B'',4000)
set @Query = @Query + REPLICATE(''C'',4000)
set @Query = @Query + REPLICATE(''D'',4000)
select LEN(@Query)
SELECT @Query /*Won''t contain any "D"s*/
SELECT @Query as [processing-instruction(x)] FOR XML PATH /*Not truncated*/
Para ver el SQL dinámico generado, cambie al modo de texto ( atajo: Ctrl-T), luego use SELECCIONAR
PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell
--SET NOCOUNT ON
SELECT @Query
En cuanto a sp_executesql , intente esto (en modo texto), debería mostrar los tres aaaaa... ''s el del medio siendo el más largo con'' SELECT .. ''agregado. Observe el indicador Ln... Col.. en la barra de estado en la parte inferior derecha que muestra 4510 al final de la 2ª salida.
declare @n nvarchar(max)
set @n = REPLICATE(convert(nvarchar(max), ''a''), 4500)
SET @N = ''SELECT '''''' + @n + ''''''''
print @n -- up to 4000
select @n -- up to max
exec sp_Executesql @n
Su primer problema es una limitación de la instrucción PRINT . No estoy seguro de por qué sp_executesql está fallando. Debería soportar prácticamente cualquier longitud de entrada.
Quizás el motivo por el que la consulta está mal formada es algo distinto al truncamiento.
Use esta función PRINT BIG para dar salida a todo:
IF OBJECT_ID(''tempdb..#printBig'') IS NOT NULL
DROP PROCEDURE #printBig
GO
CREATE PROCEDURE #printBig (
@text NVARCHAR(MAX)
)
AS
--DECLARE @text NVARCHAR(MAX) = ''YourTextHere''
DECLARE @lineSep NVARCHAR(2) = CHAR(13) + CHAR(10) -- Windows /r/n
DECLARE @off INT = 1
DECLARE @maxLen INT = 4000
DECLARE @len INT
WHILE @off < LEN(@text)
BEGIN
SELECT @len =
CASE
WHEN LEN(@text) - @off - 1 <= @maxLen THEN LEN(@text)
ELSE @maxLen
- CHARINDEX(REVERSE(@lineSep), REVERSE(SUBSTRING(@text, @off, @maxLen)))
- LEN(@lineSep)
+ 1
END
PRINT SUBSTRING(@text, @off, @len)
--PRINT ''@off='' + CAST(@off AS VARCHAR) + '' @len='' + CAST(@len AS VARCHAR)
SET @off += @len + LEN(@lineSep)
END
Fuente: