Monday, November 5, 2018

SQL & SSMS Tricks

Just posting some tricks I've used or just learned

http://www.e-squillace.com/ssms-tricks-shortcuts/

SSMS Tricks or Options I like
  • ALT + SHIFT =multi-line select > cool
  • Options > Text Editor > All Languages > Scroll Bars > Use map mode for vertical scroll bar > cool
  • Splitting query windows
  • Options > Text Editor > All Languages > check Line Numbers
  • Dark theme/Font
  • Options > keyboard > Keyboard: search command Query.ChangeConnection , press shortcut keys ALT + G, and Assign
  • Options > keyboard > Query shortcuts; Set to anything you want. In future, highlight a table name (with or without schema) and CTRL+3. these are my settings
    • ALT + F1 = sp_help
    • CTRL + F1 = sp_helptext
    • CTRL + 1 sp_who
    • CTRL + 2 sp_lock
    • CTRL + 3 SELECT TOP 100 * FROM 
    • CTRL + 4 SELECT COUNT(*) FROM
    • CTRL + 5 SELECT * FROM
    • CTRL + 6
    • CTRL + 7 sp_helpdb
    • CTRL + 8 USE <database name>
    • CTRL + 9 sp_helptext
    • CTRL + 0 sp_whoisactive
  • CTRL + SHIFT + R to refresh IntelliSense
  • ALT + SHIFT + T to move down 1 line
  • CTRL + SHIFT + ENTER to create new empty line below
  • Options > Query Execution > Advanced - uncheck SET ARITHABORT
  • Options > Query Results - Play the Windows default beep
  • Change Environment (Object Explorer font) to go bigger
  • Script out permissions in SSMS
    • In SSMS (Management Studio) you need to go to Tools-->Options  then look for "SQL Server Object Explorer" and expand it and go to "Scripting".Then look for Object scripting options and change "Script Permissions" to "True".
Starting SSMS with a specific connection and script file - SQLServerCentral

Configuring SSMS for presenting - Paul S. Randal


SSMS Shortcut Keys
Bookmark - CTRL K, K to bookmark, N for Next, P for Previous
ALT+F8 to open Object Explorer
SHIFT+F1 on a keyword/object (or DMV) to open up in browser

SSMS Add-Ons (FREE)


SQL Code tricks

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
GO
--instead of (NOLOCK) on every table
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO

-- /* First line. Removing the two dashes activates the block comment
SELECT 
patientname,
Patientid,
Language
FROM whatevertable
Where name = 'Whatever'
-- */ Last line. When the block comment is on, this terminates it


Resources

http://www.sqlservercentral.com/articles/Management+Studio+(SSMS)/160267/ 

Thursday, October 18, 2018

DBA Salary


Sharing interesting info as I was reading Brent's blog

- at least I was doing pretty OK in Canada, relatively
- DBA in Canada do earn (much less) than USA

What Should We Change About the Data Professional Salary Survey for 2019?
The 2018 Data Professionals Salary Survey Results - Brent Ozar Unlimited®
Analyzing Salary Data with Power BI and R – Part 1 |





Enable Telnet in Windows 10

Telnet is off by default in Windows 10

How to enable it?
Open Command prompt as Admin
Run this code - dism /online /Enable-Feature /FeatureName:TelnetClient
Voila!

Do it via GUI
https://social.technet.microsoft.com/wiki/contents/articles/38433.windows-10-enabling-telnet-client.aspx

Thursday, September 27, 2018

SSMS XEvent Profiler to read Extended Events

I'm learning the new SQL Profiler, found in SSMS 17.3+, called XEvent Profiler which uses Extended Events


Resources
Overview of XEvent Profiler in SQL Server Management Studio

Inside the XEvent Profiler - SQL Hammer | SQL Hammer

Wednesday, September 5, 2018

List All Groups, DisplayName, ServerName in CMS (Central Management Server)

If you use CMS
If you have a large group of servers and can't memorize all the names/IPs
Use below code to show them all (and copy to Excel to format)

Connect to your CMS directly (not as a group) and run below code (using Recursion CTE)

WITH MyCTE
AS (
--root, anchor
SELECT server_group_id, name, G.parent_id, ParentName = CAST('' AS sysname), 1 AS Grouplevel
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
WHERE is_system_object <>1 AND parent_id = 1
UNION ALL
--child
SELECT G.server_group_id, G.name, G.parent_id, parent.name, parent.Grouplevel+1
FROM msdb.dbo.sysmanagement_shared_server_groups_internal G
INNER JOIN MyCTE AS parent ON G.parent_id = parent.server_group_id
)
SELECT
G.grouplevel, Parent = CASE G.ParentName WHEN '' THEN G.name ELSE G.ParentName END, Child = CASE G.ParentName WHEN '' THEN '' ELSE G.name END
,G.name, G.ParentName
,svr.name AS 'Display Name',svr.server_name AS 'Server Name'
FROM MyCTE  G
LEFT OUTER JOIN msdb.dbo.sysmanagement_shared_registered_servers_internal svr ON G.server_group_id = svr.server_group_id
WHERE 1 = 1
ORDER BY
GroupLevel ASC, Parent, Child, [Display name]

Wednesday, August 29, 2018

Testing SQL connection using file.udl trick

Besides the usual
>telnet SQLname 1433

One can create a file and open it (note: show the extention in your File Explorer settings)
TestDatabase.udl 


Source
https://dougrathbone.com/blog/2013/11/18/testing-connectivity-to-microsoft-sql-server-without-any-tools-installed

Friday, August 24, 2018

Set Default template for SSMS New Query button by editing SQLFile.sql




  1. Go to SQL install path, for me it's "C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\ManagementStudio\SqlWorkbenchProjectItems\Sql"
  2. Edit the SQLFile.sql in Admin mode
  3. Type in whatever you want for default
  4. Save and try in SSMS [New Query] button



Source
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b56b9ed6-1786-4ad6-87c9-7e4e81068672/how-to-set-the-default-query-for-the-ssms-new-query-button?forum=transactsql

Friday, May 18, 2018

SQL Cluster Registry Checkpoint

Error
[sqsrvres] GetRegKeyAccessMask: Could not get registry access mask for registry key Software\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication (status 2)).



Fix - Populate the registry to the other node

https://www.mssqltips.com/sqlservertip/3440/registry-check-pointing-in-a-windows-cluster-to-bring-sql-server-online/

https://sqlserver-help.com/2015/04/17/information-checkpoint-in-sql-server-cluster-resources/

> cluster res /checkpoints

Find your resource name to replace below
Run in cmd (Admin)

cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Cluster" 
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\MSSQLServer" 
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Replication" 
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\Providers" 
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerSCP" 
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\CPE" 
cluster res "SQL__YKF" /addcheck: "SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.HA5\SQLServerAgent"


Tuesday, April 17, 2018

2008 Cluster install fails on newer Windows 2012/2016

Tried to install SQL 2008 R2 on Windows 2016 = fails, cluster verification fails
SQL 2014 was a-okay

PowerShell (Admin mode)
Install-WindowsFeature -Name RSAT-Clustering-AutomationServer

Error to install SQL Server 2008 on Windows 2012 – Marcelo's Spaces
https://marcelodba.wordpress.com/2014/08/17/error-to-install-sql-server-2008-on-windows-2012/

https://manjusullad.wordpress.com/2015/11/26/unable-to-install-sql-2008-on-windows-server-2012-failover-cluster/