Dynamic SQL Script More Than 8000 Characters

Question

I have a SQL script with more than 8000 characters and I stored it in some VARCHAR(MAX). And when execute it using:

EXEC (@script1 + @script2 + @script3 + ...)

its return 0 rows affected.

I try using replicate and get same problem. I am using SQL Server 2008.

post the output of print cast((@script1 + @script2 + @script3) as ntext) in your question — StackUser, Jun 02 '16 at 09:50
@StackNewUser: that will not help, since, [per the documentation](https://msdn.microsoft.com/library/ms176047), `PRINT` will truncate long message strings (regardless of the input type). To check the actual statement, it will need to be stored in a `VARCHAR(MAX)` column somewhere. — Jeroen Mostert, Jun 02 '16 at 10:05

score 2 · Answer 1 · answered Jun 02 '16 at 10:32

2

Try sp_executesql.

On 64-bit servers, the size of the string is limited to 2 GB, the maximum size of nvarchar(max).

So put all your data in @SQLString variable and execute like below:

DECLARE @SQLString nvarchar(max);

SET @SQLString = @script1 + @script2 + @script3 + ...

EXECUTE sp_executesql @SQLString

answered Jun 02 '16 at 10:32

gofr1

I am using 32-bit servers – Slocky Jun 07 '16 at 02:03
And? Did you try? :) Make all '@scriptN' nvarchar(max) and concatenate them in on '@SQLStrin'g and try to execute this like shown below. Good question/answer about nvarchat/varchar http://stackoverflow.com/questions/12639948/sql-nvarchar-and-varchar-limits – gofr1 Jun 07 '16 at 02:58
To explicitly say to system that this is nvarchar put N before single quoted expression. Like '@string = N'SELECT * FROM Table' – gofr1 Jun 07 '16 at 03:12

1 Answers1