| Author |
Topic |
|
MightyGorilla
Starting Member
7 Posts |
 Posted - 2007-07-23 : 09:22:05
|
| We frequently have blocking issues and need to run sp_lock, sp_who, etc. to determine what is going on - and I'd like some way to script some of this functionality to omit some of the thousands of rows I don't need to see, and link ids to table names. I've been searching through the BOL and the web and only found a few blurbs seeming to say this isn't possible. Is it really not possible to do anything with the result set of an SP? Select it, sort it, join it, etc?Thanks, Travis- |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2007-07-23 : 09:32:35
|
| you can save it into a table.crete table yourTable (columns must match the results' columns and datatypes)insert into yourTableexec yoursproc_______________________________________________Causing trouble since 1980blog: http://weblogs.sqlteam.com/mladenp |
 |
|
|
MightyGorilla
Starting Member
7 Posts |
Posted - 2007-07-23 : 09:48:55
|
| Glad to see there is some way to handle the data, however it stinks that the last locking issue we had that caused me to post my question was specifically related to temp_db, so that issue would preclude me from using this method (at least with temp# tables). I suppose I could just create normal tables and drop them instead..... :)Thanks,Travis- |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-23 : 10:02:51
|
quote:
Originally posted by MightyGorilla
Glad to see there is some way to handle the data, however it stinks that the last locking issue we had that caused me to post my question was specifically related to temp_db, so that issue would preclude me from using this method (at least with temp# tables). I suppose I could just create normal tables and drop them instead..... :)
try the temp tables first. Your reasoning doesn't make sense.you might also be able to re-code your stored procedure into an inline table-valued function. If it can be re-coded into a function you will be able to use it in your FROM clause (like a view). Think of the TVF as a parameterized view. here is an article to check out for more info http://www.sqlteam.com/article/intro-to-user-defined-functions-updated-ec |
|
|
|
MightyGorilla
Starting Member
7 Posts |
Posted - 2007-07-23 : 11:20:55
|
| eyechart, If I get this error from EM, "Error 1222: Lock Request time out period exceeded",according to the MS knowledgebase acticle [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;308518&Product=sql2k[/url] there are locks being held preventing my use of temp_db. (That was my reasoning - whether it makes sense or not.) Also - I was referring to MSSQL build-in SPs, not stuff I wrote- so I don't know that I can rewrite any of them.Travis- |
|
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2007-07-23 : 13:12:19
|
quote:
Originally posted by MightyGorilla
eyechart, If I get this error from EM, "Error 1222: Lock Request time out period exceeded",according to the MS knowledgebase acticle [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;308518&Product=sql2k[/url] there are locks being held preventing my use of temp_db. (That was my reasoning - whether it makes sense or not.) Also - I was referring to MSSQL build-in SPs, not stuff I wrote- so I don't know that I can rewrite any of them.
that is only a problem with Enterprise Manager. That does not prevent you from using tempdb, just from refreshing the active processes list in Enterprise Manager. You can get the equivalent information by running sp_who2 from Query Analyzer.This article is just explaining why we sometimes get this error when trying to display current activity in EM. It does not indicate a bigger issue or any real problem that would prevent you from using tempdb.So, what I am saying is try using the temp table suggestion. -ec |
|
|
|
MightyGorilla
Starting Member
7 Posts |
Posted - 2007-07-23 : 13:35:38
|
| 10-4. I mis-read the KB article. I see now it only says the sysobjects, sysindexes, and syscolumns tables in tempdb are inaccessible. My bad. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Way to use SP-generated result set?