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 |
|
Lumbago
Norsk Yak Master
3271 Posts |
 Posted - 2006-08-16 : 04:15:00
|
| Hi all,I just encountered a weird problem in one of our databases regarding a view that is set up to run against a linked server. The view is very simple:SELECT * FROM servername.database.dbo.tablenameWe just added two columns in the table on the linked server, but when reviewing the columns in the view in SSMS the two columns are not there. Doing a SELECT * FROM viewname doesn't show the two columns either...I'm aware that using * in selects is bad practice but it should still work shouldn't it? After a bit of investigation I found that recompiling the view solved the problem but I still find it a bit weird, especially after reading this in BOL: "When querying through a view, the Database Engine checks to make sure that all the database objects referenced anywhere in the statement exist and that they are valid in the context of the statement, and that data modification statements do not violate any data integrity rules" Shouldn't this work for the SELECT * also?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2006-08-16 : 09:25:02
|
| After adding column to underlying table, you should fire sp_refreshview 'viewname' to update view definition even though it is 'select *'.Harsh AthalyeIndia."Nothing is Impossible" |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-08-17 : 02:18:13
|
Hm, never heared about that sp but I'll give it a go...thanx  --Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
Weird view issue on linked server