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 top 1 [text] FROM ::fn_get_sql(sysprocesses.sql_handle)
) as sqlcommand
The error back is incorrect syntax near 'sysprocesses'. I can't see if I'm doing anything obvious wrong.
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.
declare @sql_handle binary(20);
declare c1 cursor for select sql_handle from sysprocesses where status='runnable';
fetch next from c1 into @sql_handle;
while (@@FETCH_STATUS =0)
,substring(b.program_name,1,20) as Program
from ::fn_get_sql(@sql_handle) a
, sysprocesses b
, sys.sysdatabases c
where b.sql_handle =@sql_handle
fetch next from c1 into @sql_handle
;END close c1