Geek asks and answers

Handling more than 8000 characters in the stored parameter proc

I have a stored SQL procedure that sends mail. His signature is as follows:

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 a message contains, for example, 5000 characters, it works. When it is 12,000, I get an error[ODBC SQL Server Driver]String data, right truncation.

According to the help files, varchar (max) can process 2 ^ 31-1 bytes / characters. So I tried changing @Body varchar(max)to @Body varchar(30000), and I get an error message that

The size (30000) given to the type 'varchar' exceeds the maximum allowed for any data type (8000).

So max is 8000, not 2 ^ 31-1 bytes? How can I handle more than 8000 characters?

+3
source share
6 answers

nvarchar (max) varchar (4000) varchar (max). 2 , ...

. http://technet.microsoft.com/en-us/library/ms186939.aspx

+3

8060 (8K) - SQL Server, 8K...

varchar # 8000 nvarchar # 4000 ( char → 2 )

varchar (30000)

varchar (max) nvarchar (max) 2 ^ 31 ( 2 ), 2 ^ 30 ( 1 )

, SQL Server sp_send_dbmail, ...

+2

NVARCHAR(MAX) VARCHAR(MAX).

0

BLOB. , . , BLOB .

0

. 2 - 1 2- , NVARCHAR(MAX).

,

(2 ^ 31 - 1) / 2 = 1'037'741'823 double-byte characters

1 billion, 37 million, 741 thousand and 823 characters to be precise

NVARCHAR(MAX) ( , ...)

0

REPLICATE . , , :

@x varchar (max) set @x = replicate (cast ('a' varchar (max)), 10000) @x, len (@x)

, SQL Server REPLICATE , , , . , , , , , 8000 .

0

More articles:


All Articles