Wie führt man eine SQL-Anweisung mit mehr als 8000 Zeichen aus einer Variablen aus?

Das Problem liegt bei der impliziten Konvertierung.

Wenn Sie Unicode/nChar/nVarChar-Werte haben, die Sie verketten, konvertiert SQL Server Ihre Zeichenfolge implizit in VarChar (8000), und es ist leider zu dumm, um zu erkennen, dass Ihre Zeichenfolge abgeschnitten wird oder Sie eine Warnung erhalten, dass Daten vorhanden sind für diese Angelegenheit abgeschnitten!

Beim Verketten langer Zeichenketten (oder Zeichenketten, von denen Sie glauben, dass sie lang sein könnten), always vor-Verketten Sie das Erstellen von Zeichenketten mit CAST ('' as nVarChar (MAX)) wie folgt:

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.

Was für ein Schmerz und Grusel zu denken ist, wie SQL Server funktioniert. :(

Ich kenne andere Problemumgehungen im Web, die sagen, dass Sie Ihren Code mithilfe mehrerer Variablen in mehrere SET/SELECT-Zuweisungen aufteilen, dies ist jedoch angesichts der obigen Lösung nicht erforderlich.

Für diejenigen, die ein Maximum von 4000 Zeichen erreicht haben, lag dies wahrscheinlich daran, dass Sie Unicode hatten, sodass es implizit in nVarChar (4000) ..__ konvertiert wurde.

Erläuterung:
Was hinter den Kulissen passiert, ist, dass der SQL Server zwar die von Ihnen zugewiesene Variable (MAX) verwendet, die rechte Seite des Werts auswertet, den Sie zuerst zuweisen und standardmäßig nVarChar (4000) oder VarChar (8000) verwenden ) (abhängig davon, was Sie verketten). Nachdem Sie den Wert ermittelt haben (und ihn für Sie abgeschnitten haben), konvertiert er ihn in (MAX), wenn Sie ihn Ihrer Variablen zuweisen. Bis dahin ist es jedoch zu spät.

DECLARE @sql VARCHAR(max)
SET @sql = 'SELECT * FROM myTable'
Exec @sql

Hinweis: 

Print(@sql)

nur die ersten 8000 Zeichen anzeigen!

Das Problem ist, dass Ihre Zeichenfolge standardmäßig ein Limit von 8000 Symbolen hat. Um dies zu verhindern, sollten Sie es in (N) VARCHAR (MAX) konvertieren. 

DECLARE @sql VARCHAR(8000)
        SET @sql = CAST('SELECT * FROM myTable' AS VARCHAR(MAX))
--Check length of variable
 PRINT 'Length is: '+CAST(LEN(@sql) AS VARCHAR)+ 'symbols'
        Exec @sql

benutzen 

EXEC
(
  '
   --your sql script here
  '
)

Sie sollten die Antwort in diesem Beitrag lesen, die die Situation sehr gut erklärt: SQL NVARCHAR und VARCHAR Limits

1. Wenn die Länge x Ihrer Zeichenfolge weniger als 4000 Zeichen beträgt, wird eine Zeichenfolge in nvarchar(x) umgewandelt.
2. Wenn die Länge y zwischen 4000 und 8000 liegt, varchar(y)
3. Wenn die Länge mehr als 8000 Zeichen beträgt, kann nvarchar(max) bis zu 2 GB speichern.

Problem ist, dass nvarchar(max) + varchar(y) = nvarchar(max) + nvarchar(4000); SQL konvertiert Ihre varchar(y) in nvarchar(y) oder nvarchar(4000), wenn y größer als 4000 und kleiner als 8000 ist, wodurch Ihre Zeichenfolge abgeschnitten wird!

Nun, ich habe das schon mal gemacht (in SQL 2005) und ich kann Ihnen sagen, dass Sie zwei Möglichkeiten haben:

1 - Verwenden Sie die gespeicherte Prozedur sys.sp_sqlexec, die einen Parameter vom Typ Text annehmen kann (IMO ist dies der richtige Weg). Stört die Warnung nicht. In SQL 2008 wird ntext immer noch unterstützt. Wenn Sie das varchar (max) -Ding dort ausführen, wird es funktionieren. Wenn Sie also 2008 haben, funktionieren sowohl die Textlösung als auch die Variable max (max), sodass Sie Zeit haben, sie zu ändern = -). Im Jahr 2012 funktioniert jedoch nur die Variable max (max), daher müssen Sie sie vor dem Upgrade ändern.

2- (Dies ist, was ich zuerst getan habe) Überprüfen Sie diesen Beitrag: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274 und tun Sie, was der Benutzer "Kristen" sagt. Arbeitete wie ein Zauber für mich. Vergessen Sie nicht, sie auf eine leere Zeichenfolge voreinzustellen. Wenn Sie meinen Beitrag verstanden haben, wissen Sie inzwischen, dass es in SQL 2008 oder neuer dumm ist, dies zu tun.

Es gibt keine Lösung dafür, wie Sie es tun. MsSql unterstützt ab 2012 beispielsweise Ntext, mit dem Sie mehr als 8000 Zeichen in einer Variablen angeben können. Um dies zu lösen, können Sie mehrere Variablen oder mehrere Zeilen in einer Tabelle erstellen, die Sie durchlaufen können.

Bei einer MsSql-Version beträgt die maximale Größe einer Variablen bei der neuesten Version zum Zeitpunkt der Eingabe 8000 Zeichen. Wenn Sie also mit einer Folge von 80.000 Zeichen zu tun haben. Sie können die Daten in zehn Variablen mit jeweils 8000 Zeichen (8000 x 10 = 80.000) parsen oder die Variable in Stücke schneiden und in eine Tabelle schreiben Identitätswert, damit Sie die Daten in derselben Reihenfolge abrufen können.

Die Methode, die Sie ausprobieren, funktioniert derzeit nicht mit MsSql.

Eine andere obskure Option, die funktioniert, aber nicht ratsam ist, ist das Speichern der Variablen in einer Textdatei, indem die Shell-Befehle zum Lesen/Schreiben der Datei verwendet werden. Dann haben Sie über 8000 Zeichen Platz. Dies ist langsam und weniger sicher als bei den anderen oben beschriebenen Methoden.

Ich hatte das gleiche Problem, die Strings wurden abgeschnitten. Ich habe gelernt, dass Sie die Anweisung sp_executesql mehrmals ausführen können. 

Da mein Codeblock deutlich über der Grenze von 4k/Max lag, bringe ich ihn in kleine Stücke wie folgt heraus: 

set @statement = '                                                                                      
update pd                               
set pd.mismatchtype = 4                             
  FROM [E].[dbo].[' + @monthName + '_P_Data] pd                             
  WHERE pd.mismatchtype is null '                                                                               
exec sp_executesql @statement                               


set @statement = 'Select * from xxxxxxx'
exec sp_executesql @statement

set @statement = 'Select * from yyyyyyy '
exec sp_executesql @statement

end

So kann jeder Satz @Statement den Wert varchar (max) haben, solange jeder Chunk selbst innerhalb der Größenbegrenzung ist (ich habe den tatsächlichen Code in meinem Beispiel aus Platzgründen ausgeschnitten)

ALTER PROCEDURE [dbo].[spGetEmails]
AS
BEGIN
    SET NOCOUNT ON;
    -- Insert statements for procedure here

    declare @p varbinary(max)
set @p = 0x
declare @local table (col text)

SELECT   @p = @p + 0x3B + CONVERT(varbinary(100), Email)
 FROM tbCarsList
 where email <> ''
 group by email
 order by email

 set @p = substring(@p, 2, 10000000)

 insert @local values(cast(@p as varchar(max)))
 select   col from @local


END

Vor dem Drucken in Cast konvertieren und Datentyp ändern.

PRINT CAST(@sql AS NTEXT)

Probieren Sie es jetzt aus.

Ich hatte das gleiche Problem. Ich habe eine SQL mit mehr als 21.000 Zeichen. Aus irgendeinem Grund, 

Declare @SQL VARCHAR(MAX)

EXEC(@SQL) 

würde mit mehreren Fragen aufkommen

Ich musste es schließlich gleich in mehrere Variablen aufteilen und dann funktionierte es.

    Declare @SQL1 VARCHAR(MAX) = 'First Part'
    Declare @SQL2 VARCHAR(MAX) = 'Second  Part'
    Declare @SQL3 VARCHAR(MAX) = 'Third Part'
    Declare @SQL4 VARCHAR(MAX) = 'Fourth Part'

    Set @SQL= @SQL1 + @SQL2 + @SQL3 + @SQL4

    EXEC(@SQL)