CopyPaste.Net: June 2015

Ctrl+R doesn't work in SQL Server 2012 SSMS

Problem: Ctrl+R doesn't work in SQL Server 2012 SSMS!!!

When I started work with SSMS of SQL Server 32012, and tried to hide/display the result panel, using Ctrl+R – it doesn't work for me, and I’v got this error in the bottom of the SSMS:

(Ctrl+R) was pressed. Waiting for second key of chord.

Solution:

Open from the menu: Toolsàoptions, choose KeyboardàKeyboard.

There, choose from the list: “window.ShowResultPane” event, and assign the Ctrl+R to “Use new shortcut in: SQL Query Editor”.

(Don’t forget to press the “Assign” button…)

Script fpr Role creation including permissions

-- set here the role name:

declare @RoleName varchar(50) = 'ROLE_NAME'

declare @RoleScript varchar(max)

SELECT @RoleScript = 'CREATE ROLE ' + @RoleName + char(13)

SELECT @RoleScript = @RoleScript + 'GRANT ' + prm.permission_name + ' ON ' + OBJECT_NAME(major_id) + ' TO ' + rol.name + char(13) COLLATE Latin1_General_CI_AS

FROM sys.database_permissions prm

JOIN sys.database_principals rol ON prm.grantee_principal_id = rol.principal_id

WHERE rol.name = @RoleName

PRINT @RoleScript

Transaction was deadlocked on lock resources with another process and has been chosen as the deadlock victim

Error message:

Transaction (Process ID 64) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

But if I don’t want this process to be the deadlock victim?

Solution:

SET DEADLOCK_PRIORITY HIGH

see here:

http://copypastenet.blogspot.co.il/2014/01/set-deadlockpriority.html

Drop few columns in one statement

ALTER TABLE dbo.MyTable DROP COLUMN ColumnA, ColumnB, ColumnC

Pivot On Multiple Columns

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

-- temp table for the example

CREATE TABLE tmpResults

( CustomerType nvarchar(50),

SomeTypeLetter nvarchar(50),

NumOfCustomers int

)

INSERT INTO tmpResults (CustomerType, SomeTypeLetter, NumOfCustomers)

VALUES ('CustomerType1', 'SomeTypeA', 33),

('CustomerType1', 'SomeTypeB', 12),

('CustomerType1', 'SomeTypeC', 61),

('CustomerType2', 'SomeTypeA', 11),

('CustomerType2', 'SomeTypeB', 25),

('CustomerType2', 'SomeTypeC', 21),

('CustomerType3', 'SomeTypeA', 17),

('CustomerType3', 'SomeTypeB', 38),

('CustomerType3', 'SomeTypeC', 9),

('CustomerType4', 'SomeTypeA', 5),

('CustomerType4', 'SomeTypeB', 91),

('CustomerType4', 'SomeTypeC', 12)

--SELECT * FROM tmpResults

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

--Pivot On Multiple Columns:

SELECT [CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],

[CustomerType2-SomeTypeA], [CustomerType2-SomeTypeB], [CustomerType2-SomeTypeC],

[CustomerType3-SomeTypeA], [CustomerType3-SomeTypeB], [CustomerType3-SomeTypeC],

[CustomerType4-SomeTypeA], [CustomerType4-SomeTypeB], [CustomerType4-SomeTypeC]

FROM

( SELECT isnull(p.NumOfCustomers,0) as NumOfCustomers,

(p.CustomerType) + '-' + (p.SomeTypeLetter) as [desc]

FROM tmpResults p

) [base]

PIVOT

( SUM(NumOfCustomers)

FOR [desc] IN

(

[CustomerType1-SomeTypeA], [CustomerType1-SomeTypeB], [CustomerType1-SomeTypeC],