January 6, 2010 at 6:44 pm
Greetings all.
I am currently building a Dynamic sp_executesql statement and I am running into the nvarchar(max) limit when executing this statement. I have many parameters in place within the sql statement which is why I am using this and not just Exec @sql.
When I export the @sql that is getting truncated I place it into word and notice it is only 6k+ characters. The only thing I can think of is I have a CTE within the code itself and then the select against the corresponding CTE.
For whatever reason I can not seem to get this within the limits without cutting my code down.
I tried assigning the maximum to nvarchar(8000) which I thought was the max, but I get this error
The size (8000) given to the parameter '@SQL' exceeds the maximum allowed (4000). I could have sworn it was up to 8000 characters but maybe that was bits. Oh well. There has to be some sort of solution.
Link to my blog http://notyelf.com/
January 6, 2010 at 11:15 pm
If you really are using 2k5, then NVARCHAR(MAX) will give you up to a billion characters. And MAX is not a number... NVARCHAR(MAX) with the word MAX is a valid datatype.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2010 at 7:28 am
Also the unicode datatypes NCHAR and NVARCHAR do have a maximum size of 4000 as each character takes 2 bytes.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 7, 2010 at 7:31 am
Still, it looks like single string assignment cannot be longer than 8000 characters. To work around this, split your long sql string into parts and use + operator to put them together.
declare @mx nvarchar(max)
set @mx = replicate('a', 7999)
select len(@mx)
go
declare @mx nvarchar(max)
set @mx = replicate('a', 8000)
select len(@mx)
go
declare @mx nvarchar(max)
set @mx = replicate('a', 10000) --still 8000
select len(@mx)
go
--result value as sum of partial strings
declare @mx nvarchar(max)
set @mx = replicate('a', 10000)
set @mx = @mx + @mx --8000 + 8000
select len(@mx)
Regards
Piotr
UPDATE:
Actually, if you convert parameter to replicate to nvarchar(max), the function doesn't truncate the output:
declare @mx nvarchar(max)
set @mx = replicate(convert(nvarchar(max), 'a'), 10000)
select len(@mx)
...and your only reply is slàinte mhath
January 7, 2010 at 8:39 am
I will also check on the limits of character strings, however, if this is an internal string do you really need an nvarchar? if you used the varchar you will be able to double the size to 8000 instead of 4000
January 7, 2010 at 9:09 am
I am not sure if this affects just columns in a table that use max or also affects variables, you might one to give the sp_tableoption a try
When the sp_tableoption stored procedure 'large value types out of row' option is set to OFF, the in-row storage limit for large-value types is 8,000 bytes. A 16-byte root is stored in-row when this option is set to ON. For more information, see sp_tableoption (Transact-SQL).
January 7, 2010 at 9:34 am
jcdyntek (1/7/2010)
I will also check on the limits of character strings, however, if this is an internal string do you really need an nvarchar? if you used the varchar you will be able to double the size to 8000 instead of 4000
He's building dynamic sql... sp_executesql takes nvarchar as a parameter, not varchar...
January 7, 2010 at 10:14 am
Thank you for all the responses, I will try out a few of the suggestions here! However, I did find a solution though I have no idea why it works 🙂
I Declared @sql as varchar(max) then loaded all of my code, then towards the end passed my @sql into @Sql2 which is nvarchar(max) and it works fine
Honestly why that works I have no idea, and more over I have no idea why I even thought of it but hey...it works!
Link to my blog http://notyelf.com/
January 7, 2010 at 11:16 am
Piotr.Rodak (1/7/2010)
Still, it looks like single string assignment cannot be longer than 8000 characters. To work around this, split your long sql string into parts and use + operator to put them together.
declare @mx nvarchar(max)
set @mx = replicate('a', 7999)
select len(@mx)
go
declare @mx nvarchar(max)
set @mx = replicate('a', 8000)
select len(@mx)
go
declare @mx nvarchar(max)
set @mx = replicate('a', 10000) --still 8000
select len(@mx)
go
--result value as sum of partial strings
declare @mx nvarchar(max)
set @mx = replicate('a', 10000)
set @mx = @mx + @mx --8000 + 8000
select len(@mx)
Regards
Piotr
UPDATE:
Actually, if you convert parameter to replicate to nvarchar(max), the function doesn't truncate the output:
declare @mx nvarchar(max)
set @mx = replicate(convert(nvarchar(max), 'a'), 10000)
select len(@mx)
How would I use this in my code? This seems to repeat the same string over and over again. How do I feed my code into this?
Link to my blog http://notyelf.com/
January 7, 2010 at 11:41 am
mtassin (1/7/2010)
jcdyntek (1/7/2010)
I will also check on the limits of character strings, however, if this is an internal string do you really need an nvarchar? if you used the varchar you will be able to double the size to 8000 instead of 4000He's building dynamic sql... sp_executesql takes nvarchar as a parameter, not varchar...
In SQL Server 2005, I believe you'll find that sp_executesql will take NVARCHAR(MAX) (1 billion characters) so essentially unlimited in 2k5. No special tricks needed.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2010 at 11:43 am
Jeff Moden (1/7/2010)
mtassin (1/7/2010)
jcdyntek (1/7/2010)
I will also check on the limits of character strings, however, if this is an internal string do you really need an nvarchar? if you used the varchar you will be able to double the size to 8000 instead of 4000He's building dynamic sql... sp_executesql takes nvarchar as a parameter, not varchar...
In SQL Server 2005, I believe you'll find that sp_executesql will take NVARCHAR(MAX) (1 billion characters) so essentially unlimited in 2k5. No special tricks needed.
Exactly... and it does... I've tested as such. What it won't like though is varchar... you need nvarchar, which was my point above.
January 7, 2010 at 7:36 pm
Only if it's a constant... in 2k5, it'll take any variable datatype that's implicitly convertable to NText and that includes VARCHAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 7, 2010 at 8:10 pm
Jeff Moden (1/7/2010)
Only if it's a constant... in 2k5, it'll take any variable datatype that's implicitly convertable to NText and that includes VARCHAR.
Jeff can you show an example of this?
Link to my blog http://notyelf.com/
January 7, 2010 at 11:09 pm
shannonjk (1/7/2010)
Jeff Moden (1/7/2010)
Only if it's a constant... in 2k5, it'll take any variable datatype that's implicitly convertable to NText and that includes VARCHAR.Jeff can you show an example of this?
How about a quote from Books Online?
Syntax
sp_executesql [ @stmt = ] stmt
[
{, [@params=] N'@parameter_name data_type [ [ OUT [ PUT ][,...n]' }
{, [ @param1 = ] 'value1' [ ,...n ] }
]
Arguments
[ @stmt = ] stmt
Is a Unicode string that contains a Transact-SQL statement or batch. stmt must be [font="Arial Black"]either a Unicode constant or a variable that can be implicitly converted to ntext[/font]. More complex Unicode expressions, such as concatenating two strings with the + operator, are not allowed. Character constants are not allowed. If a constant is specified, it must be prefixed with an N. For example, the Unicode constant N'sp_who' is valid, but the character constant 'sp_who' is not. The size of the string is limited only by available database server memory.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 8, 2010 at 7:34 am
That's odd because this query
declare @bob-2 varchar(max)
select
@bob-2 = 'select * from sys.tables'
exec sp_executesql @bob-2
Returns this message on SQL 2k5 when I run it.
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply