Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sth_Weird
Starting Member
38 Posts |
 Posted - 2010-04-26 : 10:19:02
|
| Hi,I am using SQL Server Management Studio to access a SQLServer 2000 database.When I try to manually edit columns in my view, an error pops up saying that my view was not editable because the select statement contains the TOP clause.But when I manually delete the TOP clause and try to save the query, it automatically adds the TOP clause.I googled and found out that you can stop this behaviour in the properties. So I set it to false.But unfortunately, setting it to false also deletes my ORDER BY clause :-( As soon as I add an ORDER BY clause, the TOP clause is added again as well. but I really can't edit the table like that, it contains hundreds of rows in no particular order, I need it sorted! :-/???Can anybody tell me a way to edit my view without removing the ORDER BY?Thanx a lot in advance!sth_Weird |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-26 : 12:34:01
|
| Don't use an order by in the view, use it in the query that queries the view. Order by should not be included in a view unless there is a TOP that limits the number of rows (not top 100%). SQL 2000 did honour order by in views, SQL 2005 and above do notbtw, the view designer (and gui data editor) tend to be a little buggy at times. Learn how to use UPDATE statements and ALTER statements.--Gail ShawSQL Server MVP |
 |
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2010-04-27 : 02:33:09
|
| Thanx for your reply!The database I am trying to query is an SQLServer 2000 DB. But until recently I used Enterprise Manager to query it. Since I have to deal with both SQLServer 2000 and SQLServer 2005 I recently switched to Management Studio, and since then I am having troubles with queries that I'm sure worked quite well in Enterprise Manager.Querying the query as you suggested lead to the same error :-(Not using the ORDER BY causes another error saying I'm trying to update multible rows, and no matter which row I choose it is always "4 rows" (which is definitly rubbish, I am only using 1 row at a time). Is that what you mean when you say "buggy"?I wish I still had Enterprise Manager, I never had to deal with query problems there (if I did it my query was indeed wrong). It seems the only way to edit any query is when it only contains one table :-/.I know how to manually write SQL SELECT/UPDATE/DELETE queries but when I do it seems a bit like writing html pages or programming software using notepad.Anyway thanx. I guess I'll edit the query using my C# programme (which uses the same queries but works fine...it just takes longer to edit the data that's why I wanted to edit the query result directly in the database).sth_Weird |
|
|
|
sth_Weird
Starting Member
38 Posts |
Posted - 2010-04-27 : 02:38:22
|
PLEASE DELETE THIS POST, I WAS TRYIN TO EDIT THE LAST ONE AND ACCIDENTY HIT THE "Reply with quote" BUTTON INSTEAD...SORRY!quote:
Originally posted by sth_Weird
Thanx for your reply!The database I am trying to query is an SQLServer 2000 DB. But until recently I used Enterprise Manager to query it. Since I have to deal with both SQLServer 2000 and SQLServer 2005 I recently switched to Management Studio, and since then I am having troubles with queries that I'm sure worked quite well in Enterprise Manager.Querying the query as you suggested lead to the same error :-(Not using the ORDER BY causes another error saying I'm trying to update multible rows, and no matter which row I choose it is always "4 rows" (which is definitly rubbish, I know for sure I'm only editing 1 row at a time). Is that what you mean when you say "buggy"?I wish I still had Enterprise Manager, I never had to deal with query problems there (if I did it my query was indeed wrong). It seems the only way to edit any query is when it only contains one table :-/.I know how to manually write SQL SELECT/UPDATE/DELETE queries but when I do it seems a bit like writing html pages or programming software using notepad.Anyway thanx. I guess I'll edit the query using my C# programme (which uses the same queries but works fine...it just takes longer to edit the data that's why I wanted to edit the query result directly in the database).sth_Weird
|
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-04-27 : 05:01:04
|
quote:
Originally posted by sth_Weird
Not using the ORDER BY causes another error saying I'm trying to update multible rows, and no matter which row I choose it is always "4 rows" (which is definitly rubbish, I am only using 1 row at a time). Is that what you mean when you say "buggy"?
No, that usually means that you have duplicate rows. Multiple rows with the same values.I'm sorry that database programming feels like programming, but if you get familiar with T-SQL, the scripts can be faster than hacking through the GUI.There are a lot of limitations on updatable views, the mail on being that an update must only affect one table. Another reason I suggest you get used to the UPDATE statement and directly update the tables--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
View not updatable because TOP clause?