5

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?

Cameron Castillo
  • 2,712
  • 10
  • 47
  • 77
  • 2
    I assume that the problem is not with the MSSQL side but rather with the client library you're using to connect to the database. What code is behind that part ? (PS: varchar(max) is any size between 0 and 2Gb but internally it's handled differently than varchar(1-8000), then again it should not cause problems when used as a parameter). – deroby Feb 11 '14 at 11:46
  • Could you add a comment on how the accepted answer helped you out so anyone tumbling upon this knows what to do too? – deroby Feb 11 '14 at 20:10

6 Answers6

5

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

Mez
  • 4,666
  • 4
  • 29
  • 57
  • Thanks, but the error still remains when passing more than 8000 characters. I also tried declaring @Body nvarchar(30000) as a 2nd test but the error remains "The size (30000) given to the type 'nvarchar' exceeds the maximum allowed for any data type (8000)" – Cameron Castillo Feb 11 '14 at 11:38
  • As the error states that you can't declare @body with nvarchar(30000). As stephen said you need to use nvarchar(max) – Krishnraj Rana Feb 11 '14 at 11:46
  • Thanks. I was hoping my comments was clear, but the 1st try I did use nvarchar(max), the 2nd try was nvarchar(8000). I do believe there might be another type conversion as well. Will post soon. – Cameron Castillo Feb 11 '14 at 11:54
  • You can check also text - but I believe nvarchar(max) is ok for you @user1208908. If the answer helped you and answered you problem do not forget to mark as answer. – Mez Feb 11 '14 at 11:55
  • 1
    Another thing to note, that resulted in the follow up error, is that when connecting via ADO and passing parameters you should use adLongVarWChar with nvarchar. – Cameron Castillo Feb 11 '14 at 14:47
2

Text fields cannot be larger than 8060 Bytes (8K) due to SQL Server Page Size which is 8K...

varchar has a maximum #of chars of 8000 nvarchar has a maximum #of chars of 4000 (each char-->2 bytes)

You cannot declare a parameter varchar(30000)

You should use varchar(max) or nvarchar(max) the first has 2^31 chars (approx 2billions), the latter has 2^30 chars (approx 1billion)

Also, please note that SQL Server has a Stored Proc Named sp_send_dbmail that you can use to sen emails...

Antonio
  • 535
  • 10
  • 26
0

Try using NVARCHAR(MAX) instead of VARCHAR(MAX).

BAdmin
  • 927
  • 1
  • 11
  • 19
  • You can not set size to NVARCHAR (Or VARCHAR) to more than 8000. Have you tried declaring @Body NVARCHAR(MAX) ? – BAdmin Feb 11 '14 at 11:53
0

Use the BLOB data type. I use it occasionally for very long fields but it cannot be compared. I do not believe there is a max length on BLOB.

Tony
  • 801
  • 1
  • 7
  • 22
  • 1
    Although text and image fields are still available they are really clunky to work with. varchar(max) and varbinary(max) work just as well, if not better. – deroby Feb 11 '14 at 11:48
  • I agree but the OP seemed to be having issues so I thought I would throw out another option. – Tony Feb 11 '14 at 11:59
0

Max. capacity is 2 GByte of space - so you're looking at just over 1 billion 2-byte characters that will fit into a NVARCHAR(MAX) field.

Using the other answer's more detailed numbers, you should be able to store

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

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

in your NVARCHAR(MAX) column (unfortunately, that last half character is wasted...)

SOURCE

Community
  • 1
  • 1
Hoh
  • 1,196
  • 1
  • 12
  • 30
0

REPLICATE returns the input type irrespective of later assignment. It's annoying, but to avoid silent truncation, try this example:

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

This is because SQL Server performs the REPLICATE operation before it considers what you're assigning it to or how many characters you're trying to expand it to. It only cares about the input expression to determine what it should return, and if the input is not a max type, it assumes it is meant to fit within 8,000 bytes.

Mala
  • 1,119
  • 7
  • 4