It would greatly help to know:
- the current Collation of the VARCHAR(MAX) column
- the exact string length (in bytes) at which you start seeing the error
- What software is reporting this error?
- Does the error occur if only a single Unicode character is present?
- Does a 10,000 character string with no Unicode characters get inserted correctly?
- For a short-enough string that gets inserted without error yet contains one of these Unicode characters, what value exactly is in the table? You can check by converting the column to
VARBINARY(MAX). Did the Unicode character get translated or something else?
It is possible that the string that errors is just over a 4000 or 8000 character limit that then requires using a MAX type and the driver is initially guessing (or being told) that it is one size and then finding out that it is another. The error message says "invalid precision value" which has to be referring to how the column is being configured (i.e. similar to setting up a SqlParameter in .NET and declaring the max size, such as new SqlParameter("@name", SqlDbType.VarChar, 8000)). So this type of error should only really happen if the software is attempting to set up the column as VARCHAR of over 8000 or NVARCHAR of over 4000, as either of those conditions would be an invalid "precision". Now, in order to get such a number I suspect that some code in there is counting the number of bytes (i.e. DATALENGTH ) of the string, which will be larger than the number of characters. Of course, this would be true of any character in a .NET string or C++ wchar, so I am suspecting that the difference with having some Unicode characters vs not having any is that without any Unicode characters it can convert the string to an 8-bit encoding (i.e. single byte for use with VARCHAR), but somehow having a Unicode character prevents that. It's a long-shot (until there is more info provided), but it is pretty clear where the error is occurring.
If I just insert the character alone it won't cause an issue.
How are you inserting the character? Through the same software that is generating the error, or in SSMS? If you are doing this manually via an INSERT statement, then that isn't a good test as SQL Server converts U+3000 into a regular space, U+0020 (one byte), instead of two questions marks (still 2 bytes, as would happen if there was no equivalent character):
SELECT N'-' + NCHAR(0x3000) + N'-',
CONVERT(VARCHAR(MAX), N'-' + NCHAR(0x3000) + N'-'),
CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), N'-' + NCHAR(0x3000) + N'-'));
returns:
- - - - 0x2D202D
If possible, trying passing in both of the strings generated by the following code via the software, not via SSMS:
DECLARE @String4k NVARCHAR(MAX);
SET @String4k = REPLICATE(N'a', 3999) + NCHAR(0x3000);
SELECT LEN(@String4k);
DECLARE @String8k NVARCHAR(MAX);
SET @String8k = REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7999);
SET @String8k += NCHAR(0x3000);
SELECT LEN(@String8k);
The @String4k string is 3999 characters that can convert cleanly into an 8-bit encoding (i.e. VARCHAR) plus the U+3000 character that will likely remain as 2 bytes. So maybe this comes across, through that software, as 4001 characters. I doubt that this is the issue, but can't hurt to test.
The @String8k string is 7999 characters that can convert cleanly into an 8-bit encoding (i.e. VARCHAR) plus the U+3000 character that will likely remain as 2 bytes. So maybe this comes across, through that software, as 8001 characters.
