Mathew Nolton Blog

Software dementia unleashed...

  • sp_lock2 and sp_lockcount

    Here are two helpful procedures to help with sqlserver database work. 

    1. The code for both of these are based on the system stored procedures sys.sp_lock that comes built in the master database.
    2. build both of these in your master database if you want to use them in all databases across your database instance.
    • sp_lock2 -- provides object_name for the object_id normally presented with sp_lock
    • sp_lockcount -- provides a count of the locks. useful when doing etl processes and you are monitoring locks but do not care to receive the tens of thousands of lock data coming back...when all you want to know is the count.

    sp_lock2 code

    USE [master]
    GO
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- ==================================================================
    -- Author:        Mathew Nolton (based on sp_lock in sqlserver)
    -- create date: 05/15/2009
    -- Description:    Returns lock information similar to sp_lock but
    --                with additional object name information.
    -- ==================================================================
    create procedure [dbo].[sp_lock2]
        @spid1 int = NULL,        /* server process id to check for locks */
        @spid2 int = NULL        /* other process id to check for locks */
    as
        -- ======================================================================================================
        -- set options
        -- ======================================================================================================
        set nocount on
        set transaction isolation level read committed
    
        -- ======================================================================================================
        -- do the work.
        -- ======================================================================================================
        select     convert (smallint, [req_spid])    as [spid],
                [rsc_dbid]                        as [dbid],
                db_name([rsc_dbid])                as [dbname],
                [rsc_objid]                        as [ObjId],
                object_name([rsc_objid])        as [ObjName],
                [rsc_indid]                        as [IndId],
                object_name([rsc_indid])        as [IndName],
                substring ([v].[name], 1, 4)    as [Type],
                substring ([rsc_text], 1, 32)    as [Resource],
                substring ([u].[name], 1, 8)    as [Mode],
                substring ([x].[name], 1, 5)    as [Status]
        from     [master].[dbo].[syslockinfo] [s]
                inner join [master].[dbo].[spt_values] [v]
        on        [s].[rsc_type]        = [v].[number]
                inner join [master].[dbo].[spt_values] [x]
        on        [s].[req_status]    = [x].[number]
                inner join [master].[dbo].[spt_values] [u]
        on        [s].[req_mode] + 1    = [u].[number]
        where    [v].[type]            = 'LR'
        and        [x].[type]            = 'LS'
        and        [u].[type]            = 'L'
        and        ( @spid1 is null or [req_spid] in (@spid1, @spid2) )
        order by [spid]
    
    

    sp_lockcount code

    -- ==================================================================
    -- Author:        Mathew Nolton (based on sp_lock in sqlserver)
    -- create date: 05/15/2009
    -- Description:    Returns count of locks.
    -- ==================================================================
    create procedure [dbo].[sp_lockcount]
        @spid1 int = NULL,        /* server process id to check for locks */
        @spid2 int = NULL        /* other process id to check for locks */
    as
        -- ======================================================================================================
        -- set options
        -- ======================================================================================================
        set nocount on
        set transaction isolation level read committed
    
        -- ======================================================================================================
        -- do the work.
        -- ======================================================================================================
        select     count(*)
        from     [master].[dbo].[syslockinfo] [s]
                inner join [master].[dbo].[spt_values] [v]
        on        [s].[rsc_type]        = [v].[number]
                inner join [master].[dbo].[spt_values] [x]
        on        [s].[req_status]    = [x].[number]
                inner join [master].[dbo].[spt_values] [u]
        on        [s].[req_mode] + 1    = [u].[number]
        where    [v].[type]            = 'LR'
        and        [x].[type]            = 'LS'
        and        [u].[type]            = 'L'
        and        ( @spid1 is null or [req_spid] in (@spid1, @spid2) )
    

    Read more...

  • SqlScript for dropping a set of procedures, user-defined functions or views

    I have been wanting to write some posts lately of some productivity tools and tricks. My last post was for a VS-Addin for updating the reference paths for all "selected" projects in your visual solution set.

    This post is for a sql script that enables you to drop a series of stored procedures, UDF's or Views. I thought about adding tables but then I would have to handle keys/constraints as well....maybe next time.

    PRINT N'Dropping procedures/functions...'

    GO

    PRINT N''

    GO

    declare     @name varchar(256), @type varchar(2), @sql nvarchar(1000), @printText varchar(1000)

     

    -- declare cursor

    declare localCursor cursor local fast_forward read_only for

    select      [name], [type]

    from  [sysobjects]

    where [type] in ('P','TF','FN','V')

    and (

                [name] in

                (

                      '[Put procedure/view or udf name here]',

                      '[Put another procedure/view or udf name here]'

                )

          )

          order by [type],[name]

     

    -- now open the cursor

    open localCursor

    fetch next from localCursor into @name, @type

     

    -- now loop through building list.

    while @@fetch_status=0

    begin

          if( @type = 'P' )

          begin

                set @printText = 'Dropping procedure ' + @name + ' ...'

                set @sql = 'drop procedure ' + @name

          end

          else if ( @type in ('TF','FN') )

          begin

                set @printText = 'Dropping function ' + @name + ' ...'

                set @sql = 'drop function ' + @name

          end

          else if ( @type in ('V') )

          begin

                set @printText = 'Dropping view ' + @name + ' ...'

                set @sql = 'drop view ' + @name

          end

         

          print @printText

          exec sp_executesql @sql

          fetch next from localCursor into @name, @type

    end

    close localCursor

    deallocate localCursor

     

    HTH,

    -Mathew Nolton

    Read more...

  • Add-In for updating all reference paths in a solution

    Update Project References Add-In 

    Visual Studio doesn't natively support the ability to update all selected project's within a Solution with the same reference paths. This is important (at least to me) if you have to download from your source control system a different version of your source code. Since you typically do not check in your project preferences into source control, you will be stuck with the mundane task of selecting your project, setting the reference path, select the next project, etc.

    This add-in enables you to select a given set of projects within your solution, set your reference paths and make updates across all selected projects. For example:
    UpdateReferencePaths Image

    You have a couple of options

    1. Completely Replace Existing References...enough said
    2. Do nothing if a reference exists (and append to end of list if it doesn't)
    3. Prepend to List...(note: if the reference already exists, it will be moved).
    4. Append to List...(note: if the reference already exists, it will be moved).

    To use the add-in, copy the files UpdateReferencePaths.AddIn and UpdateReferencePaths.dll to your add-in directory. Typically, this is located at: C:\Documents and Settings\[Your User Name Here]\My Documents\Visual Studio 2005\Addins

    Get the Add-In and the source code here: http://www.cybral.com/tools.aspx#updatereferencepaths

    -Mathew Nolton
    http://www.cybral.com

    Read more...

  • Using skmRss.RssFeed

    I have been evaluating the use of Scott Mitchell's RSSFeed control for a new version of my website (it's not done yet) for my consulting company that focuses on the Cable Sector of the Telecommunications Industry and I wanted to display relevant information for both Web Services and the Cable Sector. The control is very nice (and free). During my investigation I wanted to perform a couple of actions that were not quickly apparent but still quite doable once I dug a bit deeper. Specifically, I wanted to be able to:

    Read more...

  • Creating your own XmlSerializer

    Very recently I came across an issue that required the creation of a new class derived from XmlSerializer. For reasons I don't want to get into here, we serialize an object instance into XML and store it into a database column so that we can reconstitute it later. This is a great approach except for the issue of changing class definitions.

    Read more...

  • New Version of XmlPreCompiler

    Well, I finally got around to making the XmlPreCompiler even easier to use. As some of you may or may not know, the XmlPreCompiler is a tool based on a tool Chris Sell's originally created to help developer's handle the xml serialization error:

    Read more...

  • Part 2: Consulting in the Cable and Wireless Sectors....The Power of the Bundle....

    The Cable sector as well as the entire Telecommunications industry has been expending a large amount of resources to sell bundled products and services. This means that instead of just offering Analog and Digital Cable Services, Cable and Telecommunication Companies are offering Cable, High Speed Internet, Digital Telephone, Digital Video Recorders, High Definition TV, etc. in a "Bundled" product.

    Read more...

  • Fail Fast

    I found this paper about "Failing Fast" while reading Paul Lockwood's blog. It refers to Martin Fowler's wiki about writing code to Fail Fast. This technique has been around for a while; however,  I honestly have always been more of a proponent of writing defensive code. 

    Read more...