Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
DBADave
Constraint Violating Yak Guru
366 Posts |
 Posted - 2008-10-02 : 14:15:23
|
| To find the exact SQL statement executed at a point in time I can run the script below, which I found in BOL and various blogs. Can someone explain why statement_start_offset is divided by 2 and then 1 is added and statement_start_offset is divided by 2 and then 1 is added? If statement_start_offset is the number of characters into the currently executing batch or stored procedure at which the currently executing statement starts and statement_end_offset is the number of characters into the currently executing batch or stored procedure at which the currently executing statement ends, why not just subtract statement_start_offset from statement_end_offset? I'm certain there is a good explanation, but I can't figure it out.SELECT a.session_id,SUBSTRING(c.text, (a.statement_start_offset/2)+1, ((CASE a.statement_end_offset WHEN -1 THEN DATALENGTH(c.text) ELSE a.statement_end_offset END - a.statement_start_offset)/2) + 1) as statement_text FROM sys.dm_exec_requests aJOIN sys.dm_exec_sessions bon a.session_id = b.session_idCROSS APPLY fn_get_sql (a.sql_handle) cThanks, Dave |
|
|
mcrowley
Aged Yak Warrior
771 Posts |
Posted - 2008-10-02 : 16:43:59
|
| At a guess, I would imagine that the query string is encoded in UNICODE, which is a double byte character system. As for the adding one, the offset for the first query would logically be 0, which is an invalid value for substring. |
 |
|
|
|
|
|
Explain code used to find currently executing SQL