Running the same stored procedure sequentially

Use the following system stored procedure to block the same stored procedure from running concurrently.

   sp_getapplock

Finally, you must call the following so that next process is allowed to run the same stored procedure.

   sp_releaseapplock

Note:

• The above system stored procedures must be running within a database transaction.
• If @@lock_timeout is -1, then, sp_getapplock will be block until it has been released and commit/rollback must be called. 
• If @@lock_timeout is not -1, then, the result will be less than zero (failed). But, sometimes it returns "> 0" (i.e., successfully get he app lock) and on the other hand it might fail to lock the "record in the table". In this case, error #1222 will be raised by SQL server.

Getting the deadlock details

In SQL 2012, you may query the deadlock details with the following SQL statement:

SELECT XEvent.query('(event/data/value/deadlock)[1]') AS DeadlockGraph
FROM ( SELECT XEvent.query('.') AS XEvent
       FROM ( SELECT CAST(target_data AS XML) AS TargetData
              FROM sys.dm_xe_session_targets st
                   JOIN sys.dm_xe_sessions s
                   ON s.address = st.event_session_address
              WHERE s.name = 'system_health'
                    AND st.target_name = 'ring_buffer'
              ) AS Data
              CROSS APPLY
                 TargetData.nodes
                    ('RingBufferTarget/event[@name="xml_deadlock_report"]')
              AS XEventData ( XEvent )
      ) AS src;


The following query is for SQL2008:

SELECT  CAST(event_data.value('(event/data/value)[1]',
                               'varchar(max)') AS XML) AS DeadlockGraph
FROM    ( SELECT    XEvent.query('.') AS event_data
          FROM      (    -- Cast the target_data to XML
                      SELECT    CAST(target_data AS XML) AS TargetData
                      FROM      sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s
                                 ON s.address = st.event_session_address
                      WHERE     name = 'system_health'
                                AND target_name = 'ring_buffer'
                    ) AS Data -- Split out the Event Nodes
                    CROSS APPLY TargetData.nodes('RingBufferTarget/
                                     event[@name="xml_deadlock_report"]')
                    AS XEventData ( XEvent )
        ) AS tab ( event_data )


For more details about deadlock and resolution, please read the following article:

https://www.red-gate.com/simple-talk/sql/database-administration/handling-deadlocks-in-sql-server/
https://www.red-gate.com/products/dba/sql-monitor/resources/articles/monitor-sql-deadlock

Something about deadlock

This is a nice article that talks about how to find more details about deadlock.

Finding and Extracting deadlock information using Extended Events

http://social.technet.microsoft.com/wiki/contents/articles/31280.finding-and-extracting-deadlock-information-using-extended-events.aspx

Using TRY/CATCH to handle the deadlock

https://technet.microsoft.com/en-us/library/aa175791(v=sql.80).aspx

Wait for a while..

Here is the code that you need to put your process into sleep mode:

declare @wait_time nvarchar(255)

print 'start working on it.. => current ms: ' + cast(datepart(millisecond, getdate()) as nvarchar)

set @wait_time = '00:00:00.' + left(cast(cast(rand() * 1000 as int) as nvarchar), 3)
waitfor delay @wait_time

print 'im done and waited for ' + @wait_time
    + ' => current ms: '
    + cast(datepart(millisecond, getdate()) as nvarchar)

Concatenating the records and use appropriate separator

If we have 4 records, we want to concatenate all 4 records and the last separator should be "&" symbol. We can achieve this by using row_number() function and then decide whether the separator is "," or "&".

For example:

declare @s nvarchar(max)

create table #tb1 (
    email nvarchar(255)
)
insert into #tb1 values ('a@a.com');
insert into #tb1 values ('b@a.com');
insert into #tb1 values ('c@a.com');
insert into #tb1 values ('d@a.com');

select
    @s = coalesce(@s
        + case when rowidx < cnt then ',' else ' & ' end
        + email, email)
from (
    select
        rowidx=row_number() over( order by email )
        , email
        , cnt = (select count(*) from tb1)
    from #tb1
) as a

select @s

drop table #tb1

Code sharing

I've been thinking of sharing my codes for many years and finally I decided to do it.

I have uploaded the table structures, views, stored procedures and functions into Github. You may download and use it freely. I will update the contents if I encounter the useful script and table design.

  https://github.com/lauhw/MSSQLProc

Let me know if you have anything to share with me. ;)

Get the Guid.Empty constant in MS SQL

The following function returns the Guid.Empty (.Net constant) in MS SQL:

create function dbo.fn_empty_guid ()
returns uniqueidentifier
as
begin
    declare
        @result uniqueidentifier

    set @result = cast(cast(0 as varbinary) as uniqueidentifier)
   
    return @result
   
end
go

Usage:

select dbo.fn_empty_guid()