What is the limitation in the length of an SqlCommand query
Solution 1
SqlServer 2000 has a 4000 character query limit for adhoc queries.
Can you abstract this into a stored procedure?
Solution 2
Here is a thought:
SQLServer 2000's VARCHAR allows up to 8000 characters, so this might work:
PSeudoCode:
SQLCommand command = new SqlCommand("exec sp_executeSQL @CMD");
command.Parameters.Add(new SqlParameter("@CMD",YourDynamicSQL, VARCHAR);
Solution 3
a must read for dynamic queries... The Curse and Blessings of Dynamic SQL, I highly recommend you read it. Might not help you this time but it'll definitely help you in the future..
A quote from the article, just in case.
sp_executesql and Long SQL Strings in SQL 2000
There is a limitation with sp_executesql on SQL 2000 and SQL 7, since you cannot use longer SQL strings than 4000 characters. (On SQL 2005 and later, you should use nvarchar(MAX) to avoid this problem.) If you want to use sp_executesql when your query string exceeds this limit to make use of parameterised query plans, there is actually a workaround. To wit, you can wrap sp_executesql in EXEC():
DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @state char(2) SELECT @state = 'CA' SELECT @sql1 = N'SELECT COUNT(*)' SELECT @sql2 = N'FROM dbo.authors WHERE state = @state' EXEC('EXEC sp_executesql N''' + @sql1 + @sql2 + ''', N''@state char(2)'', @state = ''' + @state + '''')
This works, because the @stmt parameter to sp_executesql is ntext, so by itself, it does not have any limitation in size.
You can even use output parameters by using INSERT-EXEC, as in this example:
CREATE TABLE #result (cnt int NOT NULL) DECLARE @sql1 nvarchar(4000), @sql2 nvarchar(4000), @state char(2), @mycnt int SELECT @state = 'CA' SELECT @sql1 = N'SELECT @cnt = COUNT(*)' SELECT @sql2 = N'FROM dbo.authors WHERE state = @state' INSERT #result (cnt) EXEC('DECLARE @cnt int EXEC sp_executesql N''' + @sql1 + @sql2 + ''', N''@state char(2), @cnt int OUTPUT'', @state = ''' + @state + ''', @cnt = @cnt OUTPUT SELECT @cnt') SELECT @mycnt = cnt FROM #result
You have my understanding if you think this is too messy to be worth it.
Solution 4
I've run into a 2k limit for queries run against an AS/400. I usually managed to get under the 2k limit by removing all whitespace - it makes the query unreadable but it's the simplest way to get under the limit.
Solution 5
In my own experience, I found that, what first seemed to be a SQLServer2000 limit on the length of queries, was actually (believe it or not) not really a limit on the length of a query, but, is a limit on the length of any given LINE in the query.
It was about a year ago when I ran into this, so off the top of my head I don't remember what the line-length was, but you could try splitting the huge query into lines of max line length 64K or thereabouts, and see how it goes. My recollection is, that the line length limit might have been 64K, believe it or not. I had taken this insanely-long query (was generated by a sql-generator program), the query was about 80K long, and I split it in half in Notepad (i.e., I put a linefeed in the SQL code at about the halfway point --- but I made sure not to split up any words), and then pasted the whole thing into Query Analyzer command window. Then it worked, having the linefeed somewhere in the middle thus causing each of the 2 lines to be less than 64K long. I hope this helps.
If not, try smaller line lengths. I am certain that when I got my query to the point where no line within it, exceeded a certain length, the overall query worked.
Michael Prewecki
Software Developer working primarily with C#. Programming Skills include but not limited to T-SQL, C# and VB.NET.
Updated on June 04, 2022Comments
-
Michael Prewecki almost 2 years
Is there a limitation in the length of a query that SQL Server can handle?
I have a normal SqlCommand object and pass a very long select statement as a string.
The query seems to be fine when running against an SQL Server 2005/2008 engine but doesn't execute against an SQL Server 2000 engine.
I don't have any error details as I only have this information 3rd hand but my application isn't working as expected. I could go to the trouble of installing an SQL Server 2000 instance but I was just wondering if anyone has a quick. Yes there is a 4K or 8K limit in SQL Server 2000 but not in 2005 type answer.
I'm aware that I could use stored procedures but lets assume I have a valid reason for not using them :-)
-
Michael Prewecki over 15 yearsThat's what I feared. Yes I can, the problem is I don't have management over the database which means adding sprocs etc isn't under my control.
-
Michael Prewecki over 15 yearsAS I said, I don't have control over the database. But yes i'm aware of the potential for SQL injections. Since the SQL Server is very locked down and the app is on a closed network it isn't a major consideration at this time.
-
Michael Prewecki over 15 yearsThat's a great idea and something i'll use if I can't get the query under 4K
-
Michael Prewecki over 15 yearsOh and I do sanitise the user input but you can never be sure now can you.
-
Michael Prewecki over 15 yearsIt certainly is, i'm having to do some serious column manipulations...so dynamically perform LEFT, RIGHT, REPLACE operations on multiple columns.
-
JoshBerke over 15 yearsI'm lurkin...heh dynamic sql is good if you parametize your queries...(especially if the paramters are from user input)