Syntax error on executing this Performance tuning query

  • Hi guys,

    I am executing this query in the query analyzer 2005.I am the sysadmin to the server.

    when I executed this query it errored with a syntax error as highlighted in red . please help me

    syntax error

    Server: Msg 102, Level 15, State 1, Line 13

    Incorrect syntax near '.'.

    select top 10 qs.sql_handle, qs.execution_count

    , qs.total_elapsed_time, qs.last_elapsed_time

    , qs.min_elapsed_time, qs.max_elapsed_time

    , qs.total_clr_time, qs.last_clr_time

    , qs.min_clr_time, qs.max_clr_time

    , substring(st.text, (qs.statement_start_offset/2)+1

    , ((case qs.statement_end_offset

    when -1 then datalength(st.text)

    else qs.statement_end_offset

    end - qs.statement_start_offset)/2) + 1) as statement_text

    from sys.dm_exec_query_stats as qs

    cross apply sys.dm_exec_sql_text(qs.sql_handle) as st

  • FYI, you have to quote the color, "red" in the color tag.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Huh, it works just fine for me.

    Are you sure the is SQL Server 2005? Is the database possibly in compatibility mode=80?

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.
    [/font]
    [font="Verdana"] "Performance is our middle name."[/font]

  • Probably compatability mode. What do the following queries return?

    SELECT @@version

    exec sp_dbcmptlevel < Name of the database you're in >

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 4 posts - 1 through 3 (of 3 total)

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