/************************************
Chapter 15 Scripts
For MCITP SQL Server 2005 Database Administration All-in-One Exam Guide
By Darril Gibson

The scripts available here are the same ones used in each of the chapters. Ideally, you would type in the script and learn from any errors you might make. Occasionally, however, you may want a copy of the script to paste into the query window or into a batch file. If so, feel free to use these script files. 

I strongly encourage you not to rely on these pre-typed scripts, but instead to get used to typing the T-SQL yourself. The more you do it (even if you’re just typing what you see in the book), the more comfortable you’ll get with T-SQL.

Some scripts in the book are intended to be run from the command line. They've been included where appropriate so that you can copy and paste them into Notepad and save them as a batch file (.bat). You can then run the batch file from the command prompt.
*************************************/

-- Query Window 1
USE AdventureWorks;
GO
BEGIN Transaction
  INSERT INTO Person.Contact
    (FirstName, LastName,PasswordHash, PasswordSalt)
	VALUES ('Old', 'McDonald', 'P@ssw0rd','P@$$S@lt')

/************************************/

-- Query Window 2
USE AdventureWorks;
GO
SELECT * 
FROM Person.Contact

/************************************/

SELECT *
FROM sys.dm_exec_requests
WHERE blocking_session_id <> 0

/************************************/

SELECT * FROM sys.dm_tran_locks

/************************************/

-- Query Window 3


/************************************/

Kill 54

/************************************/

SELECT * FROM sys.dm_tran_locks

/************************************/

SELECT * FROM sys.dm_exec_requests

/************************************/

DBCC SQLPERF ('sys.dm_os_wait_stats', CLEAR)

/************************************/

Sp_who 64

/************************************/

--Joe Query Window 1
USE AdventureWorks;
GO
BEGIN TRANSACTION
INSERT INTO Sales.CurrencyRate
   (CurrencyRateDate, FromCurrencyCode,ToCurrencyCode,
          AverageRate, EndOfDayRate)
VALUES (GetDate(), 'USD', 'CAD',1.585, 1.583)

/************************************/

--Sally Query Window 2
USE AdventureWorks;
GO
BEGIN TRANSACTION 
INSERT INTO Sales.Currency
   (CurrencyCode, Name)
VALUES ('VUC', 'Vulcan Cha')
--
UPDATE Sales.CurrencyRate
   SET EndOfDayRate = 1.584
   WHERE Year(ModifiedDate) = Year(GetDate()) AND
         Month(ModifiedDate) = Month(GetDate()) AND
		 Day(ModifiedDate) = Day(GetDate())

/************************************/

UPDATE Sales.Currency
   SET Name = 'Vulcan Sha'
   WHERE CurrencyCode = 'VUC'

/************************************/

--Deadlock 1 Query Window
USE AdventureWorks;
GO
BEGIN TRANSACTION
INSERT INTO Sales.CurrencyRate
   (CurrencyRateDate, FromCurrencyCode,ToCurrencyCode,
          AverageRate, EndOfDayRate)
VALUES (GetDate(), 'USD', 'CAD',1.585, 1.583)

/************************************/

--Deadlock 2
USE AdventureWorks;
GO
BEGIN TRANSACTION 
INSERT INTO Sales.Currency
   (CurrencyCode, Name)
VALUES ('VUC', 'Vulcan Cha')
--
UPDATE Sales.CurrencyRate
   SET EndOfDayRate = 1.584
   WHERE Year(ModifiedDate) = Year(GetDate()) AND
         Month(ModifiedDate) = Month(GetDate()) AND
		 Day(ModifiedDate) = Day(GetDate())

/************************************/

UPDATE Sales.Currency
   SET Name = 'Vulcan Sha'
   WHERE CurrencyCode = 'VUC'

/************************************/

USE AdventureWorks;
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRANSACTION;
-- Transaction work here
COMMIT TRANSACTION;