Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 631
  • Last Modified:

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
0
jsyers
Asked:
jsyers
2 Solutions
 
anyoneisCommented:
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

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now