Solved

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

Posted on 2007-12-05
4
185 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

707 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