Views Are Deleted

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • rockio
    #1

    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.

    Tags: None
    • Erland Sommarskog
      #2
      Re: Views Are Deleted

      rockio (rockio3@yahoo. com) writes:[color=blue]
      > 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.[/color]

      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, esquel@sommarsk og.se

      Books Online for SQL Server SP3 at
      Microsoft SQL Server
      http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
      Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

        Comment

        • rockio
          #3
          Re: Views Are Deleted

          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

            Comment

            • Erland Sommarskog
              #4
              Re: Views Are Deleted

              rockio (rockio3@yahoo. com) writes:[color=blue]
              > 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.[/color]

              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, esquel@sommarsk og.se

              Books Online for SQL Server SP3 at
              Microsoft SQL Server
              http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
              Get the flexibility you need to use integrated solutions, apps, and innovations in technology with your data, wherever it lives—in the cloud, on-premises, or at the edge.

                Comment

                • rockio
                  #5
                  Re: Views Are Deleted

                  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.

                    Comment

                    Previous template Next
                    Working...