Handling more than 8000 chars in stored proc parameter
sql-server, t-sql
Solution
You need to use nvarchar(max), instead of varchar(4000) or varchar(max). This can store up to 2 GB of text, which will solve your problem...
For more information see http://technet.microsoft.com/en-us/library/ms186939.aspx
Problem
I have a SQL Stored procedure that sends a mail. It's signature looks like this: ``` CREATE PROCEDURE SendMail @From varchar(40), @To varchar(255), @Subject varchar(255), @Body varchar(max), @CC varchar(255) = null, @BCC varchar(255) = null AS... ``` When the message is for example 5000 characters it work. When it is 12 000, I get an error that `[ODBC SQL Server Driver]String data, right truncation.` According to the help files varchar(max) can handle 2^31-1 bytes / characters. So I tried changing `@Body varchar(max)` to `@Body varchar(30000)` and I get an error that ``` The size (30000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000). ``` So the max is 8000 and not 2^31-1 bytes? How can I handle more than 8000 characters?