November 8, 2001 at 7:41 am
I have a database in SQL 7 that was converted from SQL 6.5. For each table in database, if I right-click on the table and select 'Open Table', there are two possible selections: Return all rows and Return Top. The Return Top is grayed out and I cannot return the top 10 rows, for example. If I go to the Northwind database, everything is fine.
Also when I go to Query Analyzer and say "SELECT TOP 10 * From Employees" it gives me an error about syntax near 10. If I run this same line for Northwind, no problems. Is there a setting that is
preventing me from selecting TOP N rows from a table ?
November 8, 2001 at 8:44 am
There doesn't appear to be a database option to enable this, so I wonder if the statistics are not correct. What happens if you recreate the PK index and statistics?
(DBCC DBREINDEX and UPDATE STATISTICS).
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
November 8, 2001 at 9:53 am
When you upgrade, the db remains in 65 compatibility mode. TOP was not a valid tsql statement in 65. Change the mode of the db or use SET ROWCOUNT 10 to get the top 10 rows.
Steve Jones
November 8, 2001 at 10:53 am
I can currently use the SET ROWCOUNT to get what I need but it still prevents me from doing this in Enterprise Manager unless I change the SQL statement each time, once I open the table. My next question is: how do I change the mode of the db? Thanks for your help.
quote:
When you upgrade, the db remains in 65 compatibility mode. TOP was not a valid tsql statement in 65. Change the mode of the db or use SET ROWCOUNT 10 to get the top 10 rows.Steve Jones
November 8, 2001 at 11:43 am
November 9, 2001 at 4:31 pm
It depends which EM you have. If you have SQL 2000 EM you can change it (even on a SQL 7 server) as Steve states. Otherwise if all you have is SQL 7 EM, do it from QA (TSQL) like
exec sp_dbcmptlevel 'dbname', 70
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy