The 4000 character limit for expressions
I am building rather large SQL statements using expressions and I am about to hit the 4000 character limit. First, what were the reasons for setting such a low limit? With it in place you are forced to come up with other solutions, but perhaps that was the intention.
Speaking of limits, what are the limits for storing a value in a string that is defined in a package configuration file? What are the limits when creating a string within a script task and then use it to set a string variable in the package? What are the limits when using a string value as an input parameter to an Execute SQL Task using OLE DB? Do these differ depending on the connection type?
I guess I'm getting a bit paranoid about limits, but I don't want to hit any others late in the design.
Regards,
Lars
[815 byte] By [
lasa] at [2007-12-23]
I can confirm that you can bypass the limit of 4000 characters by having your expression include a ? for OLE DB or a @VariableName for ADO.NET, then pass a variable from the parameters section of the Execute SQL Task. I still would like to know what the limit here is though.
Regards,
Lars
Do people no believe in stored procedures any more? Must everything be a dynamic string?
Why not create a proc where you have a 128MB limit on text (You ever gonna type that much?) and pass it parameters.
Neater
Faster
Easier to debug
Easier to read
Don't have to edit it and parse it while looking through a post box slit.
Is there any reason you need to build up a string and execute it when you could use a proc and pass it params?
I wish it was that simple. Creating dynamic SQL using Transact-SQL in a stored procedure is a lot of work. Especially if what you have to begin with is a large XML description of your input data. In that case, using a parameterized XSLT (which can only be done using expressions at the moment, since xsl:param is not supported) to build your SQL statement is much easier. A possible alternative would be to use a CLR procedure, but the current workaround using parameters is fine for now. Is the limit 128MB when passing a parameter to a statement as well?
Regards,
Lars
It just appears that that is the first thing people do.
Yes, it may be simplier to deploy (by a really small margin) but a nightmare to debug as you have to run the package, make a change, rerun the package, check results etc. This will be 1000 times worse at 2am when I am half awake and been called out...
Dynamic SQL in general is a waste of everyone's time... (My rule of thumb :) )
