SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 Bit Bad
 New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

kselvia
Aged Yak Warrior

526 Posts

Posted - 06/08/2004 :  11:54:11  Show Profile  Reply with Quote
I don't have a dog in this fight, but I have to mention that even if you had created the view as

Create view v_foo as Select id, value1, value2 from foo

then

select value3 from v_foo

would still not work. I'm just saying SELECT * did't break anything, and selecting columns didn't fix anything.


Edited by - kselvia on 06/08/2004 11:56:11
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2004 :  11:55:58  Show Profile  Reply with Quote
Nice test....another reason for the list....

http://weblogs.sqlteam.com/brettk/archive/2004/04/22/1272.aspx



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
35937 Posts

Posted - 06/08/2004 :  12:03:26  Show Profile  Visit tkizer's Homepage  Reply with Quote
Kselvia, selecting columns would fix it though. Yes of course select value3 from v_foo would throw an error, but if you add a column to a table and also need it in the view, then you need to add it to the view as well. The view would still function as coded without the addition though. select * though wouldn't as Sam has discovered.

Tara
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2004 :  12:05:15  Show Profile  Reply with Quote
Want to bet?

This freaked me out...

Check out the select from the view AFTER THE ALTER DROP Column...

huh?


USE Northwind

create table foo ([id] int identity, value1 varchar(20), value2 char(10));
GO

insert into foo values ('foo', 'bar');
insert into foo values ('bar', 'bar');
insert into foo values ('foo', 'foo');
GO

Create view v_foo as Select * from foo
go

select * from v_foo
GO

SELECT * INTO tmp_Foo FROM Foo

DROP TABLE Foo
GO


create table foo ([id] int identity, value1 varchar(20), value2 char(10), value3 varchar(30));
GO

SET IDENTITY_INSERT foo ON
 
INSERT INTO foo([id], value1, value2)
SELECT [id], value1, value2 FROM tmp_foo

SET IDENTITY_INSERT foo OFF
GO

insert into foo values (NULL, NULL, 'foobar');

select * from v_foo

select value3 from v_foo
GO

ALTER TABLE foo DROP Column value2

select * from v_foo
select * from foo
GO


DROP VIEW v_foo
DROP TABLE foo
GO



It's an undocumented feature!



Brett

8-)
Go to Top of Page

kselvia
Aged Yak Warrior

526 Posts

Posted - 06/08/2004 :  12:13:10  Show Profile  Reply with Quote
Oh, I get it now. <light bulb switching on>
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 06/08/2004 :  14:49:20  Show Profile  Reply with Quote
Brett,

Nice demo. I did have a SELECT * in my view after all (nuts). I suppose not using * is the fix.

Sam
Go to Top of Page

SamC
White Water Yakist

USA
3459 Posts

Posted - 06/08/2004 :  14:50:25  Show Profile  Reply with Quote
Brett,

The URL in your BLOG points to a reply page to this thread !!

Sam
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 06/08/2004 :  14:54:16  Show Profile  Reply with Quote
Thanks...changed it....

I dropped the table then ran the select against the view...finally it caught soemthing....



Brett

8-)
Go to Top of Page

Lakonikos
Starting Member

1 Posts

Posted - 07/21/2004 :  10:50:28  Show Profile  Reply with Quote
Just found this thread -- glad to know that others are experiencing the same problem. Here is a solution that I implemented:

1) Create a procedure with the following snippet.
2) Schedule a job to run nightly and exec the proc:


CREATE PROC prc_refresh_view
AS
--=========================================================
--Recompiles the Meta Data of ALL user-created views in the current database
--=========================================================
DECLARE @view varchar(100)
DECLARE cur CURSOR FOR
SELECT name from sysobjects WHERE xtype = 'V' AND category = 0

OPEN cur
FETCH NEXT FROM cur INTO @view
-------------------------------
WHILE @@FETCH_STATUS = 0


BEGIN
EXEC sp_refreshview @view
FETCH NEXT FROM cur INTO @view
END
-------------------------------
CLOSE cur
DEALLOCATE cur
--=========================================================

Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 07/21/2004 :  13:52:49  Show Profile  Reply with Quote
Good idea ... wish I could have a trigger on sysobjects<g>

Kristen
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000