NVARCHAR limit - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

NVARCHAR limit

Author

Topic

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-11-27 : 12:56:37

i need to execute a long dynamic sql, but NVARCHAR only have limit upto 4000, what should i do.
I need NVARCHAR upto 10000

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2007-11-27 : 13:10:13

This question is pretty vague. If your limit is 4000 characters, and you need 1000 characters you should be ok. I'm assuming you are attempting to execute dynamic SQL via the EXEC or sp_executesql statements. But what you are asking is unclear.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-11-27 : 13:56:02

You can use an nvarchar(max) local variable to hold a really long dynamic SQL statement.



declare @sql nvarchar(max)

set @sql = N'select [Start]=1,'+
           replicate(convert(nvarchar(max),N' '),30000)+
           N'[End]=999'

select [datalength @sql] = datalength(@sql)

exec(@sql)


Results:
datalength @sql      
-------------------- 
60052

(1 row(s) affected)

Start       End         
----------- ----------- 
1           999

(1 row(s) affected)

CODO ERGO SUM

lionofdezert
Aged Yak Warrior

885 Posts

Posted - 2007-11-27 : 14:02:56

thanx dear

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-28 : 01:41:43

and if you cant avoid dynamic sql, then read www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources