nvarchar (max) ancora troncato

Quindi sto scrivendo una procedura memorizzata in MS SQL Server 2008. È una query molto lunga e devo scriverla dynamicmente, quindi creo una variabile chiamata @Query e la @Query di tipo NVARCHAR(MAX) . Ora, mi è stato detto che nelle versioni moderne di SQL Server, NVARCHAR(MAX) può contenere una quantità ridicola di dati, molto più del massimo di 4000 caratteri originali. Tuttavia, @Query viene ancora troncato a 4000 caratteri quando provo a stamparlo.

 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 malformsd (truncated) query 

Sto facendo qualcosa in modo errato o sono completamente in errore su come funziona NVARCHAR(MAX) ?

Per visualizzare l’SQL dinamico generato, passare alla modalità testo ( collegamento: Ctrl-T), quindi utilizzare SELEZIONA

 PRINT LEN(@Query) -- Prints out 4273, which is correct as far as I can tell --SET NOCOUNT ON SELECT @Query 

Per quanto riguarda sp_executesql , prova questo (in modalità testo), dovrebbe mostrare i tre aaaaa... ‘s il più centrale è il più lungo con’ SELECT .. ‘aggiunto. Guarda l’indicatore Ln... Col.. nella barra di stato in basso a destra che mostra 4510 alla fine della seconda uscita.

 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 

Il problema sembra essere associato all’istruzione SET. Penso che l’espressione non può essere più di 4.000 byte in termini di dimensioni. Non è necessario apportare alcuna modifica alle impostazioni se tutto ciò che si sta tentando di fare è assegnare un’istruzione generata dynamicmente che contenga più di 4.000 caratteri. Quello che devi fare è dividere il tuo incarico. Se l’istruzione è lunga 6.000 caratteri, trova un punto di interruzione logico e quindi concatena la seconda metà con la stessa variabile. Per esempio:

 SET @Query = 'SELECT ....' [Up To 4,000 characters, then rest of statement as below] SET @Query = @Query + [rest of statement] 

Ora esegui normalmente la query, ad es. EXEC ( @Query )

Il problema è con la conversione implicita.

Se hai i valori Unicode / nChar / nVarChar che stai concatenando, SQL Server convertirà implicitamente la tua stringa in nVarChar (4000), ed è sfortunatamente troppo stupido per capire che troncerà la tua stringa o ti darà un avvertimento che i dati sono stati troncato per quella questione!

Quando concatenate stringhe lunghe (o stringhe che ritenete potrebbero essere lunghe), pre-concatenate sempre la costruzione della stringa con CAST (” as nVarChar (MAX)) in questo modo:

 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. 

Che dolore e paura pensare che questo è esattamente come funziona SQL Server. 🙁

So che altre soluzioni alternative sul web dicono di suddividere il codice in più assegnazioni SET / SELECT usando più variabili, ma questo non è necessario data la soluzione sopra riportata.

Per coloro che hanno raggiunto un massimo di 8000 caratteri, probabilmente era perché non si disponeva di Unicode, quindi è stato convertito implicitamente in VarChar (8000).

Spiegazione:
Quello che succede dietro le quinte è che, anche se la variabile che si assegna agli usi (MAX), SQL Server valuterà il lato destro del valore che si assegna per primo e quello predefinito a nVarChar (4000) o VarChar (8000) (a seconda su cosa stai concatenando). Dopo aver completato il calcolo del valore (e dopo averlo troncato per te), lo converte in (MAX) quando lo assegni alla variabile, ma a quel punto è troppo tardi.

I risultati del testo consentono solo un massimo di 8192 caratteri.

Immagine dello schermo

Io uso questo approccio

 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*/ 

Il tuo primo problema è una limitazione PRINT . Non sono sicuro del motivo per cui sp_executesql stia fallendo. Dovrebbe supportare praticamente qualsiasi lunghezza di input.

Forse il motivo per cui la query è malformata è qualcosa di diverso dal troncamento.

Ho riscontrato lo stesso problema oggi e ho scoperto che oltre il limite di 4000 caratteri, ho dovuto dividere la query dynamic in due stringhe e concatenarle durante l’esecuzione della query.

 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) 

Il problema con la creazione di SQL dinamico mediante l’espressione di stringa è che SQL limita la valutazione delle espressioni di stringa a 4.000 caratteri. Puoi assegnare una stringa più lunga a una variabile nvarchar (max), ma non appena includi + nell’espressione (come + CASE … END +), il risultato dell’espressione è limitato a 4.000 caratteri.

Un modo per risolvere questo problema è usare CONCAT anziché +. Per esempio:

 SET @sql = CONCAT(@sql, N' ... dynamic SQL statements ... ', CASE ... END, N' ... dynamic SQL statements ... ') 

Dove @sql è dichiarato come nvarchar (max).

Stampa tronca il valore varchar (MAX) a 8000, nvarchar (MAX) a 4000 caratteri.

Ma;

 PRINT CAST(@query AS NTEXT) 

stamperà l’intera query.

Usa questa funzione PRINT BIG per produrre tutto:

 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 

Fonte:

https://www.richardswinbank.net/doku.php?id=tsql:print_big