I have a strange problem with a nvarchar(255) field which won't accept more than 10 characters.
I've tried a few different data types including char, varchar(255), nvarchar(MAX) and varchar(MAX) but I get the following message:
Msg 8152, Level 16, State 14, Procedure onupd, Line 12
String or binary data would be truncated.
The statement has been terminated.
The code I am using for instance is:
update tblOrder
set OrderID = '151012222202551551515151514'
where OrderID = '151011'
Whereas this would work:
update tblOrder
set OrderID = '1510122212'
where OrderID = '151011'
if I turn the ANSI warnings off then the transaction completes successfully but I don't want to do this in our production environment as it could of course cause a lot of issues!
I'm using SQL Server 2008 R2 and the field was originally varchar(255) so I never thought there would be a problem!
I'm a little bit stumped so any advice would be appreciated.
say about the datatype, and size, of the OrderID column?
You aren't specifying a schema (e.g. UPDATE dbo.tblOrder) so there is a risk that there are two tables called tblOrder - one in the schema which is the default when you are logged on and, say, another for the dbo schema. (I recommend that you always specify a schema a) to avoid that risk and b) because it saves time as SQL does not have to check IF there IS an object in the default schema with that name ... discovering there isn't ... and then trying dbo instead.
Its also possible - despite the object name prefix ... - that your tblOrder is actually mapping a VIEW and that is mapping OrderID to a column that has a 10 character limit.
Or it might be mapping to a SYNONYM bit of a long shot as I expect they are rare!)
sp_help will clarify if it is any of those causes.
Other possibility is that something, somewhere, is using a VARCHAR definition with no size defined, and SQL is providing a default size (using VARCHAR without a size is an accident waiting to happen I'm afraid - in some situations SQL will even default that to Size=1 )
Lastly, maybe there is a TRIGGER on 'tblOrder' which is truncating the value (perhaps because the trigger is processing the data in some way that uses @Variables that are defined too narrow)
EXEC sp_depends 'tblOrder'
will tell you if there is a trigger (see the TYPE column in the results)
I don't think that is it because you error message says Procedure onupd, Line 12 and I would have expected it to say "Trigger" in that message instead.
Do you mean "with no warning" or "With no truncation" ?
P.S. what is the code at Line 12 of that procedure? Is that ACTUALLY the UPDATE statement that you are referring to? Might be something else altogether and the error message is just leading you to think that's the problem ...
create procedure sys.sp_depends --- 1996/08/09 16:51
@objname nvarchar(776) -- the object we want to check
as
declare @objid int -- the id of the object we want
declare @found_some bit -- flag for dependencies found
declare @dbname sysname
OK -- so that should mean it has no dependencies, up or down, which is good . Your other error message mentions Procedure onupd, which sounds like a trigger. Can you find the definition for that proc and post it?
OK, well whilst that helps there isn;t a @Variable limited to 10 characters ...
But there is a fundamental error in that trigger. The trigger has been written to ONLY work when a SINGLE ROW is updated. So its a huge accidental-waiting-to-happen if someone does an UPDATE statement that changes more than one row ...
(Hopefully you didn't write that trigger?! If not then find whoever did and shoot them!!)
Line Numbers in error messages can be a bit ambiguous, but either way "line 12" is going to be one of those two UPDATE statements.
So I expect you will find that either tblWorkCentreOrder table has the column Order that is too narrow to take the new, wider, reference, or tblCEevent has that problem ... or both of them do
will show you the column definitions for those two tables - or you can look in SSMS of course.
But either way that trigger is a disaster waiting to happen and you need to get it fixed. Its a common newbie mistake, so you may find some/many other triggers on your system are incorrectly written to only process a single row - in which case they will need fixing too.
Bit worrying that the Dependencies report didn't show that there was a Trigger on that table ...
I am also slightly confused that the error messages says "procedure" rather than "trigger" ... but maybe I've just mis-remembered and that is indeed how SQL displays it
There is an obvious problem with the trigger. It assumes that it is operating on one row. But a trigger is not called once for every row that is updated, it is called once for the update operation, whether one row or a hundred are affected.
Also, you can see that the trigger sets the maximum length to 100 characters. Which is causing the error message you see.
You need to rewrite the trigger to use set-based logic and use the lengths of the actual columns
That will be an [additional] problem, because the underlying column is width=250
But the current problem is raising error if longer than 10 characters, hence my suggestion that it is the column size in the other tables being updated that is the limitation.
Basically the code-quality is crap:
Arbitrary length used for @TempVariabes
no error checking or handling
column names inconsistent (OrderID vs. Order)
different sizes for columns that hold the same information
(and, ipso facto, no Referential Integrity defined)
triggers written with no knowledge of requirements (for triggers)
Thank you for your help with this. It turns out that the Order table in the tblCEEvent table was set to char(10) so I've now corrected this which has fixed the issue.
I didn't realise at first glance that a trigger was being used, I simply though my update statement was only going to affect the context of the table and column in the query...
The database was originally designed in 2005 and is part of a project I picked up internally a couple of years ago. I luckily, or unluckily, didn't design the database or original application. Most of the work I've done has been on the .NET front end rather than database wise (bar adding a few stored procedures etc).
I will spend some time looking at and rectifying the database taking on board your advice. The mismatch between the column lengths and data types does seem to just be the start! Why anyone would actually call a column Order is beyond me (surprised SQL Server allows it....)
In the meantime I used alter table tblCEEvent alter column [Order] char(255) to alter the columns' length negating the need to copy the data to a new column and then drop/recreate it.
You can use a reserved word for a Table / Column name, but where it is ambiguous [in context] you will have to "quote" the column name - normally like this [Order] but other wrapping styles are available.
Note that its varchar(200) in `tblOrder", and not 255. I recommend that they are all the same rather than "as big as might be needed". In fact 255 seems pretty big to me (but I don't know what it is being asked to hold). SQL will allocate memory based on the MAX size of an object, so sizing "bigger than needed" does have a downside.
The key thing to fix is that Trigger. If you have any existing processes that update more than one row then only one of the rows in the batch (at random) will actually change the other two tables
Given that that programming error exists I would check if there are other triggers - just in case there are triggers which also only process one row from the set, but in more serious scenarios than this one.
)
