I have been maintaining an ad-hoc report lately. The report uses a stored procedure to perform Cross-Tab functions on a resultset. The stored procedure works by passing in an SQL string, which in my case, is a string of generated SQL. Yesterday, I added some additional functionality to the report and the stored procedure failed. After spending quite a while on diagnosing the problem, I found the maximum length of the input parameter was 8,000 characters. My report generated an SQL string of 12,000 characters.
Before I continue, I want to note the error messages returned from the stored procedure were syntax errors. Executing the exact same SQL statement with QueryAnalyzer returned a proper resultset. Not once did I get a useful message such as, "Hey Jerko, your parameter is too long".
For this report, the SQL Statement was generated at runtime. I needed a way to shorten the SQL Statement and still preserve the flexibility of the runtime SQL generation. After talking this through with my friend and co-worker, Bassil Karam, he recommended I replace a portion of the SQL statement with a view. Once I did that, I was able to shorten up the generated SQL to fit inside the input parameter of the Stored Procedure and my report ran just fine.
As an aside, the project I am working on has approached the technical limits of SQL server quite a few times. For example, I remain shocked at the table row limit. There are a lot of things right about MS SQL Server, but there are still some weak areas. Maybe left over limitations from when SQL Server was MS Access? ( Ha Ha Ha )