Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jonnycattt
Starting Member
2 Posts |
 Posted - 2005-01-19 : 16:18:28
|
| HI All, I'm reasonably new to sql server, so please bear with me. I've inherited a stored procedure that dynamically generates a big sql statement then runs that sql statement. it selects about 130+ columns and then creates a series of @130 Left outer join calls and then @130 entries in the where clause. so the resultant FROM statement and WHERE clause are extremely long. Is there a limit on the length of these statements in SQL Server 2K? If so, can you point me to documentation on this? I'm having trouble finding it in BOL.Thanks!Marc |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-01-19 : 16:24:21
|
Look up EXEC in BOLquote:
Using EXECUTE with a Character StringUse the string concatenation operator (+) to create large strings for dynamic execution. Each string expression can be a mixture of Unicode and non-Unicode data types.Although each [N] 'tsql_string' or @string_variable must be less than 8,000 bytes, the concatenation is performed logically in the SQL Server parser and never materializes in memory. For example, this statement never produces the expected 16,000 concatenated character string:EXEC('name_of_8000_char_string' + 'another_name_of_8000_char_string')
Brett8-) |
 |
|
|
jonnycattt
Starting Member
2 Posts |
Posted - 2005-01-19 : 17:01:48
|
| thanks brett. i did some digging and found out that the problem was indeed a variable's length being exceeded in that generated FROM clause. cruised right past the 8000 limit. |
|
|
|
clarkbaker1964
Constraint Violating Yak Guru
428 Posts |
Posted - 2005-01-19 : 18:53:54
|
Can't you use ntext variable for this? |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2005-01-20 : 01:46:43
|
| >>>Can't you use ntext variable for this?<<<From BOL (DECLARE Overview) A variable cannot be of text, ntext, or image data type.HTH=================================================================Scriptures, n. The sacred books of our holy religion, as distinguished from the false and profane writings on which all other faiths are based.-Ambrose Bierce, writer (1842-1914) [The Devil's Dictionary] |
|
|
|
|
|
|
|
|
limits on query length?