Script mismatch different database

  • Hi,

    I want asking two question?

    1. I have two database 1. PMSPRD and 2. PMSDEV.

    First database (PMSPRD) this script was running fine, and get result like

    declare @PageCount bigint

    set @PageCount = 1000

    select

    [Table] = object_name(stats.[object_id])

    ,[Index]= si.[name]

    ,[Avg Fragmentation] = cast(stats.[avg_fragmentation_in_percent] as numeric (6,4))

    ,[Page Count] = stats.[page_count]

    ,[SQL script] = case

    when (stats.[avg_fragmentation_in_percent] < = 30)

    then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] reorganize'

    when (stats.[avg_fragmentation_in_percent] > 30)

    then 'alter index ['+ si.[name] +'] on ['+ object_name(stats.[object_id]) +'] rebuild'

    end

    from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats

    join sys.indexes as si on stats.object_id = si.object_id

    and stats.[index_id] = si.[index_id]

    where stats.[avg_fragmentation_in_percent] > 10.0

    and si.index_id > 0

    and stats.[page_count] >= @PageCount

    order by 3 desc, 1, 2;

    output

    -------

    alter index [PK_PME_Declarati] on [PME_Declaration] rebuild

    Second database getting error as below, what could be reason? it was running in first database.

    Msg 102, Level 15, State 1, Line 15

    Incorrect syntax near '('.

    ----------------------------------------------------------

    2. Update statistics

    exec sp_MSforeachdb

    ' IF (''?'' IN (''PMSPRD''))

    BEGIN PRINT ''Atualizando estatísticas de '' + ''?''

    use ? exec sp_updatestats END';

    output

    --------

    Updating [dbo].[Parameter_Master]

    [PK_Parameter_Master], update is not necessary...

    [_WA_Sys_Para_Code_02DD43D9], update is not necessary...

    [_WA_Sys_Para_Name_02DD43D9] has been updated...

    [_WA_Sys_Discharge_DIsplay_02DD43D9] has been updated...

    [_WA_Sys_Modify_By_02DD43D9] has been updated...

    [_WA_Sys_Modify_Dt_02DD43D9] has been updated...

    [_WA_Sys_CheckForNormal_02DD43D9] has been updated...

    [_WA_Sys_Unit_02DD43D9] has been updated...

    [_WA_Sys_UnderNABLScope_02DD43D9] has been updated...

    [_WA_Sys_HA_Limit_02DD43D9] has been updated...

    8 index(es)/statistic(s) have been updated, 2 did not require update.

    This table contain total 10 Index, 8 index(es)/statistic(s) have been updated, but 2 did not require update.

    Please suggestion me, why did not require update?

    Thanks

    ananda

  • Not sure about the first one, I suspect there's a different index name in there that is messing it up.

    The second question, no update on the stats was needed because it's possible there have been no data changes. If there are no inserts/updates/deletes, then there are no changes to the stats.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Use [database name]

    go

    add this in the begining of the code and then check

    Regards,

    Syed Jahanzaib Bin Hassan

    MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • What SQL Server version and compatibility mode are the two databases? The first query will only work on SQL 2005 + (and only in 90+ compatibility mode)

  • HowardW (4/19/2011)


    What SQL Server version and compatibility mode are the two databases? The first query will only work on SQL 2005 + (and only in 90+ compatibility mode)

    SQL server compatibility 100 for two database.

    one database was working fine and another database not working this query.

    rgds

    ananda

  • ananda.murugesan (4/19/2011)


    HowardW (4/19/2011)


    What SQL Server version and compatibility mode are the two databases? The first query will only work on SQL 2005 + (and only in 90+ compatibility mode)

    SQL server compatibility 100 for two database.

    one database was working fine and another database not working this query.

    rgds

    ananda

    Hmm, that's the exact error you'd get if it was due to compatibility.

    Does just running:

    select * from sys.dm_db_index_physical_stats (db_id(), null, null, null, 'LIMITED') as stats

    Give the same error?

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

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