Category: Uncategorised

Microsoft MVP Summit

Last month I had the great honour of winning a Microsoft MVP Award.  Most of my friends and family don’t really understand what a big deal it is to win this award.  I was over the moon.  This is an award that Microsoft gives to folks who have contributed their time and energy to help others in the Microsoft community.  There are hundreds of MVPs around the world.  Many of them have been given an MVP every year for many years, in order to acknowledge their continued service to the community and to gain their expertise in evolving the Microsoft tool set.  They have to win it back every year.  No small feat.  I won mine in the SQL Server area of expertise, in part because of this blog that I write. It seems that the solutions I log here are helpful to a lot of people.

This week I am attending the MVP Summit in Seattle. I have found it humbling and exciting to be in the company of such great minds who are dedicated to excellence.  It is energizing to meet with like minded people who are dedicated to finding new and innovative solutions and to serving their customers in the best way possible.  I have enjoyed talking to MVPs and Microsoft employees from around the world who are experts in their fields, and am learning a lot from them.  I find it reassuring to know that there are people out there, a lot of people, who are continually innovating for the betterment of our future.  Some days I get tired, and forget that they’re out there.  I have found renewed energy in being here at the conference.  John Galt is alive and well.

There is one thing about this conference that I find really surprising.  The lack of women.  I know that women haven’t traditionally gravitated to I.T. careers, but I have seen that trend changing over the years.  I attended the SQL Pass conference 2 years ago and saw quite a few women there.  But here at the MVP conference I see only a smattering.  Why is that?  Are they too busy juggling home and work to have the time to pursue additional interests?  I’m very curious.  I would like to see more women pursue and win MVPs.  They can offer an additional perspective.  Technology is quickly becoming pervasive in our homes and workplaces. And there is no denying that, with regard to technology, the Microsoft community has a huge affect on the direction in which our society will go. I think there will be an unbalanced perspective if women do not also contribute their thoughts and communicate their values in shaping this direction. This concerns me deeply.

I head home tomorrow.  It has been a whirlwind of gaining information and connecting with my peers.  I am leaving with the understanding that with this award comes the responsibility of helping Microsoft and its community to evolve and grow.  It may be because I’m a geek, but I find that very exciting.

Check if Stored Procedure is Running

Here is a sweet little script that can check which stored procedures are running and perform an action.  In this case I need to update a configuration table if the stored procedure is not running.

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

DECLARE @spName NVARCHAR(MAX)    -- the string we're looking for. The variable to become a parameter if you wish to extend this sp
DECLARE @handle SMALLINT    -- the spid of the process
DECLARE @sql NVARCHAR(MAX)  -- the dynamic SQL
DECLARE @table TABLE ( EventType nvarchar(30) , [Parameters] int , EventInfo nvarchar(4000) )   -- the table variable holding the result of DBCC INPUTBUFFER execution

DECLARE @tblProcs as TABLE (i int identity, spName nvarchar(100),IsRunning BIT) 
DECLARE @i int
SET @i = 1

INSERT INTO @tblProcs (spName, IsRunning) SELECT Name, 0 as IsRunning FROM dbo.sysobjects WHERE (type = 'P')

    DECLARE procs CURSOR FOR SELECT session_id FROM sys.dm_exec_requests WHERE status IN ('running', 'suspended', 'pending', 'runnable') AND session_id <> @@SPID ORDER BY session_id DESC  -- these are the processes to examine

    OPEN procs
    FETCH NEXT FROM procs INTO @handle
    WHILE @@FETCH_STATUS=0 
    BEGIN

WHILE @i <= (Select count(*) from @tblProcs)
BEGIN

SELECT @spName = spName FROM @tblProcs WHERE i=@i

        BEGIN TRY            
            DELETE FROM @table

            SET @sql = 'DBCC INPUTBUFFER(' + CAST(@handle AS NVARCHAR) + ')'
                
            INSERT INTO @table
            EXEC (@sql)

            SELECT @sql = EventInfo FROM @table
        END TRY
        BEGIN CATCH
            SET @sql = ''
        END CATCH
        
        IF CHARINDEX( @spName, @sql, 0 ) > 0
        BEGIN
            UPDATE @tblProcs SET IsRunning = IsRunning + 1 WHERE spName = @spName
        END

SET @i = @i + 1
END

        FETCH NEXT FROM procs INTO @handle
    END
    CLOSE procs DEALLOCATE procs

UPDATE [admin].[ConfigurationsApp] SET [Value] = 'NOT RUNNING' 
FROM [admin].[ConfigurationsApp]
INNER JOIN @tblProcs as p on P.spName = [admin].[ConfigurationsApp].Name
WHERE IsRunning = 0

END

Thanks to John Billiris who provided a good portion of this solution in this post