question

Questions: 1) How and on what frequency is the metadata about database objects updated by MSSQL? 2) Is there a way to force a refresh? Situation: Requirement to insert imported data into current reference table in database. Receipt column "Location" is nvarchar(50), import column "Location" is nvarchar(100) and some data is larger than 50 characters. Insert fails due to truncation of inserted data. Nothing unexpected in this. Editing receipt table structure to increase nvarchar(50) to nvarchar(100) failed because it would require dropping table. **This is a good thing even on the Dev environment. Used \Task\Create Script to produce a script to create the table and include the data (only about 200 rows currently, will be importing several thousand). Modified script to make “Location” column nvarchar(100) and added another column “CityID” int. Dropped table and recreated with insert of existing data. All is well Insert fails again due to truncation of inserted data. Double checked import data and largest is well below 100 characters. Viewed receipt table structure and “Location” column is indeed nvarchar(100) and new column “CityID” is indeed present The intellisense does not recognize the new column (i.e. typing Receipt. will bring up the list of available columns but “CityID” is not in the list. I believe the metadata stored by MSSQL was not getting updated. While the first attempt to insert records into the Receipt table occurred within 5 minutes of modifying the length of the “Location” column the last was after about 90 minutes. The end of the day brought a temporary standoff, and the next day produced a correctly processed insert (and intellisense was again intelligent enough to recognize the new column. Thanks, Al Kessler PS - If I knew the answer I would make some form of QotD out of this ;).