MS SQL Problem - Field length being limited

Question

I have a MS SQL DB with various tables, and one field in particular is causing me grief.

The Data type is set to varchar(100), however the field is limited to 60 characters.

If I try to put any string with more than 60 characters into the field I get an exception, "String or binary data would be truncated". Although the string is shorter than the explicitly set data type, it still throws an exception.

Is there perhaps a DB setting that does this? What could cause the explicitly set data type to be overwritten?

Edit:
The triggers do not copy the value or insert it into another table and they don't use the data either. - (Incorrect)

Strings that are smaller than 60 chars work fine.

All the columns that have varchar(100) give the same problem, but all the other columns accept correct values. The varchar(10) column works fine.

Any row in this table throws the exception if I try to update the field with a string longer than 60 chars.

I am trying to insert the data directly into the field using SQL Server Management Studio.

There is no padding involved.

Answer:

There was a second table where the column was set to 60. The update trigger called a stored Procedure that inserts the data into the "Denormalised" table.

Thanks for all the help.

Answer 1

Can you clarify exactly how you are inserting this data. Do you get the same result whether it's a direct INSERT statement that through a stored proc. If it's a stored proc is this where the character limit is being set? Also, are there any triggers on that table?

Answer 2

Perhaps you need NVARCHAR(100) or rather NVARCHAR(60).

Single character in the NVARCHAR is double the size of VARCHAR. You would use NVARCHAR if your input data is unicode

EDIT:

Based on your comments it looks like using nvarchar is not necessary a solution to the problem, and it is pretty hard to guess what the issues are with the information provided so far:

Could you script your table, with constraints and triggers and post the code, that would definitely help in finding the source of the problem.

Answer 3

Do you have many columns in that table? Can you give us an example of the text you're inserting that gives you troubles?

Answer 4

Do you insert from a .Net application? Perhaps there's an error in the parameter definition of the SqlCommand

Answer 5

Chris: I got the exception through my web app first, but then I tried to add the data directly into the table via SQL Server Management Studio.

So I get the exception regardless of where the info is being inserted from.

I checked the triggers and none of them would limit the field and there are no Database wide triggers either.

Kristof: NVARCHAR is kind of a last resort, because I only have a copy of the original DB.

I doubt that the client wants me to change their live DB.

Lassevk: There are only 14 columns in the table, and the table stores Address data.

"The school is in the 1st location on that road. The location is Lower Npunpuzo."

This is the weird part, that string was already in the DB. But if I try to edit/update the record the exception is thrown.

Answer 6

How large are the columns in the rest of the row? A row is limited to 8060 bytes, across all columns; the maximum size of your columns can be larger than this, as long as the total size of the values within them don't cross this boundary.

Also note that nvarchar uses 2 bytes per character, so nvarchar(100) uses up to 200 bytes.

edit So we've ruled out the row size. You said there is a trigger on the table. As Jim McLeod asked, what does it do? i.e. does it insert into another table that has a smaller column size OR where the row size would go over the 8060 boundary?

Answer 7

(int, not null, PK): 105970
(int, not null): 62704
(int, not null): 1
(int, not null): 7
(varchar(100), null): Take road to Ncora from Cofimvaba (gravel rd)
(varchar(100), null): The school is in the 1st location on that road. The location is Lower Npunpuzo. (varchar(100), null): If you reach Lower Npunpuzo ask for directions to school.
(varchar(100), null): Cofimvaba
(int, null): 196
(varchar(10), null): 5380
(tinyint, null): 0
(tinyint, null): 0
(int, null): 0
(datetime, null): 1900-01-01 00:00:00.000

The bold text is the problem. As I said earlier, that string was already in the DB without problems. For some reason its giving problems now.

Edit:
I just changed the data type to nvarchar(100) and it didn't work, I also tried varchar(255) without success.

Answer 8

What does the metadata function COL_LENGTH say is the defined size of that column?

Do you have any default length constraint on that column?

Assuming from the previous answers that this is not a trigger problem or an nvarchar issue, and you think the truncation is at length 60, what does updating that single column with SUBSTRING of length 60 and then 61 do? This could validate or invalidate your theory.

Alternatively it's possible that the database collation and/or encoding settings have changed since the original data was inserted. This can lead to some peculiarities. You say this is a copy of the original database. Is it sitting on a different SQL Server instance? If so, do both SQL Server instances have the same collation and encoding settings?

EDIT: Use of the ANSI_PADDING setting that you discuss is deprecated, and the setting will be permanently ON in future versions of SQL Server. But the fact that you're looking at this suggests that the value you're trying to insert is padded in some way, perhaps with trailing blanks. However, this isn't consistent with your SUBSTRING experiment results, which shows a cut-off at 60 characters. So I'm not sure if this setting is relevant, especially as it is always ON for a nvarchar column.

Does any string of 61 characters cause the update exception? Also, although you've checked the immediate table triggers, are there any cascaded (indirect) triggers that might be causing this exception?

Answer 9

The exception is thrown on the original DB as well as my copy, regardless of the Server Instance.

COL_LENGTH returns 100.

Inserting a string of 61 characters throws an exception.

I created a new table with the same columns, schema, etc. but I set ANSI_Padding off. For some reason it is set on in the table.

Is there a way to change the ANSI_Padding status once a table has been created and are there any repercussions for doing so?

Answer 10

Do you have any triggers on the table you are inserting into that might be trying to use the data?