ma*****@tmw.co.uk (Miles Ashton) wrote in message news:<bb**************************@posting.google. com>...
A common request for enhancement to applications is to "make this
field bigger". I know I've been caught with increasing a field size,
and then spending hours debugging because another stored proc has a
variable or temp table that uses the field defined as the original
varchar size. SQL Server just truncates to fit the data into the
smaller varchar, and so there are no errors raised.
An option suggested by a colleague is to no longer use varchars, but
use User Defined Types instead. To make this work effectively,
though, they suggest we would need to make it a rule that we do not
use varchars anywhere except to define user defined types.
Though there will be one point of changes I can't help thinking this
isn't a very good idea ! Any thoughts ?
Thanks.
MSSQL will raise an error and will not INSERT the data if SET
ANSI_WARNINGS is ON, so I guess for some reason you have it OFF. This
is not recommended, because some queries (distributed queries, indexed
views) require it to be ON.
If you were to use a UDDT, then you would have the issue that you
cannot modify a UDDT, you can only drop and recreate it. So to change
your definition, you would need to unbind it, drop it, recreate it and
bind it again. This may or may not be less effort than modifying your
stored procedure code.
I suspect that the best solution to your issue is to use source code
control, and understand what your dependencies are. Although
sp_depends is not 100% reliable, you can query syscomments directly to
find out which procedures reference a table.
Simon