?
Solved

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

Posted on 2007-12-05
4
Medium Priority
?
187 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 1000 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 1000 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 Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

762 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