SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 String Expression 4000 characters limit		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

pradeepv
Starting Member

USA
2 Posts
Posted - 12/10/2012 :  11:00:48  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/10/2012 :  12:59:52  Show Profile  Reply with Quote
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.
Go to Top of Page

pradeepv
Starting Member

USA
2 Posts
Posted - 12/11/2012 :  13:46:14  Show Profile  Reply with Quote
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.
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5152 Posts
Posted - 12/11/2012 :  14:12:13  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
48076 Posts
Posted - 12/14/2012 :  02:21:13  Show Profile  Reply with Quote
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 MVP
http://visakhm.blogspot.com/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000