2
Vote

object script order error

description

when I publish a database, it first scripted views, then tables.

comments

Hvasishth wrote Jun 18, 2007 at 8:27 PM

Hi

The view ordering issue you had was a problem in builds of DPW prior to 1.0, but with the latest versions those should be fixed. All objects are ordered using data from sys.sql_dependencies (See http://msdn2.microsoft.com/en-us/library/ms345449.aspx). It is possible that if you created and dropped the tables after you had created the views, then this information could have been lost. For example if you run the following script, then the dependency information between Table1 and View1 would be lost:

CREATE Table Table1 (ID int)GOCREATE VIEW View1 AS Select ID from Table1GODROP Table Table1 -- This line will delete the dependency informationGOCREATE Table Table1 (ID int)GO

Your best bet for your existing database is to drop the views which are being created before the tables and then re-create them. This will reestablish the dependency information.

In the future, you can use the WITH SCHEMABINDING option when you create views so that you will not be able to drop the tables until you clear up dependencies. This should prevent things like this from arising in the future..

Let us know if you run into any more issues.

Himanshu

maxferrario wrote Jan 18, 2008 at 8:56 AM

I have a similar error here.
In my DB I created two views (say A and B), where A is a detailed view on some data and B uses data from A and groups them by date.
But in the output view B is created BEFORE view A, and this obviously triggers an error.
I do not remember if I modified/recreated view A after creating view B, but I doubt it, because view A is actively used in a web application.

I will try to drop and create again view B, and will let you know the results.

Thank you for the precious tools.

ajitrm wrote Jul 17, 2012 at 4:12 AM

Hi Himanshu,

Thanks for your comments on resolving the scripting sequence issue.
My problem is I have to create a utility that will use sqlpubwiz.
My utility can be run on any SQL Server database database (related to my product).
As there are 100s of databases across asiapacific, I'm not sure about views that are created using schema binding or not.

In my case I will not be able to use the work around that you have given.

I would appreciate if the sqlpubwiz provides a patch that will script all tables first then views.

Regards,
Ajit

wrote Feb 14, 2013 at 9:31 PM