Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Friday, August 10, 2012 6:43 AM
Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype. And my query length is more than 4000 characters and it is showing error. Check this screenshot
how do I solve this problem.
Friday, August 10, 2012 10:41 AM ✅Answered | 2 votes
Without seeing the full batch it gets difficult to diagnose.
However, I spot another serious shortcoming that you need to fix: This:
AND bd.billingdate >= ''' + CONVERT(nvarchar(10), @BillingStartDate, 120) + '''
Should be:
AND bd.billingdate >= @pBillingStartDate
And then the call to sp_executesql should look like this:
DECLARE @params nvarchar(MAX) = N'@BillingStartDate date'
Execute sp_Executesql @SQLQuery, @params,
@BillingStartDate = @pBillingStartDate
And you should handle all parameter values that you interleave above the same way.
Parameterised SQL gives you three advantages:
1) Protection against SQL injection.
2) Better untilisation of the SQL Server cache, as queries with different parameters can share the same plan.
3) It is soooooo much easier to code.
Another issue that I spot is (NOLOCK). NOLOCK is a feature that requires very careful understanding to use in application code. The risks for incorrect results or spurious errors are considerable.
I suggest that you clean up your use of parameters first, and once you have done that, and it is still not working, post the full batch.
Then again, why are you using dynamic SQL in the first place? If you are dealing with dynamic search conditions there solutions which saves your from dynamic SQL entirely, see my article on
http://www.sommarskog.se/dyn-search-2008.html
This article also includes patterns for how to write dynamic SQL in a structured and organised way.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, August 10, 2012 11:33 AM ✅Answered
No, you don't need to dynamic SQL to handle comma-separated lists! You wasted quite some precious time because of this misconeption!
I have other articles on my web site for handling lists of values. Either you unpack them to a table with a function, or even better you pass the values in a table-valued parameter. (But since I don't a single thing about Reporting Services, I can't help you on that side of things.)
Anyway, start here: http://www.sommarskog.se/arrays-in-sql.html
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Tuesday, January 14, 2014 10:39 PM ✅Answered
Because
REPLICATE ( string_expression ,integer_expression )
If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type.
So should be
Declare @s nvarchar(MAX)
Declare @s1 nvarchar(MAX)
Declare @d nvarchar(5)
Set @d = 'ee'
Set @s1 = Replicate(cast('a' as nvarchar(max) ),10000) + @d
Set @s =N'Select '''+@s1 + ''''
--Set @s = @s + '''22'''
print Len(@s)
print Len(@s1)
exec sp_executeSQL @s
David
David http://blogs.msdn.com/b/dbrowne/
Friday, August 10, 2012 6:45 AM
Try by replacing NVarchar(5000) to NVarchar(4000) at declaration part.Maximum allowed size for NVarchar is 4000.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 6:49 AM
then how do i execute my query if i replace the length. i just found out that my query length is 4912 characters.
Friday, August 10, 2012 6:50 AM | 1 vote
Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype.
The "problem" is you don't use NVarchar(max), you use Nvarchar =>
DECLARE @SqlQuery AS NVarchar(max);
Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing
Friday, August 10, 2012 6:51 AM | 1 vote
or use nvarchar(max)
From BOL:
nvarchar [ ( n | max ) ]
Variable-length Unicode string data. n defines the string length and can be a value from 1 through 4,000. max indicates that the maximum storage size is 2^31-1 bytes (2 GB).
Friday, August 10, 2012 6:52 AM
Declare
@s nVarchar(4001)/*(Error)*/
--Declare @s nVarchar(4000)/* No Error*/
--Declare @s nVarchar(MAX)/*No Error*/
Set
@s =N'Select 1'
exec
sp_executesql @s
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 6:52 AM | 1 vote
Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype. And my query length is more than 4000 characters and it is showing error. Check this screenshot
how do I solve this problem.
Hi Harsha !
DECLARE @SQLQuery NVARCHAR(MAX)
Please try to define it with NVARCHAR(MAX) and it will run your query.
Please let me know if this doesn’t work for you. Hope I have answered you correctly.
Thanks, Hasham Niaz
Friday, August 10, 2012 7:03 AM
When i try with nvarchar(max), im not getting any error but the characters are getting limited to 4000 even if set more than 4000 characters.
Friday, August 10, 2012 7:10 AM
Maximum allowed size for NVarchar is 4000.
@Latheesh, then is there any other possible way to execute my dynamic query with is more than 4000 characters.
Friday, August 10, 2012 7:14 AM
When i try with nvarchar(max), im not getting any error but the characters are getting limited to 4000 even if set more than 4000 characters.
Because fix strings are limited to 4000 chars. Split it into several string.
At all, why do you want to use such a large "dynamic" sql statement, IMHO it makes not really sense.
Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing
Friday, August 10, 2012 7:16 AM
Im using SQL Server 2012 and in my stored procedure, I am using a dynamic sql with a variable with nvarchar(max) datatype.
The "problem" is you don't use NVarchar(max), you use Nvarchar =>
DECLARE @SqlQuery AS NVarchar(max);Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing
@Olaf Helper, you are a genius, its working. what is the difference when we use small case and capital case, i mean NVarchar(max) and Nvarchar (max). how come it is working when i use nvarchar(max) or Nvarchar(max) ?
i need this dynamic sql for ssrs reports, i will be passing the parameters dynamically, like multiple values for single parameter.
Friday, August 10, 2012 7:17 AM
When i try with nvarchar(max), im not getting any error but the characters are getting limited to 4000 even if set more than 4000 characters.
How do you know that? Did you run SELECT len(@SQLQuery) to find out? Or are you lured by the default settings in SSMS? You can adjust the settings under Tools->Options->Results to Grid.
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Friday, August 10, 2012 7:18 AM
Try this: am not getting any error
Declare @s nVarchar(MAX)/*No Error*/Declare @s1 nvarchar(MAX)Set @s1 = Replicate('a',5000)Set @s ='Select '''+@s1 +''''print Len(@s)exec sp_executeSQL @s
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 7:19 AM
@Erland Sommarskog yes i check the with the query SELECT len(@SQLQuery) and it is showing 4000.
Friday, August 10, 2012 7:21 AM
Try this: am not getting any error
Declare @s nVarchar(MAX)/*No Error*/Declare @s1 nvarchar(MAX)Set @s1 = Replicate('a',5000)Set @s ='Select '''+@s1 +''''print Len(@s)exec sp_executeSQL @sPlease click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
@Latheesh NK, it will never show any error. but with nVarchar(max) (capital V) the length is limited to 4K.
Friday, August 10, 2012 7:24 AM
Absolutely, thats why i have provided both in my example. May be am not so specific.Anyway, nice to see you happy.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 7:43 AM
Have you declared @SQLQuery as nvarchar(Max)?
you can try this too
Declare @s Varchar(MAX)
Set @s ='Select '''+Replicate('a',5000) +''''
print Len(@s)
exec (@s)
-- This doesn't show any error
Regards
Satheesh
Friday, August 10, 2012 7:48 AM
Try this: am not getting any error
Declare @s nVarchar(MAX)/*No Error*/ Declare @s1 nvarchar(MAX) Set @s1 = Replicate('a',5000) Set @s ='Select '''+@s1 +'''' print Len(@s) exec sp_executeSQL @sPlease click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
@Latheesh NK, it will never show any error. but with nVarchar(max) (capital V) the length is limited to 4K.
Are you saying that when you run code below you get result of 4000 and 5009? Are you serious?
Declare @s nVarchar(MAX)
Declare @s1 nvarchar(MAX)
Set @s1 = Replicate('a',5000)
Set @s ='Select '''+@s1 +''''
print Len(@s)
print LEN(@s1)
everything is a matter of probability...
Friday, August 10, 2012 8:02 AM
Declare @s nVarchar(MAX)/*No Error*/Declare @s1 nvarchar(MAX)Set @s1 = Replicate('a',5000)Set @s ='Select '''+@s1 +''''print Len(@s)--5009print Len(@s1)--5000exec sp_executeSQL @s
I guess the miss is between my second thread and last thread. The above is the result set. Replicate('a',5000) always retruns 5000.
I ompared between my two threads not the two declarations in the last thread. Sorry for the mislead.
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 8:47 AM
To answer your question in the subject. Nvarchar is limited to 4000 characters because nvarchar(4000) and smaller is stored either in the same data page as the row or in an overflow page. nvarchar(max) can however also be stored in LOB pages. Data pages are 8192 bytes in size, some of these bytes are reserved for headers fields which leaves about 8000 bytes for data. Nvarchar uses 2 bytes per character so you can store a maximum of 4000 characters per page. Varchar uses 1 byte per character and therefor you can store a maximum of 8000 characters per page. In varchar, however, the different kinds of characters you can use is limited to the collation used, so if your don't need special characters you can also use varchar(5000) instead of nvarchar(max). For a local variable to store a dynamic query using nvarchar(max) is fine, though.
Friday, August 10, 2012 10:02 AM
its not working people, i really don't get it. i have wasted enough time on this.
here is my query, it is inside a stored procedure
Declare @SQLQuery nvarchar(max) set @SQLQuery = 'SELECT ................AND bd.billingdate >= ''' + CONVERT(nvarchar(10), @pBillingStartDate, 120) + '''.............' set @SQLQuery = @SQLQuery + ' ORDER BY Pg.[ProgramName] ,Inv.VLANumber /* Agreement Number */,So.UsageStartDate ' print ''print @SQLQueryprint ''print 'query length'print len(@SQLQuery)Execute sp_Executesql @SQLQuery
and when i execute this procedure, this is the output.
SELECT IIF(ISNULL(Pg.[ProgramName],'') = '','Unknown', Pg.[ProgramName]) AS 'Offering Name' ..........INNER JOIN [dbo].[SalesOrderAgreementParticipant] Par (NOLOCK) ON Par.[SalesOrderId] = SO.[SalesOquery length4000Msg 105, Level 15, State 1, Line 73Unclosed quotation mark after the character string 'SalesO'.Msg 102, Level 15, State 1, Line 73Incorrect syntax near 'SalesO'.
what am i missing here... ?
Friday, August 10, 2012 10:35 AM
Hi Hrasha,
Can you cast all parameter as nvarchar(Max).
Eg: CONVERT(nvarchar(MAX), @pBillingStartDate, 120)
Check the len printed in the below example for understanding:
Declare @s nvarchar(MAX)Declare @s1 nvarchar(MAX)Declare @d nvarchar(MAX)Set @d = 'ee'Set @s1 = Replicate('a',5000) + @dSet @s =N'Select '''+@s1 + ''''--Set @s = @s + '''22'''print Len(@s)print Len(@s1)exec sp_executeSQL @s/*Also try the below*/Declare @s nvarchar(MAX)Declare @s1 nvarchar(MAX)Declare @d nvarchar(MAX)Set @d = 'ee'Set @s1 = Cast(Replicate('a',5000) as nvarchar(max)) + @dSet @s =N'Select '''+@s1 + ''''--Set @s = @s + '''22'''print Len(@s)print Len(@s1)exec sp_executeSQL @s
Please click the Mark as Answer or Vote As Helpful button if a post solves your problem or is helpful!
Friday, August 10, 2012 11:00 AM
Latheesh NK, when i change the datatypes to nvarchar(max) in all the convert functions, it is working.
Erland Sommarskog, i guess you are rite, i passed the parameters separately and it worked. coming to the NOLOCK, im not sure i can remove it or not. the query was previously written by someone and i modified that sql into a dynamic one.
I will be calling this procedure from SSRS, rdl file. So there I have option for multiple selection for single parameter, so in that case, i will get the values in comma separated state. For example for city, when multiple cities are selected, i will get
'New York','Delhi','London'
above cities will come to a single parameter, so i suppose dynamic sql is the only thing we can do here, am i correct. if there are any other alternatives, please suggest me.
thanks for the answers
Tuesday, January 14, 2014 4:14 AM
I'm testing this answer and I understand that I may use nvarchar(max) to store to 2GB datatype. But I have the same trouble when I run this script:
Declare @s nvarchar(MAX)
Declare @s1 nvarchar(MAX)
Declare @d nvarchar(5)
Set @d = 'ee'
Set @s1 = Cast(Replicate('a',10000) as nvarchar(max)) + @d
Set @s =N'Select '''+@s1 + ''''
--Set @s = @s + '''22'''
print Len(@s)
print Len(@s1)
exec sp_executeSQL @s
I replicate the text an amount of 10000 but I have this result:
8011
8002
(1 row(s) affected)
What am I doing wrong?
Thanks for your help.
Tuesday, January 14, 2014 10:29 PM
Stop using dynamic SQL and learn write complete queries. Also, stop posting screen shots. We cannot do anything with them. What does show up, looks awful. You have ISNULL() and not COALESCE, embedded spaces in data element names in violation of ISO-11179, renaming for display purposes in violation of C/S, etc.
Want to post the actual code instead of a vague narrative?
--CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL
Tuesday, January 14, 2014 10:48 PM
So you are using an SSRS parameter with 'Data Type' = Text and 'Allow multiple values' = true?
In that case it is as easy as setting the Query type for your data source to 'Text' and pasting the whole thing into the 'Query:' box with your query written like this:
SELECT
<your_columns>
FROM
<your_table>
WHERE
<some_condition> IN (@SSRSParameter)
SSRS will take care of expanding the parameter all by itself without you having to do anything.
Tuesday, January 14, 2014 11:52 PM
Thanks a lot for your answer! I'll try to keep it in mind. For me this can be mark as answered. ;)
Thursday, October 30, 2014 1:50 PM
There is actually an easy answer to your first question. I had the same problem where my dynamic SQL was > 4000 characters. I used nvarchar(max) but it was still trimming at 4000 characters.
The issue is because when you use nvarchar (max) and concatenate other parameters to it, it no longer stays as nvarchar (max) but becomes nvarchar(4000). In you code you concatenate text with a nvarchar(10) and therefore you no longer have nvarchar(max). You need to convert the nvarchar(10) to nvarchar(max) as well.
Thats what I did - and it then worked perfectly.
Wednesday, May 30, 2018 3:44 PM
so... long story short...
when I had:
DECLARE @var varchar(MAX)
SET @var = 'abc..reallylong..code'
I noticed that it was being trimmed to 4000 (SELECT LEN(@var)).
The stupid answer is:
SET @var = N'abc..reallylong..code' + N'more...long...code'
basically, each time the specific string being appended was going to be exceptionally long, prefix with N to specify nvarchar (instead of varchar), and then the length could go higher (when I fixed, my string was over 8k)
Scott Brickey
MCTS, MCPD, MCITP
www.sbrickey.com
Strategic Data Systems - for all your SharePoint needs
Thursday, May 31, 2018 2:03 AM
Good night Scott Brickey!
For me this question was answered by davidbaxterbrowne and your answer is not correct.
If it were correct please tell me why this first scritp just with N prefix does not work and return a string len of 4011 and 4002 and the second works fine with return a string len of 10011 and 10002?
Frist Script Does not work fine
Declare @s nvarchar(MAX)
Declare @s1 nvarchar(MAX)
Declare @d nvarchar(5)
Set @d = N'ee' --Here is the N
Set @s1 = Cast(Replicate(N'a',10000) as nvarchar(max)) + @d --Here is the N too
Set @s = N'Select '''+@s1 + '''' --And here is the N again
--Set @s = @s + '''22'''
print Len(@s)
print Len(@s1)
exec sp_executeSQL @s
Second Script Works fine
Declare @s nvarchar(MAX)
Declare @s1 nvarchar(MAX)
Declare @d nvarchar(5)
Set @d = 'ee' --Here is not the N
Set @s1 = Replicate(cast('a' as nvarchar(max) ),10000) + @d --Here is not the N
Set @s = 'Select '''+@s1 + '''' --Here is not the N
--Set @s = @s + '''22'''
print Len(@s)
print Len(@s1)
exec sp_executeSQL @s
Thanks.
Jamesit0
Thursday, May 31, 2018 2:07 AM
And this does not work well eigther.
DECLARE @var varchar(MAX)
SET @var = Replicate(N'a',10000) + Replicate(N'a',10000)
SELECT LEN(@var)
Thanks.
Jamesit0
Thursday, May 31, 2018 7:05 AM
It works ... when you doing it the right way, see REPLICATE (Transact-SQL) "If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes" :
DECLARE @var varchar(MAX)
SET @var = Replicate(convert(nvarchar(max), N'a'),10000) + Replicate(convert(nvarchar(max), N'a'),10000)
SELECT LEN(@var)
Olaf Helper