Find Table Locked Session

Some time your table may locked by some session and you want to know that session and why i can not access my table?

This mean your begain transaction is completed and insert/update/delete is runing but Comit transacion is yet be run

OR you can create the situation by running the below query:


BEGIN TRANSACTION
DELETE * FROM Table1

Below script will allow you to find which session is Locking your table:

SELECT
OBJECT_NAME(P.object_id) AS TableName,
Resource_type,
request_session_id
FROM
sys.dm_tran_locks L
join sys.partitions P
ON L.resource_associated_entity_id = p.hobt_id
WHERE   OBJECT_NAME(P.object_id) = 'Table1'

To release the Loking you can kill that session using below script:

KILL 54

 

If you want to know in detail why, when and which session is locking then below script is useful:


SELECT
blocking_session_id AS BlockingSessionID,
session_id AS VictimSessionID,
(SELECT [text] FROM sys.sysprocesses
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE spid = blocking_session_id) AS BlockingQuery,
[text] AS VictimQuery,
wait_time/1000 AS WaitDurationSecond,
wait_type AS WaitType,
percent_complete AS BlockingQueryCompletePercent
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text([sql_handle])
WHERE blocking_session_id > 0

IDENTITY_INSERT Scope level

Some time we need to insert in the identity field also but what will be the impact if we want to Allow the  identity insert, see the below example:

Create a Test Table :
————————————–

CREATE TABLE sample(
id int IDENTITY(1,1)
,name VARCHAR(100)
,address VARCHAR(100)
,phoneNo VARCHAR(15)
)

In One Session, I am going to Allow the Identity Insert
————————————————————————–

SET IDENTITY_INSERT dbo.sample ON;

INSERT INTO sample(id,name,address,phoneNo)
SELECT 10,'user-2','address-2','014002555-2'


SELECT * FROM sample

In another session i am going to insert normally without ID value:
——————————————————————-
INSERT INTO dbo.sample(name,address,phoneNo)
SELECT 'user','address','014002555'

SELECT * FROM sample

—————————-

So the Conclusion is IDENTITY_INSERT will be Enabled on Session Level only. You can insert with identity value from one session and another session will be working normally with auto identity value.

UPDATE with top clause

If you want update a table with top clause see the below script:

UPDATE top (1) TABLE1
SET status ='ready'
where status ='hold'

At the same time if you want to return the Row IDs of updated table:

UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid
where status ='hold'

—————— result—————-
rowid
1

Same thing if you want to store in variable

DECLARE @UpdatedIds table (id int)
DECLARE @rowId int


UPDATE top (1) TABLE1
SET status ='ready'
OUTPUT INSERTED.rowid INTO @UpdatedIds
where status ='hold'

SELECT @rowId=id FROM @UpdateIds
SELECT @rowId

HTTP Get method from MSSQL

If you want to call a URL and store the response of that url use this function:

ALTER function [dbo].[GetHttp]
(
@url varchar(8000)
)
returns varchar(8000)
as
BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)

EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win


EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text


END

Http Post from MSSQL

Call the HTTP post using MSSQL script :


ALTER function [dbo].[POSTHttp]
(
@url varchar(8000),
@data varchar(2000)
)
returns varchar(8000)
as

BEGIN
DECLARE @win int
DECLARE @hr  int
DECLARE @text varchar(8000)


EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'Open',NULL,'POST',@url,'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win, 'setRequestHeader',NULL, 'Content-type'
, 'application/x-www-form-urlencoded'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAMethod @win,'Send',null,@data
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr=sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

RETURN @text

END

CTE Function

CTE fucntion can be used on this way:

IF OBJECT_ID('tempdb..#dataList') IS NOT NULL
DROP TABLE #dataList

CREATE TABLE #dataList(
id            INT
,name        VARCHAR(50)
,parent        INT
,ip            INT
)


INSERT #dataList
SELECT 1    id, 'Group1'        Name, 0 Parent, 0 ip UNION ALL
SELECT 2    id, 'SubGroup1'        Name, 1 Parent, 0 ip UNION ALL
SELECT 3    id, 'Item1'            Name, 2 Parent, 1 ip UNION ALL
SELECT 4    id, 'Item2'            Name, 2 Parent, 1 ip UNION ALL
SELECT 5    id, 'Item3'            Name, 2 Parent, 1 ip UNION ALL
SELECT 6    id, 'Item4'            Name, 2 Parent, 1 ip UNION ALL
SELECT 7    id, 'SubGroup2'        Name, 1 Parent, 0 ip UNION ALL
SELECT 8    id, 'Item5'            Name, 7 Parent, 1 ip UNION ALL
SELECT 9    id, 'Item6'            Name, 7 Parent, 1 ip UNION ALL
SELECT 10    id, 'Item7'            Name, 7 Parent, 1 ip UNION ALL
SELECT 11    id, 'Item8'            Name, 7 Parent, 1 ip

--SELECT * FROM #dataList


DECLARE @id INT = 1
;WITH CTE(id, name, parent, ip)
AS
(
SELECT * FROM #dataList WHERE id = @id
UNION ALL
SELECT d.* FROM #dataList D
INNER JOIN CTE c ON d.parent = c.id
)

SELECT * FROM CTE WHERE ip = 1 ORDER BY id ASC;

Dynamic PIVOT

Sample for Dynamic PIVOT Query:

Some time we need complex cross tab result for report, that time we can use the PIVOT Query, for example we have 3 Tables and we have data like this :


IF OBJECT_ID('tempdb..#customer') IS NOT NULL
DROP TABLE #customer

CREATE TABLE #customer (
CustomerId INT,
CustomerName VARCHAR(50)
)


INSERT #customer
SELECT 1, 'Ram'
INSERT #customer
SELECT 2, 'Hari'
INSERT #customer
SELECT 3, 'Depesh'
INSERT #customer
SELECT 4, 'Sailesh'

--Select * from #customer



————————————————————————–

IF OBJECT_ID('tempdb..#LoanType') IS NOT NULL
DROP TABLE #LoanType

CREATE TABLE #LoanType (
LoanId INT,
LoanName VARCHAR(50)
)


INSERT #LoanType
SELECT 1, 'Loan-1'
INSERT #LoanType
SELECT 2, 'Loan-2'
INSERT #LoanType
SELECT 3, 'Loan-3'
INSERT #LoanType
SELECT 4, 'Loan-4'
INSERT #LoanType
SELECT 5, 'Loan-5'
INSERT #LoanType
SELECT 6, 'Loan-6'

--Select * from #LoanType


——————————————————————————

IF OBJECT_ID('tempdb..#LoanCustomer') IS NOT NULL
DROP TABLE #LoanCustomer


CREATE TABLE #LoanCustomer (
CustomerId INT,
LoanId INT,
Amount MONEY
)


INSERT #LoanCustomer
SELECT 1, 1, 500
INSERT #LoanCustomer
SELECT 2, 1, 5300
INSERT #LoanCustomer
SELECT 3, 3, 1500
INSERT #LoanCustomer
SELECT 2, 1, 2500
INSERT #LoanCustomer
SELECT 1, 6, 5500
INSERT #LoanCustomer
SELECT 2, 4, 1100
INSERT #LoanCustomer
SELECT 2, 3, 3200
INSERT #LoanCustomer
SELECT 2, 3, 3200

--Select * from #LoanCustomer


——————————————————————————–

Combine Result of three table from PIVOT Query:


DECLARE
@sql VARCHAR(MAX)
,@LoanType VARCHAR(MAX)


SELECT
@LoanType = ISNULL(@LoanType + ', ', '') + '[' + LoanName + ']'
FROM (
SELECT
DISTINCT
l.LoanName
FROM #LoanCustomer lc
INNER JOIN #LoanType l ON lc.LoanId = l.LoanId
) x


SET @sql = '
SELECT
CustomerName
,' + @LoanType +
Sample for Dynamic PIVOT


FROM (
SELECT
c.CustomerName,
l.LoanName,
lc.Amount
FROM #LoanCustomer lc
INNER JOIN #customer c ON lc.CustomerId = c.CustomerId
INNER JOIN #LoanType l ON l.LoanId = lc.LoanId
) dataList
PIVOT
(
SUM(Amount)
FOR LoanName IN(' + @LoanType+ ')
) X '


EXEC (@sql)


 

This Script is Prepared by Netra Prasad Acharya (MSSQL Developer)

Follow

Get every new post delivered to your Inbox.