View Top Clause suddenly not working

[diobbi]

Hi,

I am copying (via cut and paste)views from 1 database to another database of the exact same structure. Any of my views that contain the "Top and Order by" clause in them are no longer working. I now get the error "Incorrect syntax near 100" or whatever number I have there. The views work fine in the other database with these statements but won't work in the new database. All the other views I copied over without the "Top" statement in them worked fine.
Any ideas as to why the "Top" statement would work against one database and not another? Thanks.

Deb

 

[MeanGreen]

My guess is that your NEW database is set with OLD version compatibility. Run the sp_dbcmptlevel against the database and see what version you are set for. Example:

EXEC sp_dbcmptlevel 'dbName' and it will show your your compatibility level. To change it:

EXEC sp_dbcmptlevel 'dbName', 80 -- this is for 2K
EXEC sp_dbcmptlevel 'dbName', 70 -- this is for 7
EXEC sp_dbcmptlevel 'dbName', 65 -- this is for 6.5

Hope this helps.

 

[diobbi]

MeanGreen,

Thank you so much for your quick response and correct answer. I had spent days on this and never would of thought of that. The new database was set to 65. I changed it to 7 and now everything works fine. Thanks again.

Deb