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.
| Author |
Topic |
|
pradeepv
Starting Member
2 Posts |
 Posted - 2012-12-10 : 11:00:48
|
| Hi all,I have a query more than 4000 characters limit .I am setting with variables to the query.while i used the string expression i am getting error like string expression is limited to 4000 characters limit.Is any one have idea about how to eliminate the length limit in expression. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-10 : 12:59:52
|
| It could be that you have declarations such as NVARCHAR(4000) somewhere in your code. You can change those to NVARCHAR(MAX) to go beyond 4000. |
 |
|
|
pradeepv
Starting Member
2 Posts |
Posted - 2012-12-11 : 13:46:14
|
| Actually i am giving the expression to set up the variable value in SQL command but the query is too long .In the expression the string is not taking more than 4000 characters limit.I don't have any idea how to eliminate this length. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-12-11 : 14:12:13
|
Someone on the forum would be able to suggest the changes you need to make if you post (simplified) sample code that demonstrates the problem. It is hard to guess without that. For example, see the following:DECLARE @s0 NVARCHAR(4000) = REPLICATE('A',4000);DECLARE @s1 NVARCHAR(MAX) = REPLICATE (@s0,4);SELECT LEN(@s1)-- returns 4000 as length even though @s1 is declared as nvarchar(max)-- this is expected behavior. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-12-14 : 02:21:13
|
quote:
Originally posted by pradeepv
Actually i am giving the expression to set up the variable value in SQL command but the query is too long .In the expression the string is not taking more than 4000 characters limit.I don't have any idea how to eliminate this length.
if you're referring to 4000 limit inside SSIS, then yes its a limitation and has been resolved only in 2012. What you could do as a workaround is to wrap query in a procedure with parameter and then inside variable call statement to execute procedure with parameter value appended rather than passing full log query inside variable which is why it crossed 4000 limit. then use this variable with EXEC statement as Source for SQL commend------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
String Expression 4000 characters limit