What is the limitation in the length of an SqlCommand query

Question

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 :-)

Answer 1

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);

Answer 2

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.