Solved

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

Posted on 2007-12-05
4
182 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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL query for highest sequence 4 63
SQL Query help 3 44
SQL Syntax 6 32
8 hour Continual coverage based on Time in and Time Out 9 21
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 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