Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-10-13 : 07:20:45
|
| Steve writes "I am using synonyms to alias our 3rd party data cache.The cache is recreated once/twice a day and loaded into new tables.When the cache processing is complete, a synonym is altered to redirect cache queries the new tables.This works fine, but we have discovered that any views we have that use the cache synonyms, do not return data from the new cache, but still return data from the old cache.Even when I recompile the view using sp_recompile, the view does not return data from the new cache!!Simply test:create synonym xxxx for <table1>;create view vvvv as select * from xxxx;select top(20) * from vvvv;drop synonym xxxx;create synonym xxxx for <table2>;select top(20) * from vvvv;-- Data returned is from <table1>sp_recompile 'vvvv';select top(20) * from vvvv;-- Data returned is from <table1>, againThe only method of rebuilding the view to return data from the new cache follows:declare @source varchar(max);select @source = text from syscomments where id = <view id>;set @source = replace(@source, 'CREATE', 'ALTER');exec(@source);select top(20) * from vvvv;-- Data returned is from <table2> :)What's going on ???Thanking you for your thoughts, in advance.Steve Cooper" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2006-10-13 : 09:13:31
|
| "If a view is not created with the SCHEMABINDING clause, sp_refreshview should be run when changes are made to the objects underlying the view that affect the definition of the view. Otherwise, the view might produce unexpected results when it is queried." -Book OnlineJay White |
 |
|
|
stephen.cooper100
Starting Member
1 Post |
Posted - 2006-10-13 : 10:14:09
|
| Thanks Jay.btw SCHEMABINDING cannot be used if you are using SYNONYMs.Have fun!Steve |
|
|
|
|
|
|
Using synonyms in views