What is the SqlCommand request length limit

Question

Is there a limit to the length of a query that SQL Server can handle?

I have a regular SqlCommand object and pass a very long select statement as a string.

It appears that the query is executed while working with the SQL Server 2005/2008 engine, but is not executed with the SQL Server 2000 engine.

I don't have any error data since I only have this information, but my application does not work as expected. I could solve the problem of installing an instance of SQL Server 2000, but I was just wondering if anyone has a quick way. Yes, there is a 4K or 8K limitation in SQL Server 2000, but not in a type 2005 response.

I know that I can use stored procedures, but I admit that I have a good reason not to use them :-)

Answer 1

SqlServer 2000 has a 4000 character limit for adhoc requests.

Can you abstract this in a stored procedure?

Answer 2

Here is a thought:

SQLServer 2000 VARCHAR allows up to 8000 characters, so this might work:

pseudo code:

SQLCommand command = new SqlCommand("exec sp_executeSQL @CMD"); command.Parameters.Add(new SqlParameter("@CMD",YourDynamicSQL, VARCHAR); 

Answer 3

a must read for dynamic queries ... Curse and blessings of dynamic SQL , I highly recommend that you read it. This time will not help you, but it will definitely help you in the future.

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. (For SQL Server 2005 and later, you should use nvarchar (MAX) to avoid this problem.) If you want to use sp_executesql when the query string exceeds this limit to use parameterized query plans, there is actually a workaround. To do this, 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 for sp_executesql is ntext, so by itself it has no size restrictions.

You can even use the output parameters with 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 it is too dirty to be worth it.

Answer 4

I ran into a 2k limit for requests running against AS / 400. Usually I managed to overcome the 2k limit by removing all spaces - this makes the request unreadable, but this is the easiest way to get the limit.

Answer 5

In my own experience, I found that the first, seemingly limiting, length of SQLServer2000 queries is not really (believe it or not) a query length limit, but a limit on the length of any given LINE in a query.
That was about a year ago when I came across this, so from head to toe I don’t remember what string length is, but you can try breaking up a huge query into strings with a maximum string length of 64K or so and see how it happens. My recollection is that the string length limit can be 64 KB, believe it or not. I took this insanely long query (it was generated by sql-generator), the query was about 80K in length, and I split it in half in Notepad (that is, I put the line in the SQL code about half a point --- but I didn’t smooth a word), and then put it all into the Query Analyzer command window. Then it worked, having a straight line somewhere in the middle, causing each of the two lines to be less than 64 KB in length. Hope this helps. If not, try decreasing the string length. I am sure that when I received my request before the moment when no line inside it exceeded a certain length, the general request worked.

Answer 6

Do not do this due to sql injections. Discard this if the dynamic sql application can be manipulated by the user at all.

also - consider SP as it is easier to maintain and also helps with SQL injection.