front-page Archives - Page 5 of 6

My route to achieving MCSE Data Platform

28th January 2016 By John McCormack 2 Comments

This is my first non technical blog. Rather, it is about career and personal development.

Why did I want to achieve MCSE Data Platform?

Well, at first I didn’t. It seemed like a million miles away from where I started. My initial goal was just to pass the 70-462 exam (Administering Microsoft SQL Server 2012 Databases) as my boss had suggested it would be a good thing to learn. This was the first I had heard about Microsoft certifications. From there, with each subsequent exam pass, I just extended my goal to MCSA SQL Server 2012 and then MCSE Data Platform.

Timeline

I had set the target of passing 70-462 by the end of 2013 which would be just over 1 year as a DBA. The great thing about the certification is you can do the 70-461, 70-462 and 70-463 in any order. Since most of my limited knowledge was about Database Administration and the subject I needed to learn most about initially was also Database Administration, this was a no brainer.

[Read more…]

The EXCEPT operator in T-SQL

19th January 2016 By John McCormack Leave a Comment

The except operator in t-sql

When I first read about the except operator in t-sql and saw how simple it was to use, it knew I would be using it a lot. And I have.

I first came across it when studying for the 70-461 exam (Querying Microsoft SQL Server 2012). I keep trying to tell my colleagues about it but it seems they already know. So the only logical thing to do was write a blog post about it.

What does it do?

The EXCEPT OPERATOR compares two sets of data and performs set difference. In other words, it returns all of the rows from query 1 EXCEPT when an identical row is returned from query 2. This makes EXCEPT a quick and easy way to compare two sets of data without using 3^rd party tools or more complicated JOIN syntax.

What is the syntax?

[Read more…]

How to write a PIVOT query in T-SQL

12th January 2016 By John McCormack Leave a Comment

In this post, I explain how to write a simple PIVOT query in T-SQL and why we might want to use the PIVOT operator. The Oxford English Dictionary definition of PIVOT is

The central point, pin, or shaft on which a mechanism turns or oscillates.

So what we are doing here is TURNING THE DATA AROUND (PIVOTING) for visual purposes. The object of the exercise is to return 3 wide rows of data, instead of the 36 unique rows in the table.

Create the table and data which we will use in out PIVOT query

Lets create a database table which records the size of different tables within a database over time.

CREATE TABLE TableSize
(
TableSizeId int identity (1,1) Primary Key,
DatabaseName sysname,
Name sysname,
DataKB INT,
Collection_Date Date
)

[Read more…]

Style over substance

6th January 2016 By John McCormack 2 Comments

When it comes to managing emails, I can easily receive about 600 a day. This is probably far too many (although that’s a subject for another day). The challenge with receiving so many emails, especially so many system generated errors and warnings is that they can fail to stand out. Important information can be missed. A lack of formatting can also make it difficult to understand what the problem is, if any.

Here is an example of a boring system generated message.

To get around this, I always try to use some pretty HTML in my output just to help with the appearance and to make the message easier to read. I don’t go overboard, just a header tag, something like <h4> is usually enough (unless you want it really big) and of course I like to include my output in a table, usually with a splash of colour in the table header.

[Read more…]

An alternative to sp_msforeachdb

23rd December 2015 By John McCormack Leave a Comment

It’s well known that the sp_msforeachdb stored procedure is undocumented however it can be handy. The trouble with the proc is that it is unreliable. What’s more, it could be dropped from future SQL Server releases causing all your automated scripts to fail.

This little script shows you a more reliable solution and saves you from relying on an undocumented procedure.


USE Master
SET NOCOUNT ON
IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp

SELECT name,0 as completed
INTO #tmp
FROM sys.databases
WHERE name NOT IN('tempdb','Training2012') -- A list of DBs you don't wish to include

DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)

WHILE EXISTS(SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN

SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname

END

Let’s break up what the script actually does:

DROP temp table if it already exists
1. IF OBJECT_ID('tempdb..#tmp') IS NOT NULL DROP TABLE #tmp
Select into a temporary table with two columns. (Name and Completed). We set completed=0 for each row created.
1. SELECT name,0 as completed INTO #tmp FROM sys.databases
The WHERE clause is optional
1. WHERE name NOT IN('tempdb','Training2012')
Create a @dbname variable (for each DB name) and a @cmd variable (for building up the T-SQL command).
1. ```
DECLARE @dbname sysname
DECLARE @cmd NVARCHAR(4000)
```

The LOOP (Create and execute a new statement for each DB name and exit the loop once all DBs have been handled).

WHILE EXISTS (SELECT 1 FROM #tmp WHERE completed = 0)
BEGIN
SET @dbname = (SELECT TOP 1 NAME FROM #tmp WHERE completed = 0) -- You can ORDER BY name if you care about the order
SET @cmd = 'BACKUP DATABASE '+@dbname+' TO DISK = ''D:\Backup\'+@dbname+'.Bak'''
EXEC sp_executesql @cmd
UPDATE #tmp SET completed = 1 WHERE name = @dbname
END

This approach is simple and reliable and it avoids the use of undocumented stored procedures and cursors. The example above demonstrates a native backup but this command can be anything you like.