|
|
Posted by Peter on 10/02/70 11:45
Erland,
Thanks, I tested it and indeed I did not notice a difference in using
sp_recompile or not.
And I see also the difference in naming the columns instead of using the
asterix. It seems to me the binding is different. With the asterix, the
binding is by column number instead of by name.
mmmm
"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns97A8690D07743Yazorman@127.0.0.1...
> Peter (someone@someplace.com) writes:
>>> CREATE TABLE dbo.t1 (x INT NOT NULL PRIMARY KEY, z1 INT NULL);
>>> GO
>>> CREATE VIEW dbo.v1 AS
>>> SELECT * FROM dbo.t1
>>> GO
>>> ALTER TABLE dbo.t1 DROP COLUMN z1 ;
>>> ALTER TABLE dbo.t1 ADD z2 INT ;
>>> GO
>>> EXEC dbo.sp_recompile 'dbo.v1' ;
>>> /* Notice that the second column still exists as Z1 in the view */
>>> SELECT x,z1 FROM dbo.v1 ;
>>>
>>
>> O that is bad!!! I gonne test that immidiatly tuesday. Is it different
>> when naming the columns?
>
> Yes, then you get an error when you try to access the view.
>
> Then again, what David's example really shows is that you should use
> sp_refreshview when you've changed the underlying table, not sp_recompile.
> To wit, with sp_refreshview, v1 picks up the changed in columns.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|