difference between view and procedure

  • Thread starter Thread starter Educo Gent
  • Start date Start date
E

Educo Gent

I use microsoft access 2002 and sqlserver 2000.

I have created a view and in that view I select all fields of a table
(SELECT dbo.table_name.* FROM dbo.table_name). If, afterwards, I add a
field to the table, the added field is not shown in the view. If I save the
view again, then the field is shown in the view.

Is it possible to create views in this manner that changes in the table
structure are automatically shown in the view? If not, how can I 'update' a
view without saving it again?

If I create a procedure, there seems to be no problem.

Thanks for help.
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Everytime a column is added to a Table any Views dependent on that Table
must be recompiled if the Views are to include that new column. That is
a requirement of MS SQL Server (aka SQL'r).

In versions of SQL'r higher than 6.5, stored procedures (SPs) aren't
compiled until they are run. That is why SPs are able to run after a
table's design has changed.

You could write an SQL script that updates all of a Table's Views & run
it everytime a table's design is changed; which shouldn't be too often
(read: very, very rarely) after the db design has been decided on. So,
the script would read the text of the View from table sysobjects (I
believe), delete the view & re-create it w/ the saved text. Repeat for
other Views.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQWWHfoechKqOuFEgEQJP1gCg6L5+k7bekPMo/etQkwxUizW8LhcAoM0c
zEk4tel8XTUfyXxRmBFl4HLd
=Wa++
-----END PGP SIGNATURE-----
 
thanks for help

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Everytime a column is added to a Table any Views dependent on that Table
must be recompiled if the Views are to include that new column. That is
a requirement of MS SQL Server (aka SQL'r).

In versions of SQL'r higher than 6.5, stored procedures (SPs) aren't
compiled until they are run. That is why SPs are able to run after a
table's design has changed.

You could write an SQL script that updates all of a Table's Views & run
it everytime a table's design is changed; which shouldn't be too often
(read: very, very rarely) after the db design has been decided on. So,
the script would read the text of the View from table sysobjects (I
believe), delete the view & re-create it w/ the saved text. Repeat for
other Views.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQWWHfoechKqOuFEgEQJP1gCg6L5+k7bekPMo/etQkwxUizW8LhcAoM0c
zEk4tel8XTUfyXxRmBFl4HLd
=Wa++
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top