Monday, March 19, 2012

joining sysprocesses with fn_get_sql

ok, I have a requirement where I need to get a list of sql commands currently being blocked.
This is very easy to do via stored procedure, and I have it working well using a vb.net console app to fire it off.

Trouble is, I need to ship it to different offices on an ad hoc basis. I don't want to install a stored procedure on each site because it'll be a one off job and there is not likely to be anyone available who would know how to even install a new sp. So, I thought I'd try and pull back the sql commands via a select statement, joining together sysprocesses and the fn_get_sql udf. This returns a table, so I presumed I could join the two together using a subquery via the sql_handle with something like this :

SELECT sql_handle,
(
SELECT top 1 [text] FROM ::fn_get_sql(sysprocesses.sql_handle)
) as sqlcommand

FROM master..sysprocesses

The error back is incorrect syntax near 'sysprocesses'. I can't see if I'm doing anything obvious wrong.

Anyone any ideas? I'm using SQL Server 2000 sp3.A BOL example for using fn_get_sql:
DECLARE @.Handle binary(20)SELECT @.Handle = sql_handle FROM sysprocesses WHERE spid = 52SELECT * FROM ::fn_get_sql(@.Handle) HTH|||thanks for your reply, however, that will only work in a stored procedure.
I won't be able to install a sp, as it needs to be completely standalone.
I'm trying to find a one-hit SQL query to do the job. It should work, but it doesn't.

cheers,

No comments:

Post a Comment