March 1, 2012 at 5:44 am
Alexander G. (2/29/2012)
Eugene Elutin (2/29/2012)
Does your job have a step to recreate a table or make any DDL change?The job does not execute any ddls at all.
Can you confirm that after executing sp_refreshview you can insert a new row into your table and that row will not be visible in a view until you execute sp_refreshview again?
From the first execution of sp_refreshview the view seems to behave correctly. So you do not need to execute it again, all inserts are transparent. Of course this solves my problem for the moment but it does not remove my doubts...
Some more diagnosis:
- Not only does select * or select count(*) return wrong values from the view. Even if I execute procs that query this view the results are wrong.
- When closing ssms (running on the server) and restarting it and using the same (windows) login the view result is still wrong.
- When backing up the DB with the view and restoring it in a db_bak, select count(*) from db_bak.stage.RATING returns the
correct values.
- When querying the view and the table from a remote client (instead of before where ssms was executed on the server) or querying with ssms on the server but with a different login, the queries return the correct values.
It seems to be some user/connection specific caching issue...
Updates the metadata for the specified non-schema-bound view.
Quote straight out of BOL. I doubt sp_RefreshView resolves this for you. This procedure only refreshes metadata and not data.
You can't selectively explicitly grant permissions to the amount of data you can retrieve from a view either.
A view that retrieves data from a table with no filters should always return all the data in that table
unless a filter is specifically applied. New rows should come straight through.
Is there a chance that might be working with different database objects in different schemas maybe?
If you do solve this one, do share and let us know.
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply