Need to obtain SQL Statement for SPID
-
How would I get the SQL statement for a SPID using T-SQL? I do not have access to any tools just the command line utility.
Regards,
David
Best Regards,
~David
-
try.
DBCC INPUTBUFFER(spid)
Shows you most of it, but now always all of it.
-
That seems to work! Thank you.
David
Best Regards,
~David
-
Or you can try something like the statement below:
SELECT
ss.is_user_process ,
ss.session_id,
ss.host_name,
ss.login_name,
ss.nt_domain,
ss.nt_user_name,
ss.status,
ss.total_elapsed_time,
ss.reads,
ss.writes,
ss.logical_reads,
ss.cpu_time as SessionCPU, -- CPU time, in milliseconds, that was used by this session.
ss.row_count,
ss.prev_error,
(select m.text from sys.messages m where ss.prev_error = m.message_id and m.language_id = 1033) as ErrorMessage,
r.command,
r.cpu_time, -- CPU time in milliseconds that is used by the request.
r.percent_complete,
s.blocked,
s.waittime,
s.cpu as SysProcCPU,
(
SELECT
SUBSTRING(text, statement_start_offset/2,
(CASE WHEN statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), text)) * 2
ELSE statement_end_offset
END -statement_start_offset)/2)
FROM sys.dm_exec_sql_text(r.sql_handle)
 
AS QueryText FROM
sys.dm_exec_sessions ss
inner join sys.dm_exec_requests r on r.session_id = ss.session_id
inner join sys.sysprocesses s on s.spid = r.session_id
WHERE
is_user_process <> 0
Cheers,
R
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply