Locks - Revision history

Crmtogetherkb at 20:48, 30 May 2014

2014-05-30T20:48:00Z

← Older revision		Revision as of 21:48, 30 May 2014
Line 31:		Line 31:
	----		----

	~~DECLARE @ctac_CreatedDate datetime~~
	~~DECLARE @ctac_companyid int~~
	~~DECLARE @ctac_personid int~~
	~~DECLARE @ctac_ctordersid int~~
	~~DECLARE @ctac_ctorderrefid nVARCHAR(20)~~
	~~DECLARE @ctac_msg nVARCHAR(max)~~

	~~DECLARE db_cursor CURSOR FOR~~
	~~--get a list when then~~
	~~select ctac_msg,~~
	~~ctac_CreatedDate,ctac_companyid,ctac_personid,ctac_ctordersid,ctac_ctorderrefid~~
	~~from ctActivityLog~~
	~~where CONVERT(VARCHAR(MAX), ctac_msg) like 'Order Stage changed from Service to Booked'~~

	~~OPEN db_cursor~~
	~~FETCH NEXT FROM db_cursor INTO @ctac_msg, @ctac_CreatedDate,~~
	~~@ctac_companyid, @ctac_personid, @ctac_ctordersid, @ctac_ctorderrefid~~

	~~WHILE @@FETCH_STATUS = 0~~
	~~BEGIN~~
	~~select ctor_BookedOnDT, @ctac_CreatedDate, DATEDIFF(MINUTE, ctor_BookedOnDT, @ctac_CreatedDate) as 'Difference'~~
	~~from ctOrders~~
	~~where Ctor_ctOrdersID=@ctac_ctordersid~~

	~~--update ctOrders~~		UPDATE
	~~--set ctor_BookedOnDT~~=~~@ctac_CreatedDate~~		Communication
	~~--where Ctor_ctOrdersID=@ctac_ctordersid~~		SET
	~~--end~~		Comm_DateTime = ctor_bookingdatetime
			FROM
			Communication
	~~FETCH NEXT FROM db_cursor INTO~~ ~~@ctac_msg~~,~~@ctac_CreatedDate,~~		INNER JOIN
	~~@ctac_companyid~~, ~~@ctac_personid~~, ~~@ctac_ctordersid~~, ~~@ctac_ctorderrefid~~		ctOrders
			ON
	~~END~~		ctor_ctOrdersID=comm_ctorderid

	~~CLOSE db_cursor~~		and ctor_case_caseid is null and ctor_engineeruserid is not null and ctor_orderStage in ('confirmed','booked','jobdone') and DATEDIFF(day,ctor_bookingdatetime,Comm_DateTime)<>0
	~~DEALLOCATE db_cursor~~

Crmtogetherkb at 20:14, 30 May 2014

2014-05-30T20:14:23Z

← Older revision		Revision as of 21:14, 30 May 2014
Line 31:		Line 31:
	----		----

	~~dleete~~		DECLARE @ctac_CreatedDate datetime
			DECLARE @ctac_companyid int
			DECLARE @ctac_personid int
			DECLARE @ctac_ctordersid int
			DECLARE @ctac_ctorderrefid nVARCHAR(20)
			DECLARE @ctac_msg nVARCHAR(max)

	--~~-uk~~		DECLARE db_cursor CURSOR FOR
	~~update ctOrders~~		--get a list when then
	~~set ctor_bonus_cid=3~~,		select ctac_msg,
	~~ctor_subtotal_cid=3~~,		ctac_CreatedDate,ctac_companyid,ctac_personid,ctac_ctordersid,ctac_ctorderrefid
	~~ctor_vatcash_cid=3~~,		from ctActivityLog
	~~ctor_vatcost_cid=3~~,		where CONVERT(VARCHAR(MAX), ctac_msg) like 'Order Stage changed from Service to Booked'
	~~ctor_billduenow_CID=3~~
	where ~~ctor_PersonId in~~
	(
	~~select pers_personid~~
	from ~~person~~
	~~where pers_SecTerr=-805306364 --uk~~
	)

	~~update ctOrders~~		OPEN db_cursor
	~~set ctor_bonus_cid=2~~,		FETCH NEXT FROM db_cursor INTO @ctac_msg, @ctac_CreatedDate,
	~~ctor_subtotal_cid=2~~,		@ctac_companyid, @ctac_personid, @ctac_ctordersid, @ctac_ctorderrefid
	~~ctor_vatcash_cid~~=2,
	~~ctor_vatcost_cid=2~~,		WHILE @@FETCH_STATUS = 0
	~~ctor_billduenow_CID=2~~		BEGIN
	where ~~ctor_PersonId in~~		select ctor_BookedOnDT, @ctac_CreatedDate, DATEDIFF(MINUTE, ctor_BookedOnDT, @ctac_CreatedDate) as 'Difference'
	(		from ctOrders
	~~select pers_personid~~		where Ctor_ctOrdersID=@ctac_ctordersid
	~~from person~~
	where ~~pers_SecTerr~~=-~~1073741819~~ -~~-ireland~~		--update ctOrders
	)		--set ctor_BookedOnDT=@ctac_CreatedDate
			--where Ctor_ctOrdersID=@ctac_ctordersid
			--end


			FETCH NEXT FROM db_cursor INTO @ctac_msg,@ctac_CreatedDate,
			@ctac_companyid, @ctac_personid, @ctac_ctordersid, @ctac_ctorderrefid

			END

			CLOSE db_cursor
			DEALLOCATE db_cursor

Crmtogetherkb at 19:57, 30 May 2014

2014-05-30T19:57:38Z

← Older revision		Revision as of 20:57, 30 May 2014
Line 25:		Line 25:

	Ref: http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx		Ref: http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx




			----

			dleete

			---uk
			update ctOrders
			set ctor_bonus_cid=3,
			ctor_subtotal_cid=3,
			ctor_vatcash_cid=3,
			ctor_vatcost_cid=3,
			ctor_billduenow_CID=3
			where ctor_PersonId in
			(
			select pers_personid
			from person
			where pers_SecTerr=-805306364 --uk
			)

			update ctOrders
			set ctor_bonus_cid=2,
			ctor_subtotal_cid=2,
			ctor_vatcash_cid=2,
			ctor_vatcost_cid=2,
			ctor_billduenow_CID=2
			where ctor_PersonId in
			(
			select pers_personid
			from person
			where pers_SecTerr=-1073741819 --ireland
			)

Crmtogetherkb at 15:37, 18 April 2012

2012-04-18T15:37:24Z

← Older revision		Revision as of 16:37, 18 April 2012
Line 1:		Line 1:
	SQL for detecting locks in SQL		SQL for detecting locks in SQL

	SELECT L.request_session_id AS SPID,		SELECT L.request_session_id AS SPID,
	DB_NAME(L.resource_database_id) AS DatabaseName,		DB_NAME(L.resource_database_id) AS DatabaseName,
	O.Name AS LockedObjectName,		O.Name AS LockedObjectName,
Line 13:		Line 13:
	AT.name as TransactionName,		AT.name as TransactionName,
	CN.auth_scheme as AuthenticationMethod		CN.auth_scheme as AuthenticationMethod
	FROM sys.dm_tran_locks L		FROM sys.dm_tran_locks L
	JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id		JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
	JOIN sys.objects O ON O.object_id = P.object_id		JOIN sys.objects O ON O.object_id = P.object_id
Line 21:		Line 21:
	JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id		JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
	CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST		CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
	WHERE resource_database_id = db_id()		WHERE resource_database_id = db_id()
	ORDER BY L.request_session_id		ORDER BY L.request_session_id

	Ref: http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx		Ref: http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx

Crmtogetherkb: Created page with "SQL for detecting locks in SQL SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P...."

2012-04-18T15:37:08Z

Created page with "SQL for detecting locks in SQL SELECT L.request_session_id AS SPID, DB_NAME(L.resource_database_id) AS DatabaseName, O.Name AS LockedObjectName, P...."

New page

SQL for detecting locks in SQL

SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
L.resource_type AS LockedResource,
L.request_mode AS LockType,
ST.text AS SqlStatementText,
ES.login_name AS LoginName,
ES.host_name AS HostName,
TST.is_user_transaction as IsUserTransaction,
AT.name as TransactionName,
CN.auth_scheme as AuthenticationMethod
FROM sys.dm_tran_locks L
JOIN sys.partitions P ON P.hobt_id = L.resource_associated_entity_id
JOIN sys.objects O ON O.object_id = P.object_id
JOIN sys.dm_exec_sessions ES ON ES.session_id = L.request_session_id
JOIN sys.dm_tran_session_transactions TST ON ES.session_id = TST.session_id
JOIN sys.dm_tran_active_transactions AT ON TST.transaction_id = AT.transaction_id
JOIN sys.dm_exec_connections CN ON CN.session_id = ES.session_id
CROSS APPLY sys.dm_exec_sql_text(CN.most_recent_sql_handle) AS ST
WHERE resource_database_id = db_id()
ORDER BY L.request_session_id

Ref: http://weblogs.sqlteam.com/mladenp/archive/2008/04/29/SQL-Server-2005-Get-full-information-about-transaction-locks.aspx