Oct 26, 2009

Here is the code for yesterday's Scenario:
create table #Department
DepartmentID int not null primary key clustered,
DepartmentName varchar(10) not null

create table #Employee
EmployeeID int not null identity primary key clustered,
DepartmentID int,
EmployeeName varchar(100)

insert #Department values(1, 'Sales'), (2, 'IT'), (3, 'Finance')
insert #Employee values (1, 'Sales Person1'), (1, 'Sales Person 2'), (2, 'Developer 1'), (2, 'Developer 2'),(2, 'DBA')

Oct 25, 2009

How to "aggregate" on strings

If you need to get the sum of salary by department we may be able to use Sum and group by clauses in a single select statement to get it done. But if you have a situation where you need to give a comma separated string of employees for each department, how will you handle it?
Consider these tables:

Department Table

Employee Table
Sales Person 1
Sales Person 2
Developer 1
Developer 2

Now you want the results like this:
Sales Person 1, Sales Person 2
Developer 1, Developer 2, DBA

Oct 24, 2009

Catching row count and error number

After some days I am writing this blog.
I had a couple of training programs (total of 5.5 days) and then I need to travel to US (on official visit!) so I couldn't update the blog.
So here is the update.

If you need to track error number and row count of a statement how will you handle it?
Both of these are captured using @@ERROR and @@ROWCOUNT system variables.
But there is a catch:
They reflect the value of the immediate SQL statement irrespective of whether it is a DML statement (INSERT, UPDATE, DELETE & SELECT statements) or just a control statement. (Examples are IF, SET, WHILE) That means if you add SET @Err_Variable = @@ERROR after a DML statement, you will be able to catch the error number, but it will take the statement SET @Row_Variable = @@ROWCOUNT to return 1 always
How to resolve it?

Oct 11, 2009

For those use SQL logins

Today,  I came to read about this blog.  There seems to be a security issue if you are using SQL logins, but Microsoft says it is not an issue to worry about it.  According to this post, Microsoft will not issue a security patch to resolve this problem. 

Are you worried about this?  You can read this article where it shows how things can go wrong and ways to prevent them.

Is SQL Server's latest security hole a real threat?test-security-hole-real-threat-801

Oct 10, 2009

The problem that prevented SQL Server from responsing

I couldn’t write for some time due to the heavy schedule I have. I was asked my Microsoft to do a customized training to one of their clients and it was a 4.5 day training. Preparing the materials took most of my time.

During this time I also went through some troubleshooting processes. Here is one of that:

A particular server failed to respond. Some DBAs tried to restart but the service could not be re-started. Agent was trying to stop before SQL Server stops and that was on waiting. At this moment I got a chance to work with them in resolving the problems. I tried to check on SQL Server error log. I couldn’t even connect. I checked with task manager where it showed that SQL Server was using only a few MB of memory (less than 1 GB) but CPU usage was 70% - 80%

Oct 6, 2009

Gift from Quest

Yesterday, When I came from home I had a pleasant surprise.
Exactly one month ago, I wrote a post on free online training on SQL Server which was hosted by Quest. As I participated and filled the survey, Quest decided to honer me by thanking for my participation.

Of course I learned from the participation, But Quest surprised me with added stuff. Even though I participated only on two sessions, I received a Cd with all the sessions they had on that day. In addition to that I got two banners one on SQL server dynamic management views and the other on catalog views. To add, I also got a T-shirt on my size.
Of course the packet included some marketing stuff as well, but it gave me some advantage. As it was categorized as marketing material it bypassed the customs.

I went though the CD, it had the presentations and some additional documents. REALLY COOL stuff.

In summary, I gained by participating (the knowledge), I am gaining by participating (the goodies) and I will be gaining by participating. (I filled the survey so that future training programs will be more beneficial.)

Are you guys thinking of participating at the next training program?

Oct 4, 2009

Backup Process

Recently I had a question: What will happen to the transactions executed while a backup job is in progress? This question prompted me to learn more about backup process. Additionally I was asked to give training to a set of administrators so I wanted to be prepared with the answer before they ask the question.

There are a lot of articles in many sites about backup and restore and this article summarizes what I learned from those articles.