What's wrong with this T-SQL select statement?

Question

Hi,

I'm reading an article from this website, but when i run the code that article provided, i get the error:

Msg 102, Level 15, State 1, Line 16
Incorrect syntax near '.'.

I double checked the syntax and can't find the cause of this error. The code is as follows, formatted by SQL Prompt.

Anybody can help me with it? Great thanks.

SELECT  es.session_id ,
        es.host_name ,
        es.login_name ,
        er.status ,
        DB_NAME(database_id) AS DatabaseName ,
        SUBSTRING(qt.text, ( er.statement_start_offset / 2 ) + 1, ( ( CASE WHEN er.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
                                                                           ELSE er.statement_end_offset
                                                                      END - er.statement_start_offset ) / 2 ) + 1) AS [Individual Query] ,
        qt.text AS [Parent Query] ,
        es.program_name ,
        er.start_time ,
        qp.query_plan ,
        er.wait_type ,
        er.total_elapsed_time ,
        er.cpu_time ,
        er.logical_reads ,
        er.blocking_session_id ,
        er.open_transaction_count ,
        er.last_wait_type ,
        er.percent_complete
FROM    sys.dm_exec_requests AS er
        INNER JOIN sys.dm_exec_sessions AS es ON es.session_id = er.session_id
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
        CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
WHERE   es.is_user_process = 1
        AND es.session_Id NOT IN ( @@SPID )
ORDER BY es.session_id

---

Edit:

I run the upper query using SSMS 2008 against a SQL Server 2005 database. When I try to use SSMS 2005 run the same query against a SQL Server 2005 database, everything works fine. As i know, SSMS 2008 can connect to a SQL Server 2005 database and operate on it, right? Also I tried another example, listed below. It's really simple one. Similarly, it execute successfully when using SSMS 2005 against SQL Server 2005. When using SSMS 2008 against SQL Server 2005, it give the same error as i mentioned early.

SELECT  stat.sql_handle ,
        sqltext.text SQL
FROM    sys.dm_exec_query_stats stat
        CROSS APPLY sys.dm_exec_sql_text(stat.sql_handle) sqltext

Anybody have some idea on this?

Thanks.

---

Edit 2:

Just tried, using SSMS 2008 against SQL Server 2008 works fine.

Answer 1

nothing wrong with the SQL per se ["works on my machine"(tm)] so it is probably a data error. The substring function is the most likely suspect. Try running it with TOP 1 and/or in reverse order.

Answer 2

You say that you're connecting to a "SQL Server 2005 database" a couple of times. Does that mean a SQL Server 2005 server?

I am able to reproduce your problem when I use SQL Server 2008 SSMS on SQL Server 2008 server. This occurs when my database compatibility level for the current database is SQL Server 2000, which doesn't have the features you're trying. When I switch the database the command works.

Check the compatibility level for your database. The command will work if SQL Server 2005 or later.