SQL text using cross apply

Can someone tell me why the top query succesfully returns teh SQL text but the bottom one does not?



SELECT GETDATE(), *
FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text(sql_handle)

SELECT 
GETDATE() AS datetime, spid, kpid, blocked, waittype, waittime, lastwaittype, waitresource, uid, cpu, physical_io, memusage, login_time, last_batch, ecid, open_tran, status, sid, hostname, program_name, hostprocess, cmd, nt_domain, nt_username, net_address, net_library, loginame, context_info, sql_handle, stmt_start, stmt_end, request_id
FROM sys.sysprocesses
OUTER APPLY sys.dm_exec_sql_text(sql_handle)

Open in new window

reticentKoalaAsked:
Who is Participating?
 
liijaConnect With a Mentor Commented:
I can't see text-column in your bottom one...

try

SELECT
GETDATE() AS datetime, text
FROM sys.sysprocesses
OUTER APPLY sys.dm_exec_sql_text(sql_handle)
WHERE text is not NULL
0
 
Anthony PerkinsCommented:
I can't see text-column in your bottom one...
That would certainly explain it.  :)
0
 
Anthony PerkinsCommented:
But seriously, you will get a value of NULL for text when there is no sql_handle as you are using OUTER APPLY.
0
 
reticentKoalaAuthor Commented:
Doh. Thanks.

That's fine. I want to see processes even where there is no associated tsql.
0
All Courses

From novice to tech pro — start learning today.