View in 2005 says cell is read only
-
Hi,
As a certified DBA in 2000 I've done hundreds of views in Enterprise
Manager. I've just started to get my teeth into 2005.
As one of my first forays, I thought I'd whip up a typical view. No
problems, but when I execute, the results pane say 'cell is read only'.
I'm used to directly editing within the results pane in Enterprise Manager
(yeah, I know...I'm careful) but in SSMS the view doesn't allow me to edit
data. This is essential for the kind of stuff I do. What gives? This
particular view is just three tables joined, plain old select statement with
a column or two from each table. No criteria.
This question was originally posted on the ms news server but so far have not had any decent response after a few months. Surely this is a common question? Basically I'm just after the same editing functionality in Management Studio which I had in EM. Note, I want to edit a VIEW from the GUI, in the results pane, not edit a table, nor edit the results from a query window.
cheers
(This was also posted in another forum on this site - just wanted to cover my bases).
-
your view should surface all the relevant PK's so the view can hit the appropriate base table.
but you might be interested in this experiment [same for TSQL and in SSMS UI]
create
table T1
( IDA int not null primary key
, descrip1 varchar(99)
)
create
table T2
( IDB int identity(1,1) not null primary key
, descrip2 varchar(99)
)
create
table T3
( IDA int not null foreign key references T1(IDA)
, IDB int not null foreign key references T2(IDB)
, descrip3 varchar(99)
)
go
create
view v1 as
select T1.*, T2.*, T3.descrip3
from T1
join T3 on T3.IDA=T1.IDA
join T2 on T2.IDB=T3.IDB
go
insert
into T1 values (1, 'this is NOT identity table')
insert into T2(descrip2) values ('this IS not identity table')
insert into T3 values(1,scope_identity(), 'some join table')
go
-- #1 fails Msg 4405 ... is not updatable because .. affects multiple base tables
update V1 set
descrip1='XXX'
, descrip2='YYY'
, descrip3='ZZZ'
where IDA=1
and IDB=(select min(IDB) from T2)
go
-- #2 but individually it all works OK
update V1 set
descrip1='XXX'
where IDA=1
and IDB=(select min(IDB) from T2)
go
update
V1 set
descrip2='YYY'
where IDA=1
and IDB=(select min(IDB) from T2)
go
update
V1 set
descrip3='ZZZ'
where IDA=1
and IDB=(select min(IDB) from T2)
go
select
* from V1
-
Thanks Dick,
Although you say that you could update individually, that's not really what I'm asking.
If I create a view, I need to be able to edit the results, not through query code, but through the results pane in the GUI.
Anyone? This is my biggest problem so far moving from 2000 to 2005.
cheers
-
Dick wrote
your view should surface all the relevant PK's so the view can hit the appropriate base table.
but you might be interested in this experiment [same for TSQL and in SSMS UI]
I think Dick got the point. Run the code and try afterwood in SSMS GUI to edit the results....
-
I shouldn't need the IDB=(select min(IDB) from T2) clause
Like in EM, I should be able to graphically link the tables as appropriate, run the view. A few records will display in the results pane which should be editable.
Using the example from Dick, my view would read
select T1.*, T2.*, T3.descrip3
from T1
join T3 on T3.IDA=T1.IDA
join T2 on T2.IDB=T3.IDB
I just run it, and edit any old field I need. That's the functionality I need in SSMS which Microsoft in their wisdom have destroyed. Surely this is common? Am I the only one out there who edits data like this?
cheers
-
Editing data directly to the table using the SSMS has always been frowned upon from most of the DBAs I know. Being able to control the transaction commit or rollback is the biggest advantage in using TSQL instead.
-
Sure direct editing may be frowned upon. Side issue.
Still looking for a solution!
-
While in the view designer, open the properties window and change the update specification to yes. Once you save your view, you should be able to open it and directly edit it.
For further info regarding the entire process to do this go to http://msdn2.microsoft.com/en-us/library/f5scy1hs(VS.71).aspx or search for updateable view in SQL BOL
-
Hi,
I thought this might've been the solution! But alas, changes to the view designer's properties didn't help.
And the link refers to VisualFoxPro, not SSMS.
-
I had success in the sample I created joining three tables from the Adventure Works DB. Employee, EmployeeAddress, and EmployeePayRate and successfully updated the data in the view.
BTW - I also caught that reference to Visual Fox Pro in the top left corner as well, interesting isn't it?
Good luck in your efforts.
-
Views have to meet some specific criteria to be updatable. For a quick test, try updating a record wih a SQL statement rather than using the SSMS grid. If you cannot, look in the books online about updatable views and make sure your view meets all of the criteria.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply