Resave View Unless Error

[thankgodfortektips]

Hi All,

I have an ASP application which is working with an SQL 2000 database. The application has been working fine for months and lately I have started developing new areas in it.

Since the new developments I have been getting complaints about errors, sometimes it is that in a table in the asp app the data is from another field i.e. The StartDate data will come under the Name column. Or sometimes the ASP app cant read the data.

I have found the solution but I want to know why it is happening. The solution is to open up the views in Design view, run it, save it, close it... and we are good.

I have been editing the tables that the views are based on, so I assume it is this that is making it go wrong. But is there some kinda maintenance I should be running after adding fields to a table?

Thanks in advance!

 

[mrdenny]

If you have a SELECT * from a table in your view, you'll need to rebuild the view after the tables are updated. The views use column numbers in the backend to figure out where data goes, so if you change the number of columns it will through things off.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[thankgodfortektips]

MrDenny! Thanks for the advise! Is there any way to automate this... like for instance when in access you can do a compact and repair?

 

[RiverGuy]

Hey thankgodfortektips,

It's a good idea to save your SQL scripts when you create or modify objects in your database. You can then bring up your CREATE VIEW statement and use it in conjunction with your modifications.

 

[mrdenny]

The method would be what RiverGuy said. Keep a copy of the ALTER VIEW command around. Then when ever you run a script to alter the tables you can put the ALTER VIEW command after the ALTER table command.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog