I had a query against a table that would change based on the day (mytable_20120131). Copied and pasted it into a nvarchar(max) variable, tried to get it to run multiple times without luck. Odd error messages - number of selects doesn't match number of inserts, "incorrect syntax near the keyword 'on'" (just means there's a problem somewhere in the code), problem near a comma, etc. Was using nvarchar(max) since I'm plugging it into sp_executesql. Oh, and it's an explicit insert with over 140 fields.
Coworker had the bright idea to look at where the dynamic table name was added. Did a "select @sql", copied and pasted into notepad.
declare @sql nvarchar(max), @mytable nvarchar(max)
set @mytable = 'dbo.mytable'
set @sql = 'insert into mytable ([myfielda],[myfieldb],..." + @mytable + ' on ...'
Here's what it looked like around dbo.mytable when I did the select:
Turns out, there's a length to the string assignment: 4000 characters. I normally don't run into it because my code normally looks like this when I need dynamic sql, but I didn't want to have to deal with splitting the 30 lines I'd need:
set @sql = 'insert into mytable ([myfielda],[myfieldb],.."
Fix? In this case, an easy one:
set @sql = N'insert into mytable ([myfielda],[myfieldb],..." + @mytable + N' on ...'
It appears that because it's all nvarchar-assigned, that it never runs into the limit.