Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ThreePea
Yak Posting Veteran
83 Posts |
 Posted - 2003-11-12 : 10:10:36
|
I just did some reading on fn_get_sql. Cool stuff, especially if you already know the spid. But what if you are querying sysprocesses for, say, all processes that are hogging cpu time? It would be great to use fn_get_sql like so:SELECT p.spid,p.sql_handle,CMD = ( SELECT SUBSTRING(text,1,20) FROM ::fn_get_sql( p.sql_handle) )FROM master..sysprocesses AS p (NOLOCK)WHERE p.cpu > 50000 This gives me a syntax error on the argument to fn_get_sql. The only other way I can think to do it is to use a cursor and pass each handle to fn_get_sql individually. Blech.==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-11-12 : 11:15:06
|
| Sadly, we don't get CROSS APPLY, which is exactly what you want, until Yukon. |
 |
|
|
ThreePea
Yak Posting Veteran
83 Posts |
Posted - 2003-11-12 : 11:28:00
|
quote:
Originally posted by Arnold Fribble
Sadly, we don't get CROSS APPLY, which is exactly what you want, until Yukon.
Ahh, I see. I looked up some info on CROSS APPLY and now it makes sense.BTW, I got around the problem by putting the subquery code into a UDF and calling that directly on the line. Seems to work great.Thanks.==================================================Tolerance is the last virtue of an immoral society. -- G.K. Chesterton |
|
|
|
Arnold Fribble
Yak-finder General
1961 Posts |
Posted - 2003-11-12 : 12:09:55
|
| Yeah, I realized afterward that you only had one row returned by the function result, so it would be possible to turn it into a scalar value somewhere. But then I got distracted by something else -- work probably! |
|
|
|
|
|
|
|
|
Can't use fn_get_sql() in a subquery?