Help with a T-SQL Script

Question

Post reply

Help with a T-SQL Script

david.ostrander

SSCommitted

Points: 1828
More actions
March 30, 2010 at 10:57 am

#74601

Hello -
I'm in the process of using a script that will help with re-indexes. I keep getting the following errors.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ELSE'.
My database name is Backdrop and the table is dbo.PJTRAN and the index is pjtran0. Here is the script if someone can help me understand why I keep getting those errors.
IF EXISTS (SELECT [object_id],[index_id],[avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats
(DB_ID(N'Backdrop'), OBJECT_ID(N'PJTRAN.PJTRAN0'), NULL, NULL , 'DETAILED')
WHERE [avg_fragmentation_in_percent] < 30)
BEGIN
ALTER INDEX ALL ON PJTRAN REORGANIZE
END
ELSE
ALTER INDEX ALL ON PJTRAN REBUILD
Thank you..

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Does the select statement work outside of the EXISTS?

david.ostrander SSCommitted Points: 1828 More actions · Answer 2

Hi - I tried to run the 'Select' minus the EXISTS and still got the same result. Is that what you needed me to try?

Thanks

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 3

david.ostrander (3/30/2010)
IF EXISTS (SELECT [object_id],[index_id],[avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats
(DB_ID(N'Backdrop'), OBJECT_ID(N'PJTRAN.PJTRAN0'), NULL, NULL , 'DETAILED')
WHERE [avg_fragmentation_in_percent] < 30)
BEGIN
ALTER INDEX ALL ON PJTRAN REORGANIZE
END
ELSE
ALTER INDEX ALL ON PJTRAN REBUILD

Well first are you trying this query on SQL Server 2000 or 2005? You posted in the 7,2000 forum. Doing Parse within query connected to SQL Server 2000 shows the error. Doing it in SQL Server 2005 connection does not.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

david.ostrander SSCommitted Points: 1828 More actions · Answer 4

Thank you for picking that up that I posted in the wrong group. I'm query in SQL 2005.

Do you know can I move this post to another forum? Or do I have to start over again.

Thanks

Garadin One Orange Chip Points: 29613 More actions · Answer 5

Reposted to SS2K5 Forum, no more replies here please.

New Thread

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]