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

Thanks to All,

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