select top not working 2

[shresthaal]

DECLARE @LogicalFileName SYSNAME,
@MaxSize INT,
@OriginalSize INT,
@StringData VARCHAR(500)

SELECT TOP 1
@LogicalFileName = RTRIM( [name] ), @MaxSize = CASE [maxsize]
WHEN -1 THEN -1
ELSE ([maxsize] * 8)/1024
END
FROM sysfiles
WHERE [groupid] = 0
ORDER BY [size] DESC;

print @LogicalFileName

I have this code I thought this would work am I missing something?

 

[Catadmin]

Could you tell us what it's actually doing? Then tell us what you think it should be doing.

Other than that, if I'm reading this right, you're trying to set a Variable with a Select Top 1 and I don't think it's going to work the way you have it worded.

Try:

Set @LogicalFileName = 
SELECT TOP 1 RTRIM( [name] )
FROM sysfiles
WHERE [groupid] = 0
ORDER BY [size] DESC

I'm just guessing that's what your problem is since you haven't stated what the exactly problem is.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shresthaal]

catadmin

the query is supposed to select the biggest log file in a given database and set @LogicalFileName to sysfiles.name and
@maxfiles to sysfiles.size

 

[Catadmin]

So, what is it doing instead of that?



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shresthaal]

Its giving me this error

Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '1'

I don't even know if it works right

also if i tried doing

select top 1 *
from sysfiles
where [groupid] = 0
order by [size] desc it will given me the same error

what i am trying to do is truncate my transaction log file because i forgot to put a limit on it and i have been trying to truncate it so that i have some space

 

[vongrunt]

Both queries work fine for me...

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[shresthaal]

But why would it give me an error?

Is there a special way to run it or special permission ?????

I am confused because I won't work me

 

[Catadmin]

Sounds like you have some hidden character or that your error isn't on Line 6 at all. Like vongrunt did, I copied your code from the first post directly and just tested it in QA. Parses fine and then runs fine, no errors.

Of course, I am sysadmin on my machine.

Try cut-n-paste from your first post in a brand new window (don't reuse the old one) and see if it works now. Otherwise, look for anything in your window that indicates you may have hit an extra key or there may be something hiding in the faaaaaaarrr right side of the QA screen (past where you can see).



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[JamesLean]

What version of SQL Server are you using?
What compatibility level is your database set at?

Chances are, the answer to one/both of these question is 6.5 - which means TOP is invalid.

--James

 

[shresthaal]

Version SQL 8.00.2039
Compatibility level 65

 

[Catadmin]

Compatibility level of 65 is set for SQL Server 6.5, which means TOP won't work. Change it to 70 or 80 (SQL Server 2000) if you can.

But verify first that you can actually make the change without screwing up your data.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[JamesLean]

Compatibility level 65

Bingo! The TOP keyword is invalid in SQL 6.5. If possible, you should change the compat level to 80 to take advantage of full SQL 2000 syntax.

--James

 

[shresthaal]

i even tried typing in the whole thing again but it gives me the same error near 1 i do not understand what is going on is there a setting i need to change i know top is valid with the version i am using any other clues and i have full privelage on the database and the server

 

[Catadmin]

i know top is valid with the version i am using

It doesn't matter that you're using SQL Server 2000. What matters is that you TOLD SQL 2k to use the T-SQL Commands that are compatable with SQL 6.5. Since TOP is NOT VALID with 6.5, you won't be able to use it unless you change compatibility levels.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shresthaal]

EXEC sp_dbcmptlevel 'databasename', 80

is this the way to do it?

 

[shresthaal]

thank you for you guys help i feel really stupid but i learnt a lot thank you

 

[JamesLean]

Yes

--James

 

[Catadmin]

Don't feel stupid. Even we had to learn stuff the hard way. @=)

You should have seen some of the questions I asked (and still do) when I first showed up here. @=)



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[shresthaal]

Thanks again for both y'alls help