Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com

Opening View does not reflect SQL statement

I am having trouble with a VIEW. I modify the view to add a sort criteria. I can Execute the SQL and get the results I am looking for. I save the VIEW. Then if I open the VIEW using the OPEN VIEW menu option(right clicking the VIEW name) the sort order I set does not work. Please help.

Using Microsoft SQL Server Management Studio Express to access the SQL Server 2005

Hi,

this is by design. SQL Server does not guarantee to give back ordered results in a view, unless you specify the TOP clause (e.g. TOP 100 PERCENT).

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

I am having trouble with this issue, too. When I use the TOP (100) clause, the sort order is reflected when I use Open View. When I use the TOP 100 PERCENT clause, I don't get the sort order. I need to display all of the records in the view and need the sort order. Any suggestions?

Thanks!

|||

I apply Service Pack 1 for microsoft SQL Server 2005

Version : 9.00.2047

and facing the same problem and the TOP 100 Percent doesn't resolve the problem. and the sorting option (ORDER BY) working fine in preview pane , but when Open the View the sorting option doesn't work.

the workaround for this is not using the View and write direct SQL statements with ORDER BY.

Thanks

|||

Hi Jens,

Sorry to say u that ur post was not helpfull, still same problem on using SELECT TOP 100 PERCENT

I have found one link where it was written if we use SELECT TOP (100) PERCENT it will work, actually it worked but

only for NUMBER and DATE.

Why not for STRING(varchar). I am working at tokyo and my database has japanese data. what about japanese sorting..

Please give us some solution or downloadable patch to overcome this BUG of SQL Server 2005 ?

RICz
Software Specialist
Tokyo,Japan
www.rajibul.com

|||

Yes, I found a funny way to fix the sorting problem of Character in SQL Server 2005.

--It's surprising that the SQL Server tools group didn't alter the query parser to replace TOP (100) Percent with TOP (2147483647). The group also should have fixed—or warned users about—the ambiguous presentation in the Results pane for views-

Please just use the SELECT TOP (2147483647) in the SQL to support sorting of ORDER BY

It will work until microsoft fix it to work automatically

Information from
http://oakleafblog.blogspot.com/2006/09/sql-server-2005-ordered-view-and.html

RICZ
www.rajibul.com

Opening a SP to modify gives incorrect name on TAB

In SQL Server Mangement Studio when I right click on stored procedure to modify the name on the tab does not have the name of the stored procedure on it.

Is there a way to turn this on? When I more than one open how do I tell where a certain sp is?

what u see in Management Studio is Schema.StoredProcedureName... u might be seeing in Summary window where u have Name first , then Schema and Then Created... Both are same ...

Madhu

|||

The tab name currently displays an autogenerated file name with server and database information (same as what a new query would do). It does not show the stored procedure name. Currently there is no way to turn this on. Please post a suggestion for this on http://connect.microsoft.com/sqlserver. Suggestions filed on this site go directly to our internal issue tracking system and are especially taken into consideration by the team while prioritizing the future work.

Thanks,
Kuntal

Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help

Open Table with Query to modify data

In Enterprise Manager, I would right click on the table, choose Open Table and Query where I could select specific records and (most importantly) could alter data in a record by deleting the text, adding or over-typing.

In 2005 Server Management Studio I just cannot figure how to do this. I'm guessing that I need the 'Script Table as' option but then what?

I have managed to open selected data using the New Query and then Design Query in Editor, but the results only appear in a kind of view form and I cannot seem to alter any of the data entries, I get dotted lines around the selected field.

Please help, it seemed so much easier in 2000!

(1) Open the database folder for the database that contains the target table; (2) locate the target table within the database; (3) right click on the target table; (4) select the "Open Table" option; (5) the table is opened and can now be edited.

See if this also works for you.

|||

Hi

Thanks for your reply, my problem is that the table we open most often in order to edit/change fields, has over 1.5 million record rows! So we're not keen on opening the whole table. The problem with using a query is that the results can't be edited.

|||YOu can, but it is sort of hidden in the toolbox pane. Use Open table > Stop the processing of the data rows (as you do not want to wait for 1,5M rows) using the red square at the bottom of the page. Switch on the SQL Pane using the little tool in the left corner (Show SQL Pane) > Modify your query to limit the results > Execute again > You should now be able to edit the data in the results pane.

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Hi

Have done that and it works however, with more complex queries we prefer to use the Query Editor, selecting the field names and criteria in the grid and allowing the system to write the sql. Even with seemingly simple things like specifying a date time we are having to write the Convert DateTime instruction which is time consuming and somewhat frustrating.

I have worked out a stop gap solution in that I

1. open a New Query and then click the icon for Design Query in Editor

2. use the editor to write the query, copy the generated sql

3. open the table, stop the run of all records

4. Show the SQL pane and paste the query sql into it and run in order to get results that I can edit

It's not perfect and I have the problem that if I want to go back and adjust the query, I cannot open the original selection in Query Editor as it closes down after you have used it. I have to open up another new one and start again!

You could do all this in one window in Enterprise Manager, run a query, get 'editable' results and go back to the query to adjust it if need be, again getting 'editable' results.

Why have they removed this in 2005 and is it going to be resolved because I've now found other threads from users with the same problem.

Thanks

Annie

|||Well post it as a connect suggestion on http://connect.microsoft.com/sqlserver/Feedback :-)

Jens K. Suessmeyer

http://www.sqlserver2005.de
|||

Thanks Jens

Sorry we have only just set up the 2005 server and I am new to the forum so wasn't aware of the Connect site, but I will do that now.

Thanks again for the help