May 29, 2003 at 12:36 am
Hi all
Try this:
create table aaaaa
(aid int,
adesc char(100)
)
create table bbbbb
(bid int,
bid_aid int,
bdesc char(100)
)
insert into aaaaa values (1,'aaaaaaaaaaaaaaaaaa')
insert into aaaaa values (2,'aaaaaaaaaaaaaaaaaa')
insert into aaaaa values (3,'aaaaaaaaaaaaaaaaaa')
insert into aaaaa values (4,'aaaaaaaaaaaaaaaaaa')
insert into aaaaa values (5,'aaaaaaaaaaaaaaaaaa')
insert into bbbbb values (1,1,'aaaaaaaaaaaaaaaaaa')
insert into bbbbb values (2,1,'aaaaaaaaaaaaaaaaaa')
insert into bbbbb values (3,4,'aaaaaaaaaaaaaaaaaa')
create view ccccc
as
select A.*, B.bid
from aaaaa as A left outer join bbbbb as B
on A.aid = B.bid_aid
All fine right? wrong!
Select * from ccccc
1aaaaaaaaaaaaaaaaaa 1
1aaaaaaaaaaaaaaaaaa 2
2aaaaaaaaaaaaaaaaaa NULL
3aaaaaaaaaaaaaaaaaa NULL
4aaaaaaaaaaaaaaaaaa 3
5aaaaaaaaaaaaaaaaaa NULL
All OK? now, alter the table 'aaaaaa' in EM, add the column at the end of the table, call it anything you like, no data...
Try the select from the view again:
1aaaaaaaaaaaaaaaaaa NULL
1aaaaaaaaaaaaaaaaaa NULL
2aaaaaaaaaaaaaaaaaa NULL
3aaaaaaaaaaaaaaaaaa NULL
4aaaaaaaaaaaaaaaaaa NULL
5aaaaaaaaaaaaaaaaaa NULL
Where has my data gone?? columns have now all shifted?!
This also occurs when using:
alter table aaaaa add asdasdasd varchar(12)
Can someone try this and see if its a repeatable problem before I call MS?
Im on SS2k EE SP3
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 29, 2003 at 12:37 am
Hi all
Note that:
dbcc freeproccache
exec sp_recompile ccccc
all didnt work to resolve it, edit the view in EM, add a space, OK.. and all is fine again.
Cheers
Ck
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 29, 2003 at 12:39 am
also...
select A.*, B.bid
from aaaaa as A left outer join bbbbb as B
on A.aid = B.bid_aid
raw query in query analyser works a treat:
1aaaaaaaaaaaaaaaaaa NULL1
1aaaaaaaaaaaaaaaaaa NULL2
2aaaaaaaaaaaaaaaaaa NULLNULL
3aaaaaaaaaaaaaaaaaa NULLNULL
4aaaaaaaaaaaaaaaaaa NULL3
5aaaaaaaaaaaaaaaaaa NULLNULL
but the view is completely bung.
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 29, 2003 at 7:19 am
Hi all
Response from the MS SQL Server group...im still not happy with it [:-D]
> Its a bug, you guys wont admit it! 🙂
No its a not bug, you won't admit it! 🙂
View meta are materialized at the time the view is created. You can see
this with sp_help:
EXEC sp_help 'ccccc'
GO
ALTER TABLE aaaaa
ADD asdasdasd varchar(12)
GO
EXEC sp_help 'ccccc'
GO
If you need to protect yourself from a broken change control process,
you can create views WITH SCHEMABINDING. This will prevent changes to
the referenced objects unless the view is first dropped. Alternatively,
you can run a script to refresh all views after schema changes:
DECLARE @RefreshViewStatement nvarchar(4000)
DECLARE RefreshViewStatements
CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT
'EXEC sp_refreshview N''' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME) +
''''
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'VIEW' AND
OBJECTPROPERTY(
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)),
'IsMsShipped') = 0
OPEN RefreshViewStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM RefreshViewStatements INTO @RefreshViewStatement
IF @@FETCH_STATUS <> 0 BREAK
RAISERROR(@RefreshViewStatement, 0, 1) WITH NOWAIT
EXEC(@RefreshViewStatement)
END
CLOSE RefreshViewStatements
DEALLOCATE RefreshViewStatements
GO
If you'd like to suggest a change for the future versions of SQL Server,
consider sending your rationale to sqlwish@microsoft.com.
--
Hope this helps.
Dan Guzman
SQL Server MVP
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
May 29, 2003 at 5:15 pm
Interesting, to say the least. I'm used to using sp_refreshview, I can see where it would (might?) be expensive to check all the underlying objects each time, but seems like when an object is changed it wouldn't be that hard to see if it was referenced by any views, at least within the same db.
Andy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply