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

open recordset error

I get a 'data type mismatch in criteria expression' when trying to open a recordset from a query that uses a variable in the match. It works fine if I use the query without the varible. The error occurs at the rs1.open line, not the sqlstmt line.

ticket=1002
db_file = App.Path & "\goldpos.mdb"

Set cn = New ADODB.Connection
cn.CursorLocation = adUseClient
cn.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & db_file & ";" & _
"Persist Security Info=False"
cn.Open

'sqlstmt = "SELECT * FROM orders where [invoice] = 1002" THIS WORKS
sqlstmt = "SELECT * FROM orders where [invoice] = '" & ticket & "'" 'THIS DOESN'T WORK

Set rs1 = New ADODB.Recordset

rs1.Open sqlstmt, cn, adOpenStatic, adLockOptimistic, adCmdText

Set DataGrid1.DataSource = rs1Originally posted by JerryB
'sqlstmt = "SELECT * FROM orders where [invoice] = 1002" THIS WORKS
sqlstmt = "SELECT * FROM orders where [invoice] = '" & ticket & "'" 'THIS DOESN'T WORK

Why are you adding single quotes around ticket if it just wants a number? Why not:

sqlstmt = "SELECT * FROM orders where [invoice] = " & ticket