I administer SQL Servers that have several log shipping jobs, enough that manually disabling & enabling them (e.g. for maintenance windows) is tedious & time consuming. Instead, I use the following script, setting the variable @enabled to 0 or 1 to disable or enable jobs. The list inserted into #Jobs can be amended to disable […]

As part of a data warehouse project that I’m working on, I need to be able to grant or deny access to users. I do this by setting permissions at different levels on the SQL Server (database, schema & object). I normally don’t have control over what analysis or visualisation apps are used to access […]

Aim: In my last blog post, I recorded a sample query for processing JSON in SQL Server so that I can refer back to it & tailor it to my needs when I need it in future. In this post, I’m going to repeat that exercise for XML. I’ve downloaded a sample of XML from […]

Aim: Very occasionally I have to process JSON in SQL Server, usually as part of a data warehouse task. Because I don’t do this often, I find I have to rebuild my knowledge of the relevant statement every time. So I’ve decided to record a sample T-SQL query that I can refer back to & […]

In SQL Server 2016, DROP IF EXISTS was introduced to T-SQL for certain objects. Below are the ones that I use most frequently, alongside the pre-SQL Server 2016 version for dropping the objects after checking that they exist. Database: — SQL2016: DROP DATABASE IF EXISTS testDatabase; GO — Pre-SQL2016: IF EXISTS (SELECT * FROM sys.databases […]

As part of a recent SQL Server migration, I had a task to update an ODBC connection on a legacy application server running Windows Server 2012 R2. Usually, the relevant connection can be found and re-configured in the System DSN (Data Source Name) list in the ODBC Data Source Administrator, e.g: However for some reason […]

The following is a quick & easy way to generate some random data in a temp table. It’s not very complex but it can be useful for quick testing purposes. Notes: NEWID creates a UniqueIdentifier datatype with a length of 36 characters (including hyphens). GO x (where x is an integer) will execute the batch […]

Kevin Kline

Career and Technical Advice for the IT Professional

TroubleshootingSQL

Explaining the bits and bytes of SQL Server and Azure

SQL Authority with Pinal Dave

SQL Server Performance Tuning Expert

Powershellshocked

A blog about PowerShell and general Windows sysadmin stuff

Simon Learning SQL Server

I'm trying to become "better" at SQL Server and data - here's how I'm doing it!