How do I list current running Stored Procedures with SMO?

Question

I am using C#, SQL Server 2008 and SQL Management Objects, and I need to know if there is a way to get the current executing Stored Procedure(s) using SMO/C#. I have some long-running Stored Procedures and I need to monitor their current status by determining whether they are idle or executing. Can this be done with SMO? If so, how?

Answer 1

You might be able to back into it with the EnumProccesses method on the Server object. I really feel like this is a case of "when you have a hammer, everything looks like a nail", though. That is, you'll be able to do a lot better with raw T-SQL against system views like sys.dm_exec_requests