Jan Theme for Learn SQL Free – Programming / Control of flow elements of T-SQL

After a short amount of thought about what would a be a cool theme for January I decided that a series of posts on the Programming / Control of flow elements of T-SQL.

I will try and cover all of the below commands this month which cover almost all the programming elements of T-SQL.

CASE
DECLARE
BEGIN…END
RETURN
BREAK
THROW
CONTINUE
TRY…CATCH
GOTO label
WAITFOR
IF…ELSE
WHILE

and the dreaded but often very useful CURSOR

My plans for 2015

Hi to all my readers.

I must confess that my Blogging has suffered during the latter half of my wife’s pregnancy and the first five months of my newborn baby girl’s life.

I have had grand plans about Blogging about Powershell, C#, SQL Server and Python each and everyday but my own unrealistic expectations have come back to roost and I have not really done anything for several months. I have procrastinated on so many things like changing the WordPress theme, email sign up form…. that I have decided that I must now be more honest and committed to growing this Blog.

My plans for 2015 are

  • Blog at least once a week
  • Promote my Twitter and Facebook page
  • Convert all my posts into Youtube videos
  • Try and do a at least one themed month of Blogging. I am thinking of something like a month of Powershell one liners, 31 days of DMVs etc.

 

ABC The best trait of a SQL DBA

Have you ever wondered what the best trait all super SQL DBAs possess? Well I will tell what the best trait is and an easy acronym to remember it.

I had a conversation with one of my colleagues we talked about the different DBAs and System Admins in the company both past and present and he asked me what I had learned having met them all.

It was an easy answer and I replied my ‘ABC.’ He looked perplexed and then I told it him what it stood for.

ABC = Always Be Checking.

As a DBA I see myself as the protector of the data and liaison between users, developers and system admins to ensure that everyone is happy.

The only way I can do that is to follow the ABC.

Check backups

Check SQL jobs

Check database mirroring

Check SQL Logs

Check for unused indexes

Check SA password

Check index suggestions and liaise with developers to implement them and maximise their potential

Check with application managers that users are having a good experience connecting to the database

Etc

Along the DBA journey there are many problems which you will face and many of them can be fixed at source by following the ABC mantra.

If you don’t want to follow the ABC mantra then you will always find yourself reacting to situations rather than managing them.

Output results from PowerShell to CSV or XML

Have you wanted to output results from PowerShell to CSV or XML? Well I am going to show you how.

I have previously explained how to export results of PowerShell queries but did not include how to export the results to CSV or XML. The reason for that is that when you become familiar with PowerShell you will find out that PowerShell commands can return a lot more information than what they show on screen. I will show how to get that information in a future blog post but just be aware that there is a lot more information readily available and only the displayed columns are exported to text(TXT) files.

When exporting to CSV and XML the hidden columns are exported as well. I will give an example of the difference in the exports.

The results of Get-Process are displayed below.

1

If I run the below commands I would output the results of Get-Process to CSV and XML.

Get-Process | Export-CSV c:\PowerShell\ProcessCSV.csv

Get-Process | Export-CliXML c:\PowerShell\ProcessCliXML.xml

2

When you look in the text file you will see the same results as shown on screen.

3

However, the CSV and XML have all the columns available for the results of that command. I have shown an excerpt of the Get-Process results in CSV format below.

4

I would advise that you use TXT, CSV and XML files where appropriate and check out the results you could get from various commands.

Open files using PowerShell Get-Content

Have you ever wanted to open a file and view its contents using PowerShell? Well I am going to show you how.

I frequently output the results of PowerShell queries into files for later review but rarely used PowerShell to view the contents. This has been because I always had other programs readily available to view the contents easily so never thought to use it. The other day I was on a server where we didn’t have access to notepad and I wanted to look at a file I had generated earlier that day.

I used an old DOS command ‘type’ but saw my colleague who frequently worked on that server use a PowerShell command I was unfamiliar with. He was using Get-Content to look at some files so I used the help command to quickly familiarise myself with the command and once again I was smiling at the beauty and ease of PowerShell commands.

Get-Content C:\PowerShell\Process2.txt

1

Simple way to output results to a file using PowerShell

Have you ever wanted to send the output of a PowerShell command to a file? Well I am going to show you how.

When you run a PowerShell command like Get-Process, Get-Host…. It will return useful information but sometimes you want to save that information for future reference. You could cut and paste or take a screenshot but these are way more complicated than simply using the inbuilt functionality within PowerShell.

For example if you wanted to output the processes currently running you would use Get-Process. To output it you would add the below command

Get-Process  >  yourfilename.txt

Get-Process > C:\PowerShell\Process.txt

1

List active process running using PowerShell

Have you ever wanted to quickly find the processes running using Powershell? Wanted an alternate to Task Manager? Well I am going to show you how.

Whenever I get a call from someone about a performance issue on any of our SQL Servers I always usually have a quick glance of Task Manager so that I can help identify what maybe causing the problem. However, the problem can be that on a server which is low on resources it may take a long time to open or the opening of task manager may add to the problems or it may not open at all.

This has led me to frequently opening PowerShell and using it to get the information which I need. The script is quite simple and is below.

Get-Process

1

I have found the advantage of using the script is that it gives me a snapshot whilst in task manager it is always a moving target and if I run it several times I can just scroll up and down to find a pattern which would help diagnose the problem.

Hello World in C#

Have you ever wanted to reach the first milestone in programming by writing a Hello World application in C#? Well I am going to show you the script to produce the program.

I was asked by a member of our helpdesk team how he could become a programmer. I answered that he should start with the Hello World program in C# which was the first milestone all programmers had to accomplish before becoming programming gurus.

After he downloaded and installed Visual Studio 2012 Express I wrote the below script to show him how easy it was start programming and that he should just practice practice practice.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

 

namespace HelloWorld

{

class Program

{

static void Main(string[] args)

{

Console.WriteLine(“Hello World”);

Console.ReadLine();

}

}

}

 

 

 

Get the Operating System Properties via PowerShell

Have you ever wanted to get all the operating system properties of a computer? Well I am going to show you a short PowerShell script which can get loads of information about the operating system.

One of my colleagues was googling around for information about how to find the windows directory, number of users, last boot up time and many other properties of various computers. I told him that I had a short script which had that information and probably a whole lot more information which he might find useful.

$hostname = “parvinder-pc”

Get-WmiObject -class win32_operatingsystem -computer $hostname | select-object *

1

Change a database state in SQL

Have you ever wanted to change a databases state from read only to read write or vice versa? Well I am going to show you how using some easy to remember T-SQL which you will be using repeatedly over time.

 

We received a database from a client the other day and noticed one of my System Admin was struggling with the restore. He thought he was doing something wrong with the restore and I said he hadn’t done anything wrong and that the database was likely put into that state prior to being backed up and sent to us.

 

I showed him the below T-SQL which enabled him to change the state to read write and then change it to read only if required.

 

Make Database Read Only

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_ONLY

GO

 

Make Database Read/Write

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE WITH NO_WAIT

GO

 

Or

 

USE [master]

GO

ALTER DATABASE [YourDBName] SET READ_WRITE

GO