April 30, 2007 at 10:03 pm
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
May 1, 2007 at 7:18 pm
Anyone?
This is my biggest problem moving from 2000 to 2005.
I REALLY need to be able to edit data from the results pane in views. I did it all the time in Enterprise Manager.
cheers
May 3, 2007 at 11:37 am
Use Access. It's not the greatest solution in the world, but it does work - IF the table/view does allow you write access. Which brings up the larger issue of updating a table through a view. I think that the rules have changed in SQL 2005. 🙁
Steve G.
May 4, 2007 at 11:01 am
I just confirmed that it is in fact possible to modify the data in views using EM (Open View from the context menu). It is even possible to modify data from multiple tables in the view, though not at the same time.
This is from BOL for SQL 2005:
"You can modify an underlying base table's data through a view, in the same manner as you modify data in a table, through UPDATE, INSERT and DELETE statements, or by using the bcp utility and BULK INSERT statement. However, the following restrictions apply to updating views, but do not apply to tables:
Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. (etc... for other restrictions...)"
July 21, 2008 at 9:50 pm
How sad, I used this feature of 2000 quite heavily. Has anyone found a fast workaround?
July 21, 2008 at 11:11 pm
Well, I'm the OP and I still haven't found a suitable answer 18 months later. Surely this a really common question? I find it hard to believe that it can't be done.
I've worked out a whole heap of cumbersome workarounds, but something that used to take me say, 10 seconds to edit now takes me a few minutes. It's just a hassle.
Anyone?
July 21, 2008 at 11:24 pm
Hi
i was able to update the view from SSMS. Anything specila on the columns that you are trying to update
ex:- identity columns..
"Keep Trying"
July 22, 2008 at 12:23 am
Where in SSMS can you go to edit cells that are a result of multiple tables?
For example, if I open SSMS and right-click a table called Player then select Open Table I get:
SELECT *
FROM Player
And all the cells are editable. Even if I change the SQL to the following, I can still edit all the cells that are returned:
SELECT Player.FirstName, Player.LastName
FROM Player
WHERE Player.LastName = 'Graham'
But once I toss in some joins everything grays out and the status bar says "Cell is Read Only.":
SELECT Player.FirstName, Player.LastName, Team.Name
FROM Player INNER JOIN
PlayerTeam ON Player.Id = PlayerTeam.PlayerId INNER JOIN
Team ON PlayerTeam.TeamId = Team.Id
WHERE Player.LastName = 'Graham' AND Team.Name LIKE 'X%'
This is rather annoying since it was possible in SQL 2000 Enterprise Manager. Maybe I'm just going about it the wrong way 🙂 But in the above example, usually I'll end up running my SELECT query with all the joins, then copy-pasting the PK for all the rows I want to edit into a new SELECT that only queries the single table that I am editing. That approach can get complicated really fast.
Out of curiosity I also tried the same set of queries in SQL Express in Visual Studio 2005 and got the same results (darn...)
July 23, 2008 at 7:18 am
I'm at a loss to understand why you would be editing data using EM in the first place. Not because I'd be concerned about the dangers, but because I can't see a particularly good reason to use that particular tool to do the job. Why are there so many updates being done in a manual fashion in the first place? I'm thinking about questions like "What's the business process behind the need to make edits so often that one would search for a replacement tool for 18 months?"
Somehow, I just get the feeling that something bigger is at play here, that might be better dealt with by creating a fairly decent MS Access database application as a front-end (already suggested by another poster). After 18 months, I could have easily had one that dealt with each and every view I'd ever created, even if that were several hundred.
Anyone else wondering what's behind the scenes?
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2008 at 1:54 am
There's nothing "at play".
I just like editing data directly in a multi-table view. It is fast, easy, safe (if you're careful) and convenient.
Usually it is to quickly make changes to source data which then will be viewable in the app which uses that database.
It was oh so easy in 2000 Enterprise Manager & 2005 views have just screwed up that ease.
July 24, 2008 at 6:37 am
My concern was the very concept that a DBA type of person would need to edit data so often. It raises the concern that there are few, if any, controls over data entry, nor much in the way of "business process" to ensure accuracy. I've seen a lot of IT shops in my day, and none of them have ever had any IT folks doing data correction.
Should the business be continuing to absorb the cost of you doing that editing is a question that most businesses in such a position SHOULD be asking, and your value to your employer would, from my perspective, be enhanced if you were to provide them not only a process, but a means to achieve it. Just my two cents...
Steve
(aka smunson)
:):):)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
July 24, 2008 at 8:54 am
I doubt we are alone here, the great thing about forums is that there is always some odd, esoteric reason something that is different is acceptable. When programming, I edit data for ad-hoc, one-time updates in a test environment for debugging purposes. For example...
Trying out a new list view in an ASP.NET page, but I'm getting a data casting exception in the code behind.
So I create a SELECT query spanning a dozen tables showing the data that is causing an issue.
I notice one of the rows has errant data noticing that one of the cells is using an old enum value.
I click on the cell, edit the data.
Trying the view in ASP.NET again, it works, yay!
Back to important things.
With 2005, there are two extra steps:
Create a SELECT query spanning a dozen tables showing the data that is causing an issue.
I notice one of the rows has errant data noticing that one of the cells is using an old enum value.
Copy the uniqueidentifier pk for the row.
Create a new SELECT statement singling out that row.
Click on the cell, edit the data.
Trying the view in ASP.NET again, it works, yay!
Back to important things.
Why? I don't know why they'd get rid of part of a feature. You can still edit data in SSMS, so if they were trying to discourage poor behavior they should have removed editing data all together and forced people to write INSERT and UPDATE statements, preferably in transactions.
Here's to hoping that SQL 2008 allows edits with JOINs.
July 24, 2008 at 11:26 am
dgarcia182 (7/24/2008)
So I create a SELECT query spanning a dozen tables showing the data that is causing an issue.I notice one of the rows has errant data noticing that one of the cells is using an old enum value.
I click on the cell, edit the data.
Trying the view in ASP.NET again, it works, yay!
Back to important things.
If your database design has proper constraints and your application or ETL process is coded properly, you don't get errant data, and there would be no need to edit any data like that. Rather than fixing the data on one-offs, the source of the problem should be fixed so things like this don't reoccur. I think you're in the very small minority of companies that make data changes like you're doing.
July 24, 2008 at 11:59 am
Right... I don't use proper ETL practices for my test environment when debugging. Once I find what's wrong I can write up an ETL to fix the problem on production the next time there is a scheduled maintenance window.
That is far off topic, I think people are missing the point here. Why would the ability to alter data be removed? It can't be because it is a bad practice, you are still alter data but not with joins.
July 24, 2008 at 12:29 pm
What we're trying to get at is that this "need" to edit data on a "regular" basis shouldn't exist, even in development. If you are designing the databases properly, any editing you might have to do should be so near to zero that any extra cost in the editing process would be so negligible as to be not worthy of mention. If you're getting to the point where you actually have data in tables in such poor form that it needs regular editing, then there's something wrong in your development process. Even initial bulk loads of data, should be incapable of bringing problem data into a database. Data messes should be cleaned up outside of the entry point to the server, not after it gets there. This allows for more practical editing tools such as Excel or Access. Also, data cleanup is not generally a good use of a DBA's time. A DBA should more properly just identify the data records that won't properly import and pass those data back to data entry for editing. DBA's are expensive resources that shouldn't be playing the cleanup game AFTER data is in an SQL database. If you can't get away from the cleanup task, at least do it in the source data (e.g. Access or Excel).
What Microsoft is quite rightly saying (even if it's not their actual intent to say it) is that editing data AFTER it's in your database is the WRONG TIME to be messing around with it - especially when you could just as easily have all manner of referential integrity in place, and data editing with a join could have some seriously damaging effects that would just happen without concern for the editor's intent. SQL Server doesn't know the difference between production data and test or development data, so yes, it's a BAD IDEA. Always has been, always will be. The risk of damage is just too high to be worthy of consideration. In other words, you're supposed to know better.
Steve
(aka smunson)
:):):)
dgarcia182 (7/24/2008)
Right... I don't use proper ETL practices for my test environment when debugging. Once I find what's wrong I can write up an ETL to fix the problem on production the next time there is a scheduled maintenance window.That is far off topic, I think people are missing the point here. Why would the ability to alter data be removed? It can't be because it is a bad practice, you are still alter data but not with joins.
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply