March 16, 2021 at 8:46 pm
I have schema bound a view to a temporal (system-versioned) table and receive no errors in doing do. However, I am able to delete/modify columns from the temporal table, without issue. When I do a query against the view, it fails with a binding error. Has anyone else tried this? Should it work?
BTW...on the same database, if the table is not temporal (system-versioned) and I try to delete the column, I get the expected error from SQL.
AtDhVaAnNkCsE for any assistance.
March 17, 2021 at 9:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
March 26, 2021 at 9:51 am
Hello Dandschul
Sorry, can not reproduce your problem. SQL used follows. Error message produced, as expected:
Msg 5074, Level 16, State 1, Line 29
The object 'my_view' is dependent on column 'my_column_1'.
Msg 4922, Level 16, State 9, Line 29
ALTER TABLE DROP COLUMN my_column_1 failed because one or more objects access this column.
Can you give a simple example that illustrates your problem? Does SQL below fail in your environment? Thanks. Bredon
SQL for test:
create table dbo.my_table
(
my_id integer identity(1,1) not null primary key,
my_column_1 varchar(12) not null,
my_column_2 decimal(10,3) not null,
valid_from datetime2 generated always as row start,
valid_to datetime2 generated always as row end,
period for system_time (valid_from, valid_to)
) with (system_versioning = on (history_table = dbo.my_table_history));
go
insert into dbo.my_table(my_column_1,my_column_2)
values ('abc',9.1),('def',10.1),('ghi',11.1);
go
create view dbo.my_view with schemabinding as
select my_id,my_column_1,my_column_2 from dbo.my_table;
go
select * from dbo.my_view;
go
alter table dbo.my_table drop column my_column_1;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply