How to set morethan max size charecters in NVARCHAR(MAX),sql Server2005

Question

Hi All,

I am using

declare @insertsql nvarchar(MAX)

--above @insertsql for sp_executesql takes only nvarchar as input

set @insertsql='--i am giving More than 10000 characters here -----'

EXEC sp_executesql @insertsql, N'@inXMLRequest XML OUTPUT', @inXMLRequest OUTPUT

how to insert morethan 10000 charecters in NVARCHAR(MAX) in sql server2005

can any one help please

Thanks in advance

Answer 1

This has happened to me when I use inline SQL statements instead of stored procs.

If you are hitting that ceiling, you may want to consider moving to stored procs.

Answer 2

Thanks to All,

i got the answer

Insted of using SP_Executesql directly we Executing nvarchar variable

Above we Are preparing @insertsql nvarchar variable morethan 8000 characters and it is giving to sp_executesql like this

EXEC sp_executesql @insertsql, N'@inXMLRequest XML OUTPUT',@inXMLRequest OUTPUT

insted of above query replaced with below query

Exec ('DeClare @inXMLRequest XML SET @inXMLRequest='------above 8000 characters---')

Finally we will execute that nvarchar string and get out put

Answer 3

The parameter that you use for the command to run in sp_executesql (@insertsql in your case) is NVARCHAR(4000) not NVARCHAR(MAX) so you are limited to a 4000 character dynamic SQL command.

If you are running out of space in the variable, you'll need to do some code re-factoring.