Solved

Glitch in moving SQL Server "Views" to new database.

Posted on 2007-12-05
4
179 Views
Last Modified: 2008-09-20
I generated a script in SQL2000EM to "move" about 1000 views to a new server. OK with that.

Problem:  Many (over 100) views fail to create due to the fact that some VIEWS are based on other VIEWS that have not yet been generated in the script. (for example: CREATE VIEW VIEW1 as SELECT * from VIEW2....VIEW2 does not (yet) exist).
I heard from someone to try using DMO to move the view, however not familiar with that.

Thanks
0
Comment
Question by:Louis Capece
4 Comments
 
LVL 8

Accepted Solution

by:
i2mental earned 250 total points
ID: 20411860
Is this a one time move? If so, I think the easiest thing you could do would be to just run the script more than once. You'll get errors on the objects that already exist, but the ones that weren't created the first time will now have their dependencies and be created.
0
 
LVL 4

Assisted Solution

by:kenshaw
kenshaw earned 250 total points
ID: 20552389
that will work if you don't generate the DROP statements as well!  (uncheck "Generate the DROP command for each object").  By the way, it's surprising that your generated script does not just run without problems; if you have a lot of "nested" views (I mean views calling views), it could be quite tedious to keep running the same script until there are no more errors.  Perhaps you should try regenerating the script again from scratch and rerunning it over your target db
0

Featured Post

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now