SQL Server 2008 - Modulo 10 - Curso 6232

--Aula 01 - Transactions and Locks

--===================================================================

--Autor                : Lineu Alves Lima Filho

--Data Criação   : 15/04/2010

--Descrição         : Módulo 08

--===================================================================

--Versão                             : 2.0

--===================================================================

--Versão                                             :

--Data Atualização          :

--===================================================================

-- =============================================

--                            Autocommit Transactions

-- =============================================

USE [master]

GO

IF EXISTS             (SELECT * FROM sys.databases

                                                WHERE [name] = 'DB6232_Locks')

DROP DATABASE DB6232_Locks

GO

CREATE DATABASE DB6232_Locks

GO

USE DB6232_Locks

GO

CREATE TABLE tabela01

(

                id                                           INT                                        PRIMARY KEY,

                info                       CHAR(3)

)

GO

--Inserir os 3 registros abaixo numa só vez

INSERT INTO tabela01 VALUES (1, 'aaa')

INSERT INTO tabela01 VALUES (2, 'bbb')

INSERT INTO tabela01 VALUSE (3, 'ccc') --Erro de sintaxe

GO

SELECT * FROM tabela01 -- Returns no rows.

-- =============================================

--                            Explicit Transactions

-- =============================================

BEGIN TRAN      -- Ou BEGIN TRANSACTION

SELECT @@TRANCOUNT

INSERT INTO tabela01 VALUES (1, 'aaa')

INSERT INTO tabela01 VALUES (2, 'bbb')

INSERT INTO tabela01 VALUES (3, 'ccc')

COMMIT                             -- Ou COMMIT TRANSACTION

ROLLBACK          -- Ou ROLLBACK TRANSACTION

SELECT * FROM tabela01

-- =============================================

--                            Implicit Transactions

-- =============================================

SET IMPLICIT_TRANSACTIONS ON

-- Do some work in implicit transaction mode.

-- BEGIN TRAN

SELECT @@TRANCOUNT

INSERT INTO tabela01 VALUES (5, 'eee')

SELECT @@TRANCOUNT

COMMIT

SELECT * FROM tabela01

-- =============================================

SET IMPLICIT_TRANSACTIONS OFF

-- Return to autocommit mode.

SELECT @@TRANCOUNT

INSERT INTO tabela01 VALUES (6, 'fff')

-- COMMIT

SELECT * FROM tabela01

-- =============================================

--                            Sava Transactions

-- =============================================

TRUNCATE TABLE tabela01

BEGIN TRANSACTION

SELECT @@TRANCOUNT

INSERT INTO tabela01 VALUES (1, 'aaa')

SAVE TRANSACTION a

INSERT INTO tabela01 VALUES (2, 'bbb')

SAVE TRANSACTION b

INSERT INTO tabela01 VALUES (3, 'ccc')

ROLLBACK TRANSACTION b

INSERT INTO tabela01 VALUES (4, 'ddd')

ROLLBACK TRANSACTION a

COMMIT TRANSACTION

SELECT * FROM tabela01

-- =============================================

--                            Transaction Isolation Levels

-- =============================================

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

-- =============================================

--                            Transaction Isolation Levels

-- =============================================

TRUNCATE TABLE tabela01

DBCC USEROPTIONS

BEGIN TRANSACTION

SELECT @@TRANCOUNT

INSERT INTO tabela01 VALUES (1, 'aaa')

ROLLBACK

------------------------------------------------------------------

------------------------------------------------------------------

-- Open another session to execute the scripts below

DBCC USEROPTIONS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

USE DB6232_Locks

SELECT * FROM tabela01             -- Podemos ver os dados

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT * FROM tabela01             -- NÃO podemos ver os dados

------------------------------------------------------------------

------------------------------------------------------------------

SELECT @@LOCK_TIMEOUT

SET LOCK_TIMEOUT 10000         -- 10 segundos

SET LOCK_TIMEOUT -1

-- =============================================

--                            Searching for Deadlocks

-- =============================================

-- Enabling the DEADLOCK trace GLOBALLY

DBCC TRACEON (1205, -1)

-- Viewing the trace status

DBCC TRACESTATUS

-- Disabling the DEADLOCK trace GLOBALLY

DBCC TRACEOFF (1205, -1)

------------------------------------------------------------------

------------------------------------------------------------------

-- In order to demonstrate this recipe, a deadlock will be simulated.

-- In a new query editor window, the following query is executed:

USE AdventureWorks

GO

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

WHILE 1=1

BEGIN

BEGIN TRAN

                UPDATE Purchasing.Vendor

                SET CreditRating = 1

                WHERE VendorID = 2

                UPDATE Purchasing.Vendor

                SET CreditRating = 2

                WHERE VendorID = 1

COMMIT TRAN

END

------------------------------------------------------------------

------------------------------------------------------------------

-- In a second query editor window, the following query is executed:

USE AdventureWorks

GO

SET NOCOUNT ON

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

WHILE 1=1

BEGIN

BEGIN TRAN

                UPDATE Purchasing.Vendor

                SET CreditRating = 2

                WHERE VendorID = 1

                UPDATE Purchasing.Vendor

                SET CreditRating = 1

                WHERE VendorID = 2

COMMIT TRAN

END

------------------------------------------------------------------

------------------------------------------------------------------

TRUNCATE TABLE tabela01

BEGIN TRAN

SELECT @@TRANCOUNT

INSERT INTO tabela01 VALUES (1, 'aaa')

SELECT  resource_type

                                ,request_mode

                               ,request_type

                               ,request_status

                               ,request_session_id

FROM sys.dm_tran_locks

ROLLBACK

--Aula 02 - Linked Server

-- ====================================================================================

-- Autor                               : Lineu Alves Lima Filho

-- Data Criação  : 21/04/2010

-- Descrição        : Linked Server

-- ====================================================================================

-- Versão                            : 1.0

-- ====================================================================================

-- Versão                                            :

-- Data Atualização         :

-- ====================================================================================

--Mudar o modo de autenticação da instância PADRÃO

--para o Mixed Mode

--Reiniciar a instância

--Criar o login "linkedsrv" com a senha "123" com permissão

--de db_owner no banco AdventureWorks2008 na instância PADRÃO

USE [master]

GO

CREATE LOGIN [linkedsrv] WITH PASSWORD='123',

DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF,

CHECK_POLICY=OFF

GO

USE [AdventureWorks2008]

GO

CREATE USER [linkedsrv] FOR LOGIN [linkedsrv]

GO

USE [AdventureWorks2008]

GO

EXEC sp_addrolemember 'db_owner', 'linkedsrv'

GO

-----------------------------------------------------------

--Executar as seguintes queries usando uma sessão da

--instância SQLINSTANCE2

-----------------------------------------------------------

--Cria o linked server para SQL Server na instância SQLINSTANCE2

EXEC master.dbo.sp_addlinkedserver

                @server = 'NY-SQL-01',

                @srvproduct='SQL Server'

--Adiciona o login "linkedsrv" com permissão para usar o linked server

EXEC master.dbo.sp_addlinkedsrvlogin

                @rmtsrvname='NY-SQL-01',

                @useself='False',

                @locallogin=NULL,

                @rmtuser='linkedsrv',

                @rmtpassword='123'

GO

--Verificando que o banco AdventureWorks2008 não existe na

--instância SQLINSTANCE2

SELECT TOP 10 * FROM sys.databases

--Buscando dados da tabela Person.PersonPhone da instância PADRÃO

SELECT TOP 10 * FROM [NY-SQL-01].[AdventureWorks2008].[Person].[PersonPhone]

--Sem premissão para acessar o banco AdventureWorks

SELECT TOP 10 * FROM [NY-SQL-01].[AdventureWorks].[Person].[AddressType]

-----------------------------------------------------------

--Executar as seguintes queries usando uma sessão da

--instância PADRÃO

-----------------------------------------------------------

--Cria o linked server para Access

EXEC master.dbo.sp_addlinkedserver

                @server = N'ACCESS',

                @srvproduct=N'OLE DB Provider for Jet',

                @provider=N'Microsoft.Jet.OLEDB.4.0',

                @datasrc=N'F:\DADOS\livros.mdb'

--Buscando dados no Access

SELECT TOP 10 * FROM [ACCESS]...[livros]

--Inserindo dados numa tabela do Aceess pelo SQL Server

INSERT INTO [ACCESS]...[livros] VALUES

(4, 'Excel 2008', 880, 'Juliana')

--Cria o linked server para Excel

EXEC master.dbo.sp_addlinkedserver

                @server = N'CLIENTES',

                @srvproduct=N'Jet 4.0',

                @provider=N'Microsoft.Jet.OLEDB.4.0',

                @datasrc=N'F:\DADOS\clientes.xls',

                @provstr=N'Excel 5.0'

SELECT TOP 10 * FROM Clientes...tab_clientes$

ORDER BY cod_cli DESC

--Inserindo dados numa planilha do Excel pelo SQL Server

INSERT INTO Clientes...tab_clientes$ VALUES

(51, 'Lara', 19)