?
Solved

SQL text using cross apply

Posted on 2013-01-29
4
Medium Priority
?
443 Views
Last Modified: 2013-01-29
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

0
Comment
Question by:reticentKoala
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Accepted Solution

by:
liija earned 2000 total points
ID: 38830731
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38830963
I can't see text-column in your bottom one...
That would certainly explain it.  :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38830972
But seriously, you will get a value of NULL for text when there is no sql_handle as you are using OUTER APPLY.
0
 

Author Closing Comment

by:reticentKoala
ID: 38830973
Doh. Thanks.

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question