how to check if view is valid - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

how to check if view is valid

Author

Topic

rubs_65
Posting Yak Master

144 Posts

Posted - 2006-01-30 : 14:32:28

Hi,

We have a view based on 4 tables and now we get the following error when we drop the column from one of the tables and try to run query on the view:

Server: Msg 207, Level 16, State 3, Procedure vw_bb, Line 2
Invalid column name 'dd1'.
Server: Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'vw_bb' because of binding errors.

Is there a system function that I can run to check if the view is still valid before running the select statement or is that invalid status is stored in any system table?

Thanks
--Harvinder

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-30 : 14:38:03

You using SELECT * in the view?

Tara Kizer
aka tduggan

rubs_65
Posting Yak Master

144 Posts

Posted - 2006-01-30 : 14:53:23

No the actual columns, but in the actual code i am taking X lock on view (that will take lock on all the tables) before dropping and recreating the view. This was done to avoid deadlock issues. I am running the following type of select on view:
select 1 from view with(xlock) where 0=1
and this statement is failing.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-30 : 15:01:15

Have you tried dropping/creating the view?

Tara Kizer
aka tduggan

rubs_65
Posting Yak Master

144 Posts

Posted - 2006-01-30 : 15:03:53

This is the sequence of the steps when table columns are changed:
1) alter the table
2) take lock on view(that will lock all the tables) --This step is done to avoid deadlock as other session may be using the view
3) drop the view
4) create the modified view

And we are getting the specified error at step 2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-01-30 : 15:18:13

I get what you are doing now. I guess you'll need to lock the tables outside of your select 1 query. Then drop/create the view to get the new view definition.

But the real question though is why aren't you making schema changes during a maintenance window?

Tara Kizer
aka tduggan

TimS
Posting Yak Master

198 Posts

Posted - 2006-01-30 : 16:47:39

Might try and see if sp_refreshview @viewname = 'viewname' does what you want.

Tim S

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-31 : 01:03:25

When creating the view use Schema binding. Otherwise you can delete the table which is referenced by view which will cause error if query the view

create view vi
as 
select no, name from dbo.tbl

Go
select * from vi
Go
alter table tbl drop column no
Go
select * from vi -- You will get error
Go
drop view v

Using Schema binding will prevent table from altering


create view vi
	with Schemabinding
	as 
select no,name from dbo.tbl

Go
select * from vi
Go
alter table tbl drop column no - You cant alter until you drop view referencing it
Go
select * from vi
Go
drop view vi

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources