how to set 9000 characters in nvarchar(max)

Question

Hi All,

I need to set 9000 characters in nvarchar variable

declare @inXMLRequest xml

declare @insertsql nvarchar(max)

set @insertsql='--------9000 characters--------'
EXEC sp_executesql   @insertsql,
                   N'@inXMLRequest XML OUTPUT', 
                     @inXMLRequest OUTPUT 
print @insertsql

But NVARCHAR is taking 5000 characters only how to set 9000 characters in NVARCHAR variable?

Answer 1

if you write a small program that tries the same thing outside the management studio you will be able to do this.

unfortunately for some reason it is not possible to do this with a insert statement in the management studio. there is a 4k limit on what you put into the management studio.

I've people do inserts before, ie, first insert 4k chars then the next 4k chars etc

Answer 2

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

Answer 3

I'm not sure, but you cant store such long rows in varchar. a row have to fit into a db-page. varchar fields are stored in the same page, where the row is stored. a page is usually smaller than 8192 bytes (depending on db-system)

there a some exceptions like blob fields which are not stored in the same page of a row.

you should use a other data type like text/blob/image.

nvarchar 9000 is btw. impossible this will allocate 18000 byte which dont fit into a page. this is the reason for the 4000 char limit.