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