none
Getting error Msg 102, Level 15, State 1, Line 15 Incorrect syntax near 'object_id'. RRS feed

  • Question

  • select
    dense_rank () over (order by o.name) as TableID,
    s.name + '.' + o.name as tablename, i.name as Indexname, index_id as IndexID,
    cast(1 as bit) as Use_for_Update
    into #tmp
    from sys.objects o inner join sys.indexes i on
    o.object_id = i.object_id inner join sys.schemas s on
    o.schema_id = s.schema_id
    where o.type = 'U'
    --and (o.name like 'Dim%' or o.name like 'fact%')
    and o.name not like'%Delendam'
    order by o.name
    update #tmp set Use_for_Update = 0 where tablename = 'data.fact_facility'
    select * from #tmp

    declare @tab sysname, @indname sysname, @indID int, @frag decimal(5,2), @dbID int, @sql nvarchar(max) select @dbID = database_id from sys.databases where name = db_name() declare myCur cursor for select tablename, Indexname, IndexID from #tmp where Use_for_Update = 1 and IndexID > 0 open myCur while 1=1 begin fetch next from myCur into @tab, @indname, @indID If @@fetch_status <> 0 break select @frag = avg_fragmentation_in_percent from sys.dm_db_index_physical_stats(@dbID, object_id(@tab),@indID,NULL,NULL) IF @frag between 5 and 30 begin set @sql = 'ALTER INDEX ' + @indname + ' ON ' + @tab + ' REORGANIZE' print @indname + ' ' + cast(@frag as varchar(10)) print @sql end if @frag > 30 begin set @sql = 'ALTER INDEX ' + @indname + ' ON ' + @tab + ' REBUILD' print @indname + ' ' + cast(@frag as varchar(10)) print @sql end exec (@sql) end close myCur deallocate myCur

    Wednesday, March 28, 2012 6:22 AM

All replies

  • This looks like my statement I gave you a few days before.
    On what Server version do you try to run?

    I suggest to test step by step.

    Wednesday, March 28, 2012 6:57 AM
  • I tryed it is running fine in sqlserver 2008 and i tryed it on 2005 in test server it is getting error

    Incorrect syntax near 'object_id'

    .
    Wednesday, March 28, 2012 7:04 AM
  • Did you try double-clicking the error message to see more what line the error occurs on?

    I tried to count to 15, but there was no "object_id" on the line I arrived, but lines may have been folded when I view them in my newsreader.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 28, 2012 7:30 AM
  • I tryed it is running fine in sqlserver 2008 and i tryed it on 2005 in test server it is getting error

    Incorrect syntax near 'object_id'

    .

    Please, we try to help you. It's no reason to shout.

    I test your script on a SQL 2005 and it causes some errors.

    You have to adjust the linebreaks

    Wednesday, March 28, 2012 7:47 AM
  • Tested and works fine on my SQL Server 2005 SP4

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Wednesday, March 28, 2012 8:33 AM
  • Ok thanks ..

    Wednesday, March 28, 2012 8:39 AM
  • Ok thanks ..

    Did that solve??

    Welcome to MSDN Forums.
    Feel free to ask your questions and Please Note to Vote all helpful topics and Mark all answering posts.

    You Value Community's efforts and Community values your faith.
    - Arun Kumar Allu

    Wednesday, March 28, 2012 9:07 AM