[dba-SQLServer]Corrupt views ... (Crossposted at AccessD)

I've had problems when I have a view dependent on another view and the
dependent view changes.  This then corrupts the first view.  I've fixed it
by dropping and recreating the first view.  

Otherwise I (we) have noticed no problems with views in SQL 7

Cheers,
Mike Byrd

> -----Original Message-----
> From: Tom Adams [mailto:tomadatn at bellsouth.net]
> Sent: Friday, April 11, 2003 8:01 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer]Corrupt views ... (Crossposted at AccessD)
> 
> 
> I've posted a couple of items asking if anyone has had any 
> problems with Sql 7 and corrupt views.
> 
> So far no one else seems to have found this bug/feature.  
> Plowing ahead with this first in the universe coverage (you 
> heard it here
> first folks!), I thought I'd bring you up to date.
> 
> At first I thought it might be caused by Access 2000 ADP.  I 
> used both that and the Enterprise manager to make changes in views.  I
> then decided to eliminate the Access question by never 
> changing a view with Access, just using it to review data 
> (Being able to sort
> is absolutely essential in my opinion.  Although the view can 
> not be sorted, you can move columns around and then wipe as many as
> you wish and hit the A-Z button.  Wonderful really.).
> 
> I can now report that I am still hitting corrupt views.  
> However they're a little different now.  Now I open them 
> (it's mainly one
> complicated view now) in Enterprise Manager and the Sql view 
> looks corrupt.  It cuts off before the From and following parts.
> However it runs ok (Previously the view looked ok but showed 
> bad data.  Any change at that point would correct it - eg. take out a
> field and put it back in.)
> 
> Note that I can still generate Sql scripts from the database 
> window and the scripts are just fine.
> 
> Now when I delete the Sql statements that show in the EM, and 
> paste the old complete code back in, it looks ok in design, and will
> run ok with the !(Bang).  But when I save it and reopen in 
> Design it looks corrupt again.
> 
> I will test deleting the View, closing the EM, the reopening 
> and recreating with Sql Server Query Analyzer.  One poster recommended
> that.
> 
> Sql Server is running on an NT 4 server with the latest NT 
> service pack installed.  It jammed up about a week ago and the
> Administrator brought the server all the way down and back up 
> then did some Admin clean up stuff.  I'm working on a database
> maintained by Epicor (formerly platinum) with about 1,000 tables.
> 
> Note:  I usually combine all the fields and apply universal 
> criteria in a "Base" query, then use it in other specilized queries.
> When the Base query corrupted (Sql looked good, data looked 
> like poo-poo, I found I had to revise the other queries using 
> it to fix
> the problem all down the line.  Now that the corruption has 
> changed (data looks good, Sql looks like poo-poo) I find I 
> don't have to
> fix the downline views.
> 
> Note2:  A few tables have too many indexes to link to from an 
> Access Mdb.  I found that I can make a view, showing all fields with
> the table name & "_vw" and get by the index problem.
> 
> Note3:  I've really gotten to like the ADP.  It's much more 
> flexible, faster and easier to use for a variety of development tasks.
> However I also use an Mdb from time to time.  Eg.  I created 
> a view and wanted to show the records where field 1 didn't equal field
> 2.  I couldn't seem to do it in a view directly so I just 
> exported the view to excel, imported the excel file into an 
> Mdb, and added
> the criteria and had the answer in about a minute.  I might 
> learn how to do that later in an Adp but got the job done quickly by
> using an Mdb.
> 
> 
> If anyone has any comments or experience with this I'd 
> appreciate hearing from them.
> 
> Tom (Viva la Access!!! Long live Jet!!!!) Adams
> 
> 
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>