TOP create syntax error
-
may I ask what SP level is your server running?
select serverproperty('ProductVersion'),serverproperty('ProductLevel')
* Noel -
That fails too... that's why we're all stumped :
use finance
select TOP 10 *
from sysobjects
Results:Server: Msg 170, Level 15, State 1, Line 2
Line 2: Incorrect syntax near '10'.
use pubs
select TOP 10 *
from sysobjects
-
tried it with the order by ..still get the error.
-
Well that does work. But, what I was tryig to do was something like this
create my_view
as
select top 100 percent *
from my_table
order by column1
This way view is pre sorted, since you can not have just an order by within a view.
Jersey
-
8.00.194 RTM
was the result
-
yep order by works just fine.
-
RTM = Release To Manufacture.
You have the original retail version of SQL server installed you need to at least go to SP3a or SP4 if you can!!!
Cheers!
* Noel -
Hmmm I was under the impression that the service packs had been installed.
Thanks for pointing that out. I think the datacenter has some explaining to do!
-
Sorry for me to be the bad news bearer
But is is really important that you patch that server ASAP
* Noel -
I think that it's an understatement. Time to use the word needs to be done last year
. -
Update your service pack to at least SP3a and make sure your SA account has a strong password. Then retry your script.
-SQLBill
-
No its a good catch. I was under the impression that service pack 3a had been applied during a recent server upgrade project. Turns out that because my then server admin was sacked by HR, he never gave the instructions to the new admin.
Well that life in the corp world today.
Thanks for all the feed back.
--JerseyMo
-
Not quite true. TOP keyword is SQL7 (level 70) onwards only and the error is a symptom of compatabilty albeit the poster says it is not.
Remi, your code works because of the way sp_dbcmptlevel works
sp_dbcmptlevel affects the behaviors in the specified database, not the entire server. The compatibility setting for a database takes effect when the database is made the current database with the USE statement, or if the database is the default database for the login. When a stored procedure is executed, the current compatibility level of the database in which the procedure is defined is used. All stored procedures in the database are recompiled when the compatibility setting is changed in that database.
If you put batch separator (GO) after each exec sp_dbcmptlevel then you will see an error
Far away is close at hand in the images of elsewhere.
Anon. -
Wow, thanx for the tip. Here's the corrected version and results :
create database test
use test
exec sp_dbcmptlevel 'Test', 80
GO
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
--no error given
exec sp_dbcmptlevel 'Test', 70
GO
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
--no error given
exec sp_dbcmptlevel 'Test', 65
GO
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
--FAILS
exec sp_dbcmptlevel 'Test', 60
GO
exec sp_dbcmptlevel 'Test'
Select top 10 * from dbo.SysObjects
--FAILS
/*
use master
drop database test
*/
-
Thats it. I adjusted the comp level and its working.
Now this opens the disscusion of what are the potential gottcha's.
Viewing 15 posts - 16 through 30 (of 34 total)
You must be logged in to reply to this topic. Login to reply