Free Databases and Data Visualisations course for kids Week 5 update

Databases and Data Visualisations course for kids aged between 9 to 14 years week 5 update. Had a great time on Monday with 10 kids and 2 parents attending the SQL course. #sqlfamily #microsoftnz 

There was no class last week as I was away in Melbourne at the #powerplatformsummit.

So there was almost a 2 week gap so I revised all that they have learnt so far in the last 4 weeks by asking them a lot of questions.  Most of the questions some of the kids answered them right but the revision helped them to understand a bit more.

I was pleased to see most of the kids eagerly answering my questions. I felt that now all the kids are able to genuinely understand what I am trying to teach them.

This week they learnt some of the basic SQL functions like Min, Max, Count, AVG, SUM.  String functions like Left and Right.

In the next session I am planning to cover the importing of data from a csv and also will try and explain joins to them.

I am looking forward for the session on next Monday.

Free Databases and Data Visualisations course for kids Week 4 update

Databases and Data Visualisations course for kids aged between 9 to 14 years week 4 update. It was another full house on Monday with 12 kids and 4 parents attending the course. #sqlfamily #microsoftnz  

The first 45 minutes were spent in solving issues with questions like --
- 'I created a table but I cannot see it'
- 'Why is my insert statement not working ?'
- 'I have inserted a lot of duplicate records.  How do I delete them ?'
- 'I am getting a syntax error'

After this, when I did the recap of what happened last week, I was pleased to see most of the kids eagerly answering my questions. I felt that now all the kids are able to genuinely understand what I am trying to teach them.

The agenda for this week was to explain the update and delete statements.  The Like and In operators in the where clause.  Some of the basic SQL functions like Min, Max, Count, AVG, SUM.  String functions like Left, Right and Substring.

It is interesting to see how some of the kids think.  One of them asked what if we accidentally deleted all records ?  Is there an undo?

As usual I felt very happy to see the enthusiasm in these kids. I am looking forward for the session on next Monday.

Free Databases and Data Visualisations course for kids Week 3

Free Databases and Data Visualisations course for kids Week 3
Databases and Data Visualisations course for kids aged between 9 to 14 years week 3 update. It was a full house yesterday with 15 kids and 5 parents attending the course. #sqlfamily #microsoftnz

During my recap of what happened last week, most of the kids wanted to answer all of my questions.  For the homework that I have given, Imost of them completed the creation of the tables.  But everyone forgot to create the foreign keys.

There were only 2 challenges faced by the kids when they were doing their home work. First was when they had a space in the database or table name. Second was the table was not created in the new database they created.

The agenda for week 3 was to show them how to insert data into the tables and view the data.  I could just cover select statement and basic where clause and the time was up.

I feel very happy to see the enthusiasm in all of these kids. I am looking forward for the session on next Monday.

Below are few photos from the session.

Free ebooks from Microsoft

Here is a link to a huge collection of free ebooks posted by Eric Ligman from Microsoft.  Hope you will find these books useful in your career development.

What the password policy of sql server?

Yesterday I had a question in my mind as to what could be the sql server password policy. So I started hunting in the Help menu of the SSMS window. Here is how I found that.

• Click on Help from Sql Server Management Studio.


• Choose contents


• Then choose Security considerations for SQL Server


• Then choose the Password Policy as shown in the screenshot below.

Using Login Auditing in SQL SERVER by Brian Kelly

Today featured video in SQL Share was Brian Kelly's "Using Login Auditing in SQL SERVER". I watched this 3 min. video and learnt the following.

I know that we can view sql server error logs. I monitor them on a daily basis but I didnt know that the login audit requires a setup that is a bit similar to the normal windows event log set up. This can be set up when you right click on the sql server connection in the management studio and choose properties and then choose Security. There is n option to choose Failed logins only, Successful logins only and both failed and successful logins and of course the none option aswell.

These login auditing will not only appear in the sql server error logs but in the windows event logs as well.

The best part that we are getting as part of watching the sql share videos is testing the knowledge we have acquired immediately after the video finishes which I think is very innovative.



If you would like to watch the same video click on the link

What is Sargability?

I planned to attend Rob Farley's 24HOP session but could not make it. I didn't know what Sargability meant at the time and thought I will google it.

And here is what I have found out thanks to the internet.

Here is a blog post which gives the history of how the word came into existence back in 2005 where Brad McGhee explains that the sargability is a real word but it is used a lot in the SQL Server world.

So what exactly does Sargable mean?
SARG is a contraction for Search ARGument ABLE as mentioned in Rob Farley's article.

I am looking forward for the recording of this 24HOP session. Meanwhile you can have a look at the posting by Rob Farley in his blog where he gives links to the resources he has mentioned in his presentation.

Free SQL Tools as blogged by

Today I stumbled across this very useful post by Mladen Prajdić

Click here to read more

As he says in the end ...

Even if none of this stuff helps you at the moment it's good to know they exist.

Because DBA-ing is like a box of chocolates, you never know what you're going to get :)

What are plan guides?

Just a while ago I was watching a video on sqlshare Creating a Plan Guide for a Parameterized Statement By Andy Warren . Only then I heard about plan guides. I didn't know what plan guides were. Here is my find from googling


In SQL Server 2005, there is a new feature called Plan Guides that can help out in some cases where you discover poorly performing queries that you don't have direct control over. Essentially, a Plan Guide allows you to add or modify query hints to queries on the fly, just before they are executed. Click here for a detailed article on plan guides by Brad McGehee.

Tools for collecting SQL Server instances

Due to the proliferation of personal firewalls, inconsistent network library configurations, and multiple-instance support, SQL Server installations are becoming increasingly difficult to discover, assess, and maintain.

Peter Ward from WardyIT Solutions while presenting in the SQL Saturday event at Auckland last weekend has asked all of us the following question:

“Do you know how many instances are there in your network?”

Only two people in the entire room have raised their hands.

He mentioned about the following tools being available for collecting sql server instances in a network.

Sqlping from www.sqlsecurity.com

SQLPing 3.0 is a free tool available which performs both active and passive scans of your network in order to identify all of the SQL Server/MSDE installations in your enterprise.
SQLPing 3.0 is designed to remedy this problem by combining all known means of SQL Server/MSDE discovery into a single tool which can be used to ferret-out servers you never knew existed on your network so you can properly secure them. .NET Framework v2.0 Required.

You can find more information about SQLPING here

Microsoft assessment and planning toolkit (MAP Toolkit)

MAP Toolkit is a free tool from Microsoft that does a lot more than just scanning the network and identifying all instances of SQL Servers. Some of the features are outlined below.

• Secure, Agentless Discovery and Inventory
• Generate Automated, Network-wide Readiness Assessments
• Quickly Create Reports and Proposals
• Capitalise on Cross-Sell and Up-Sell Opportunities
• Shorten Your Presales Cycle, Increase Your Opportunities
• Accelerate Server and PC Migration Planning
• Leverage Virtualisation to Help Reduce Customers' Costs

For more information please click here

SQLIO
In the presentation Peter also briefly mentioned about SQLIO as a tool to measure SAN performance. I have googled for SQLIO and came up with the following.

SQLIO is a free utility from Microsoft that measures storage IO performance. The name "SQLIO" is horribly misleading, because it doesn't really have anything to do with SQL Server. There is a lot of information about SQLIO in the SQLServerpedia website

There may be lot of other tools out there. Please suggest me other tools if you can

Do your backups for SQL server still exist?

Now that you have confirgured your backups on SQLSERVER, the backups themselves seem to be working, but are the files still where you think they are? How do you know they have not been moved, or worse, deleted to free up some space on your server? And when would you find out that the files were no longer there? If you are looking for the file to do a restore, and cannot find it, then it is too late.

Here is an tip from MSsqltips that shows you a simple way to check through the backup history to find the last full backup for each database and also to check that the file still exists where it is supposed to exist. Read More .............

Installing SQL Server 2005 Reporting Services 32-bit on a 64-bit Windows Server system

We have a Windows 64-bit server and need to install the 32-bit version of SQL Server 2005 Reporting Services on this machine since there are other applications where we are using IIS on the 32-bit version. This article from MSSQLtips outlines the steps you need to take for this installation....

Read More

Delete all data from a database

I had a requirement today to delete all data from a sqlser ver database. This database has about 350 tables with a lot constraints. I manually started identifying the order of deletion absed on the constraints and realsied that this can't be done in a day. So I hit the good old google for help.

Here I found a very detailed explanation of how to easily achieve this by Susan Sales Harkins. Read More

Converting DTS to SSIS in SQL SERVER 2005

The easiest way to convert your SLQ SERVER 2000 DTS packages to SSIS is to use a tool called 'DTS xChange' that is available for download here

Here is link to the webcast by Brian Knight of Pragmatic works explaining in detail as to how to proceed with the DTS xChange tool.

One of the functions of a DBA is to keep an eye on free space within the database and database files. The auto grow feature is great as a last resort, but proactively managing the database files is a better approach.

Also, this information can be used to shrink data files as needed if there is a lot of free space in the files. In this tip from MSSQLTIPS they take a look at a few ways to find the current free space within a database, so you can better manage your database files.... Read More

Building a dashboard using SSRS

Here is a tip from MSSQLTips I found useful on how to create a dashboard using Sql Server Reporting Services.

Read More

Substring syntax in sql

Yesterday I was trying to get a part of a string from a field where the first comma occurs.

For example there is a field named 'Descriptor' which is a combination of "full name, Date of birth, unique number of the record" in that order. I wanted to get the full name using a select statement.

Here is the syntax I used.


select substring(descriptor,1,charindex(',', descriptor)-1) as [fullname]
from contacts

Getting started with Sql Server Analysis Services

Here is a tutorial from MSSQL Tips on SQL SERVER Analysis Services.


Read more

In most organizations today, business critical and confidential data resides in some type of database, increasingly, SQL Server. As a result, SQL Server database security and compliance have become critical issues that must be effectively addressed to ensure an organization’s overall security. The goal is simple - minimize business risk, data theft, and data losses through good security practices. This whitepaper gives an overview of 10 key areas you need to focus on in order to ensure SQL Server security, and avoid finding your company in the headlines.

Click here to download the whitepaper

Problems connecting to SSAS 2005 from Excel 2003

Today I was trying to connect to SSAS 2005 cube from Excel 2003 and I was not able to do so using the exisitng OLAP 8.0.

I had to download the folowing to resolve the problem.

1. Microsoft Core XML Services (MSXML) 6.0 SP1
2. Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider

I have followed the article on Microsoft downloads http://support.microsoft.com/kb/940167

and that solved my problem !