469,924 Members | 1,373 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,924 developers. It's quick & easy.

Views Are Deleted

I am runnnig Microsoft SQL Server 2000 and have a database that gets
downloaded everymorning from an ftp site. This download contains the
new records and any changes to existing records. However I am running
into a problem in that I am forced to recreate the views over everytime
we replace the new database with the old one. I've tried restoring the
database as well as replacing it and am not sure what I am missing. I
am new to SQL Server and any help would be greatly appreciated.

Jul 23 '05 #1
4 995
rockio (ro*****@yahoo.com) writes:
I am runnnig Microsoft SQL Server 2000 and have a database that gets
downloaded everymorning from an ftp site. This download contains the
new records and any changes to existing records. However I am running
into a problem in that I am forced to recreate the views over everytime
we replace the new database with the old one. I've tried restoring the
database as well as replacing it and am not sure what I am missing. I
am new to SQL Server and any help would be greatly appreciated.


I'm not really sure that I understand what's going on here. The normal
procedure would be to get datafiles of new and changed data, and merge
this into the existing database. In such case, your views still be
there.

But it sounds that you actually a get a new complete database this
morning and replace your old one each morning. Then of course the
views are replaced as well.

One possibility would be to create a second database and put the views
in this database, referring to the tables in the main database.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
Wow, I can't believe how fast of a response. Okay now back to the
topic. Erland your second assumption is correct. I only use the
database to run views for reports of the tables. Based on your
response it looks like I have three possibilities

Create a second database - Your idea of having a second database whose
views look at the tables in the updated database sounds like it may
work.

Replacing only the changes as well as new records in the tables

Replacing only the tables and keeping my views in the same database.

Since I am new to SQL and all three will require research which one of
these would be easier to figure out.

My first choice would be to have only one database but I can definitly
work on figuring this out later if it may be difficult for a newbie. I
am making the transition from MS Access to SQL Server 2000

Jul 23 '05 #3
rockio (ro*****@yahoo.com) writes:
Create a second database - Your idea of having a second database whose
views look at the tables in the updated database sounds like it may
work.

Replacing only the changes as well as new records in the tables

Replacing only the tables and keeping my views in the same database.

Since I am new to SQL and all three will require research which one of
these would be easier to figure out.


Definitely the first. The other two would require you to change how
you refresh the database. Replacing the database each morning may not
be the normal way - but it sure is simple!

But there is a fourth way! Put your views in a script. Assuming that
you have some batch file that replaces the database, just augment to
run the script that recreates the view. Or just run the view script
separately.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
Thanks Erland, I used your suggestions of creating a seperate database
to hold my views. Worked like a charm. In case others are just
learning like me and need a quick fix or temporary fix I suggest this
option. In case you are wondering I rewrote my views to include the
full path including database name. For example: dbo.table1 is now
db1.dbo.table1 where db1 is the name of the database that holds the
tables.

Jul 23 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Mike N. | last post: by
3 posts views Thread by KemperR | last post: by
8 posts views Thread by Martijn van Oosterhout | last post: by
7 posts views Thread by solidsna2 | last post: by
33 posts views Thread by Peter | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.