I recently came across a problem that was difficult to diagnose. After I had changed the datatype in a table from varchar(256) to varchar(512) my Access 2013 reports show data truncated to 256 characters. It turned out that the views that I’d created against the base table were truncating the field.
After changing the datatype in a table it is necessary to drop and re-create any views that reference it. This is very easy to do by scripting out the view and executing the script.
- Drop and re-create dependant views after changing datatypes
Xian Wang said:
You’d probably want to instead refresh the view by using sp_refreshview . Scripting out the drop/recreate could erase security or other metadata on the object if not careful.
Dr James Bayley said:
Thank you Xian for this excellent advice. The MSDN documenation also gives a nice script for finding and updating all views that depend on a particular object (table). I think this will be very useful during database development.