Surprising problem with views SQL Server 2000
-
Hi
I've included this in both the Access Forum and SQL forum as I'm not sure where it belongs. I have an Access 2003 .adp which has problems caused by views:
Something has happened several times which has surprised me greatly. I have many views with fields from several joined tables. If I add say two fields to a table showing all fields (*) in a view, the results are:
a) The two new fields do not automatically show in the view
b) All the data is moved along by two columns.
The only way I can correct this is to copy the sql to a new view. This immediately rectifies the situation.
It appears that I need a way to automatially refresh the view??
Can anybody suggest what I should do?
Thanks
Paul
-
after you modify a view you need to run sp_refreshview
* Noel -
This corrects the problem server side. Access also caches metadata when it opens. So another part of the solution would be to go to the database window of access and hit refresh so that the updated metadata gets updated.
-
Thanks Both
This is really helpful and provides the answer to the problem.
However, I am continually changing tables at present to accommodate new circumstances. It would be really useful to be able to refresh all views together - if this is possible. I could then run the refresh as a timed local package.
If I should add a field to a table and omit just one view, it could create a lot of problems. So, if anybody can suggest a way I might accomplish this, I would be very grateful.
Paul
-
I think a simple solution would be to clear the cached plan altogether. That would ensure that all plans be dropped and recreated.
check out DBCC FREEPROCCACHE.
-
There is an undocumented stored procedure called sp_MSForEachDB (there is also one for tables called sp_MSForEachTable). That procedure lets you run a script against ALL databases (or tables).
The syntax has been posted on this site before, so do a search for it. Or take a look at the stored procedure in Enterprise Manager and work out for yourself how it functions. If you have Ken Henderson's 'Guru's Guide to Transact SQL', it's in there.
-SQLBill
-
you can use sp_foreachView of this site and summit the sp_refreshview as the command. Then schedule it as a job.
Let me remind you that what you are doing is not a good practice but I understand that sometimes is a fact of live. You should be changing stuff that ad hoc only on development not on production.
* Noel -
...and deploy and night time when nobody's on the server.
-
Thanks again for your replies.
Before I try to pick my way through the sp_foreachView , can I ask if I should avoid using select * in views and sps? The concept of static views and sps is new to me, but aside from this, is there a performance issue involved also?
Paul
-
I recommend not to use select * in views nor in stored procedures
Cons of using select *:
You don't know which columns actually will be queried.
The lesser columns the faster. (less data to read from disk, possible use of indexes)
Possible problems with unions if a new column is added.
You can use the objectbrowser of query analyzer to quickly script the column names.
-
Many thanks to everybody.
I think prudence mitigates towards avoiding the use of select *. From now on I'll use columns only in views and sps.
Thanks once again
Paul
-
Good choice!
The only place where (imo) select * is 'allowed' is in conjuntion with EXISTS / NOT EXISTS clauses. eg WHERE EXISTS ( select * from foo )
'select *' in any other place is bad habit and/or plain laziness.
/Kenneth
-
Thanks Kenneth
I much appreciate your input and will adhere to this.
Paul
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply