Incorporating fn_get_sql in a Select statment

Hello,

I want to replace ", sql_handle" in the lower script with:
DECLARE @Handle binary(20)
SELECT @Handle = sql_handle FROM sysprocesses WHERE spid = @spud
SELECT [text] FROM ::fn_get_sql(@Handle)
 
to get the text of the command text in the following script:

SELECT spid as [SPID]
      , loginame as [Login]
      , program_name as [Program Name]
      , datediff(second,login_time, getdate()) as [Connected Seconds]
      , sql_handle
FROM master..sysprocesses
WHERE spid > 50

I have tried "while" and "cursors" but no avail!

HELP!

Joseph
jsyersAsked:
Who is Participating?
 
anyoneisSoftware DeveloperCommented:
Weird. I played with this for quite a while and it looks like the results are pretty intermittent. There must be some things that cause the handle to be invisible to the function.

For example, I ran this while a cursor driven query was running:

SET NOCOUNT ON
DECLARE @spud integer
set @spud = 53
DECLARE @Handle varbinary(20)
SELECT @Handle = sql_handle FROM master..sysprocesses WHERE spid = @spud
SELECT sql_handle FROM master..sysprocesses WHERE spid = @spud
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)
SELECT text FROM ::fn_get_sql(@Handle)

I ran it a bunch of times before I finally got the output:

sql_handle                                
------------------------------------------
0x010015005F815326C0A07C300000000000000000

text                                                                            
---------------------------------------------------------------------------------

DECLARE @PhoneNumber varchar(12), @ProjID int, @AttemptID int
DECLARE @ScreeningData varchar (4096)
DECLARE @KeyInfo TABLE (PhoneNumber varchar(12), ProjID int)
[...]


text                                                                            
---------------------------------------------------------------------------------

DECLARE @PhoneNumber varchar(12), @ProjID int, @AttemptID int
DECLARE @ScreeningData varchar (4096)
DECLARE @KeyInfo TABLE (PhoneNumber varchar(12), ProjID int)
[...]

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

text                                                                            
---------------------------------------------------------------------------------

So the handle is changing pretty rapidly. That's why it is so hard for you to catch it.

David
0
 
bwdowhanCommented:
I've been playing with this for awhile also and found a lot of strange things also... There are certain processes that won't display and if the optimizer doesn't create a plan it won't show up unless you turn on trace flag 2861...

I did find a nice example of a stored procedure that uses this function. It could easily be modified to include the extra information you are looking for. Here is the link:

http://vyaskn.tripod.com/fn_get_sql.htm
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.