Incorrectly creating view order
BasharLulu
Posts: 9
I'm working on a legacy system, and for some reason, the DBAs then decided to create a large number of views. So many, that they outnumber the 400 or so tables.
Now, because some views depend on other views, as well as other tables, when trying to create an SQL package for the database, I get errors like 'Invalid object name <either view or table name>'.
This only implies some sort of dependency trouble. What should I do? Is there some sort of packager setting or registry setting I should configure?
Thanks in advance.
Now, because some views depend on other views, as well as other tables, when trying to create an SQL package for the database, I get errors like 'Invalid object name <either view or table name>'.
This only implies some sort of dependency trouble. What should I do? Is there some sort of packager setting or registry setting I should configure?
Thanks in advance.
Comments
There is a setting in Packager's schema options to include the dependent objects, thereby creating the scripts in the correct order. The problem in your case, though, could be views that select from other views, especially if any of them do not specify the column names explicitly and use SELECT * instead.
When the view uses SELECT *, then the compiled version of the view needs to be updated by SQL Server. There is a function in SQL Server called sp_refreshview, which when run after an update to the view, will recompile it internally.
Version 4 of Packager automatically scripts sp_refreshview wherever it can, virtually eliminating the problem. If you feel this is the issue, an upgrade will solve it.
Thank you for the prompt response. I'll attempt to follow through on your recommendations and inform you of the outcome.
Thank you again.