Wortz, Charles
CWortz at tea.state.tx.us
Fri Apr 11 11:23:20 CDT 2003
Arthur, As I understand it, you are correct. Your TSQL is compiled and optimized so if you add a column at the end it is not seen by the compiled code. And if you add a column into the middle, it breaks the code. To be safe, always recompile after making any changes to TSQL statements. Charles Wortz Software Development Division Texas Education Agency 1701 N. Congress Ave Austin, TX 78701-1494 512-463-9493 CWortz at tea.state.tx.us -----Original Message----- From: Arthur Fuller [mailto:artful at rogers.com] Sent: Friday 2003 Apr 11 11:15 To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer]Corrupt views ... (Crossposted at AccessD) Do any of your views use "SELECT *"? I can't prove it but I suspect that SQL does some undercover optimizations on such statements, and when a column is inserted after the fact there are occasional problems. I have mixed results on this, which is why I say I can't prove it, but I never use "SELECT *" anymore; I always click every single column that I need and go from there, and this seems to make the problems go away. A. -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Tom Adams Sent: April 11, 2003 9: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