sql- exceeding variable size in a exec?

I inherited some partially complete sql code that I can't get to work.

it accesses multiple databases, so it first searches for proper database using a userID number, then inserts that database name into a query. the part i'm having a problem with (extremely abbreviated) is...

DECLARE @sql AS VARCHAR(8000)  
    SET @sql = 'INSERT INTO ['+@DatabaseName+'].dbo.[customer]  
                  ( -- containing about 200 columns. )  
                VALUES(...)'  
PRINT @sql  
EXEC(@sql)

i would get errors in the middle of a column name, sometimes saying it's expecting a parenthesis or quote. i started deleting white space so that, ie, [first name],[last name] were on the same line and not two different lines and that would get me a little further down the query. i don't have much more white spaces i开发者_如何学运维 can delete and i'm only just getting into the Values(...) portion of it. the weird thing is. i copy and pasted just the columns portion and put it into Word and it comes up as being only about 3,000 characters, including white space.

am i missing something?

if it means anything, i'm running microsoft sql server 2005, and using the sql server management studio for editing

thanks!

See here: SQL Server: When 8000 Characters Is Not Enough for a couple of solutions

extremely abbreviated

Well, that doesn't really help since you have likely abbreviated away the cause of the issue.

If I were to guess, I have seen cases where NCHAR or CHAR variables/columns were involved. These expand to their full length when used in string concatenation and it will cause the final statement to be too long.

For what it's worth for style or otherwise, use NVarchar(Max) always for SQL Server 2005 and onwards. In fact, that is the expected type if you use sp_executesql.

If you check for fixed-width N/CHAR columns and switch to nvarchar(max), you may see the problem go away.

EDIT: Test showing NVarchar(Max) holding well in excess of 8000 bytes.

declare @sql nvarchar(max)

-- this CTE sets up the columns, 1 as field1, 2 as field2 etc
-- it creates 2000 columns
;with CTE(n, t) AS (
    select 1, convert(nvarchar(max),'1 as field1')
    union all
    select n+1, convert(nvarchar(max),RIGHT(n, 12) + ' as field'+RIGHT(n, 12))
    from cte
    where N < 2000)
select @sql = coalesce(@sql+',','') + t
from CTE
option (maxrecursion 2000)   -- needed, the default of 100 is not nearly enough

-- add the SELECT bit to make a proper SQL statement
set @sql = 'select ' + @sql

-- check the length : 33786
select LEN(@sql)

-- check the content
print @sql

-- execute to get the columns
exec (@sql)

Use an nvarchar(max) datatype for @sql.