SQLAuthority News – Book Review – Murach’s SQL Server 2008 for Developers

Murach’s SQL Server 2008 for Developers (Murach: Training & Reference) (Paperback)
by Bryan Syverson, Joel Murach

Link to Amazon

Short Summary: Murach’s SQL Server 2008 for developers is an ideal book for all developers, and particularly, it is an excellent book for training and reference. If you are new to SQL, no problem! This book is the best reading material to start with.

Long Summary:
SQL Server has emerged as the leading database and nowadays there are a number of books available on this subject. However, it is important to select the right book to imbibe proper, thorough understanding. Murach’s SQL Server 2008 for Developers is an upgrade from an earlier version that was written for SQL Server 2005. As the title clearly suggests, this book deals with SQL Server skills that are very useful for application developers. It primarily focuses on 2008 edition of Microsoft’s SQL Server and introduces you to the new features added to SQL Server 2008. This book is a great training resource for developers who are just starting out using Microsoft SQL Server. But this book is equally useful for experienced developers as it provides comprehensive information on SQL, which even they are not even aware of.

In sharp contrast to the usual database or SQL Server books, the first 8 chapters of this book provide information on how to use the Select, Insert, Update, and Delete statements with the Management Studio to work with an existing database. This information develops good understanding of learners regarding how a database works. In section3, the three chapters illustrate how to design and implement a new database using either SQL statements or the Management Studio. Further, in section 4 you can learn how to use advanced features such as views, stored procedures, functions, triggers, cursors, transaction, locking, database security, XML integration, and the new FILESTREAM feature for handling BLOB data. Finally, section 5 contains three chapters on how to use SQL Server’s CLR integration feature to create database objection C# or Visual Basic code.

This latest edition intends to make a developer’s job easier by appropriately covering 2008 features, helping them reach a higher level of competence and professionalism. The FILESTREAM storage feature is useful for working more proficiently with binary large objects (BLOBs) such as image, sound, and video files. The MERGE statement allows using a single statement to make multiple updates and insertions. The date, time, datetime2, and datetimeoffset data types provide better flexibility and accuracy for working with dates and times. The user-defined table type allows passing a table as a parameter to a stored procedure or a function. Also, the SPARSE attribute helps in working with NULL values more efficiently, and filtered indexes allows retrieving data more rapidly. Moreover, the code given in this book has been meticulously updated and tested against SQL Server 2008.

This book is an excellent technical reference for SQL Server 2008 and is characterized by a great format, making it thoroughly user friendly. It has Murach’s unique paired-pages layout style where each subject is given two pages of description. The left hand page is a technical description regarding what a particular function does, while the right hand page is an illustration of that feature being used; it shows the critical details, using syntax, code, screen shots, and how-to notes. This way, tutorial and reference formats have been presented in a single book, which makes it very convenient for both beginners and developers to focus on the information they need.

One drawback about this book is that it does not have answers to exercise questions. However, if you can get Instructors CD along with this book, you will find all the answers and other vital information. If you are new to SQL Server 2008, you must purchase this book.

Ratting: 5 Starts

Summary:
This book will not only introduce you to the new features but will also serve as an excellent reference guide. This book will become an integral and prized part of your collection!

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/08/sqlauthority-news-book-review-murachs-sql-server-2008-for-developers/

SQLAuthority News – Authors Visit – DotNet Buzz Delhi TechEd Delhi on July 11, 2009

DotNet Buzz Delhi is organizing TechEd Delhi on July 11, 2009. Not just this, they are launching an official PASS Chapter in Delhi. The Agenda of the event is here and if you are around Delhi do not miss the opportunity to be a part of this upcoming great event. If you are keen to know what this event holds in store for you then read about TechEd in Ahmedabad, which saw a huge number of attendees and was a grand success. Jacob Sebastian and Pinal Dave had presented two solid SQL Sessions and created lots of buzz about Microsoft.

I will be at TechEd and will present a session on ‘SQL Server Best Practices’. This session will mainly focus on SQL Server best practices that are not commonly known. There is a huge gamut of information which can drastically enhance the performance of a system, but these are not widely known to users. I will share with you all those brilliant tips through demo to help to develop clear idea.

Jacob Sebastian, my friend and a SQL Server MVP, will also be there to support this event. He will present a session on ‘Logical Query Execution Flow’ where he will discuss how a given T-SQL Query is processed and executed. A comprehensive understanding of the query execution flow will help you solve several common problems and will help you answer a number of baffling questions that you often come across.

Again, those of you who have requested for SQL Server Cheat Sheet, I will have one copy for all of you who will attend this event.

Register for this grand event by clicking here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/07/sqlauthority-news-authors-visit-dotnet-buzz-delhi-teched-delhi-on-july-11-2009/

SQL SERVER – Languages for BI – MDX, DMX, XMLA

Today, we have a very basic thing to go over. Few days back, I was discussing with one of my friends regarding BI. He told me that he knows that BI stands for Business Intelligence but he would like to know what languages BI uses to achieve the goal. The reason I found this question very interesting was because I was asked the same question two weeks back at TechEd on Road Ahmedabad. I had promised one of the attendees that I will reply to his question soon. This question, which my friend asked recently, reminded me of the same. Let us go over the languages of BI very quickly. Again, these are just definitions and there is much more to learn. Moreover, to master each language it may take years.

MDX – Multidimensional Expressions. This language is used for retrieving data from SSAS cubes. It looks very similar to T-SQL, but it is very different in the areas of conceptualization and implementations.

DMX – Data Mining Extensions. This is again used for SSAS but rather than cubes it is used for data mining structures. This language is more complicated than MDX. Microsoft has provided lots of wizards in its BI tools, which further reduced experts for learning this language which deals with data mining structures.

XMLA – XML for Analysis. This is mainly used for SSAS administrative tasks. It is quite commonly used in administration tasks such as backup or restore database, copy and move database or learning meta data information. Again, MS BI tools provide lots of wizards for the same.

After writing the above definitions, I feel my information is incomplete as these are just very basic information and do not provide much value. I will cover interesting aspects of these languages in near future. In SQL Server Management Studio, you can write the queries in the desired languages using top menu bar.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/06/sql-server-languages-for-bi-mdx-dmx-xmla/

SQLAuthority News – FIX : Error : HP OfficeJet Scanning and Printing Gray or Pink Shades

Unlike my usual articles today’s article is not at all related to SQL Server but something drove me to include it on my blog. This issue snatched away my precious few hours. It took me over 2 hours to resolve it yesterday, which barred me from doing research on SQL Server. I am sure many people must have faced this issue and the sad part is no solution has been proposed so far.

Let us understand the problem first. I got a brand new printer HP Officejet J4580 All-in-One printer. Support Engineer came along to install it. Fax, Printing, Photocopy – all the functions just worked fine. However, when we tried to scan anything the scanned copy had gray or pink shade on it. No matter what I did, the scanned copy still contained gray/pink shade. The support engineer worked for almost an hour on it but had no clue how to resolve it. We called up HP Support online but to my disappointment they replied with uncooperative attitude- we have never faced this issue so you should never face it. I contact several global HP Support and they suggested re-shipping the complete printer to them so that they can look at it.

The most annoying part was that I kept on telling them that Photocopy worked fine, and only scanning was the issue; it seemed to be a software issue. It was very strange that no one from HP support knew how to fix it. Anyway, I decided to use every search engine available on planet to look for the solution for the same. To my surprise, I was not the only one who was facing this issue; this is quite common but there is no real answer to this problem. Most of the time annoyed users keep on changing computer, ink cartridges or ultimately printer, but no one really talks about its solution.

I was determined to solve this software problem. After attempting nearly every possible setting available on software, I finally found the solution. Please pay attention to the checkbox in the image below. Once it is checked, the saved image will appear as follows:

After the setting was changed, the image came out neatly, without any pink or gray shade.

Hopefully, this article will save some time of those who are trying to make this work and who are not able to find time to read SQL Server due to this issue.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/05/sqlauthority-news-fix-error-hp-officejet-scanning-and-printing-gray-or-pink-shades/

SQL SERVER – Disk Partition Alignment Best Practices

Disk partition alignment is a powerful tool for improving SQL Server performance. Configuring optimal disk performance is often viewed as much art as science. A best practice that is essential yet often overlooked is disk partition alignment. Windows Server 2008 attempts to align new partitions out-of-the-box, yet disk partition alignment remains a relevant technology for partitions created on prior versions of Windows. This paper documents performance for aligned and nonaligned storage and why nonaligned partitions can negatively impact I/O performance; it explains disk partition alignment for storage configured on Windows Server 2003, including analysis, diagnosis, and remediation; and it describes how Windows Server 2008 attempts to remedy challenges related to partition alignment for new partitions yet does not correct the configuration of preexisting partitions. The following topics are also included: background information, implementation, vendor considerations, two essential correlations, valid starting partition offsets, and the simple protocol to align partitions, define file allocation unit size, and assign drive letters. It includes results from tests that show how partition alignment can affect performance for SQL Server 2008.

Download Disk Partition Alignment Best Practices for SQL Server

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/04/sql-server-disk-partition-alignment-best-practices/

SQLAuthority News – Book Review – The Rational Guide to Building Technical User Communities (Rational Guides)

The Rational Guide to Building Technical User Communities (Rational Guides) (Paperback)
by Greg Low

Short Review : A Great, one-of-its-kind book for everybody who is interested in building technical user community. There is no other book written on this subject but after this comprehensive book no further reading will be required.

Link to Amazon

Detailed Review :

This is for the first time in my book review, instead of talking about the book or author, I will introduce myself in a couple of lines to explain why and how this book is helpful to those interested in building community. I am a Microsoft MVP and the founder of SQLAuthority.com. I am actively involved with Technical User Group Community all over the world and have been helping many user groups to start, grow and continuously expand successfully. I am helping user groups in capacity of their mentor to overcome any obstacles they face while running the user group.

Author Greg Low is the most experienced person when it comes to community and people. He is a Microsoft Regional Director as well as mentor for Solid Quality Australia. For over a decade, he organized several Queensland User Groups. Greg is looked upon for guidance when User Groups are in need of the most appropriate advice. He is a very renowned person in technical community and does not need any further introduction.

This book focuses on the most important lessons learned by Greg over decades of involvement in technical communities. It provides practical advice on establishing, sustaining and growing these communities. This books is focused on helping user group leaders to deal with any potential pitfalls and contains workaround as well. The book is not limited to only user group leaders. In fact, this book is for everybody who is interested in community. If you are already a member of a community, this book is a perfect guide to help you get the most out of your membership.

The first chapter, which is appropriately named as – “People, Not Technology,” sets the pace and keeps up the concept live till the end of the book. In community, staying in focus is very important for people as everything revolves around it. No matter how knowledgeable or renowned a person is in the domain of technology, if he is not a people’s person, he cannot become an integral part of community and can never grow. For any technology to survive, support from people is required. Community provides combined power of people to lend strong support to community.Throughout the book, this key concept is cherished and explored.

Greg talks about two very important challenges for any technical community. 1) Finding Speaker and 2) Finding Operational Funds. If these two things are done appropriately the heart of any community, which is “Finding People,” comes to shape on its own. I run few User Groups and they are growing tremendously. Recently, we have been discussing about lots of legal issues. That was the prime reason why I chose to read this book. After reading this book through and through, I presented the valuable ideas from this book to my fellow leaders , and we all agreed on them right away. The rich experience that Greg shares with his readers can effectively solve their problems. Because of this book, we were no longer required to spend money on lawyer and instead we will now divert that fund toward our scholarship program.

Each chapter of this book is build to stand alone and it covers the subject with sufficient breadth. I really liked the real-life examples from Greg, where he talks about his friends, fellow MVPs, SQL Server Groups Leaders and other important people of community. As each chapter is written to have independent read, same topics have been covered twice. But what I liked about it is that same subject conveys different meaning when it is presented in a different consent. This particular aspect of the book, makes it a genuine must-read book.

One negative aspect of this book is that a very important checklist of the book is only given to those who will register online. If you have a friend who owns this book and has already registered online, you can ask that friend to pass this important stuff to you. One more thing, I would like to request Greg to include his latest learning from his experience as a Global Community Development of PASS, in the next version of his book. I am sure that more information about international technical user groups can be now included.

Rating : 5 Stars

Summary : A must-have book for everybody who is involved with technical community in any capacity. This is a unique book on this subject.

PS : I read this book while I was traveling from Kerala to Gujarat after attending a TechEd Event.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/03/sqlauthority-news-book-review-the-rational-guide-to-building-technical-user-communities-rational-guides/

SQLAuthority News – MVP Award Renewed

Year ago, it was a great, perhaps the proudest moment of my professional life. I was awarded Most Valuable Professional (MVP) for SQL Server by Microsoft.

Today, I received an email informing me that I have been re-awarded SQL Server MVP status by Microsoft in recognition of my community contributions. It’s yet another proud moment for me. I’m very happy and excited that my hard work is being recognized. I hope to work even harder and serve my community better!

Microsoft Thank You! There’s a huge list of people I would like to thank for this award. However, instead of listing their names here, I will be sending personal note to them to express my thanks.

Pinal Dave
SQL SERVER MVP
pinal “at” sqlauthority.com

Please feel free to contact me for any help.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/02/sqlauthority-news-mvp-award-renewed/

SQL SERVER – Difference between Line Feed (\n) and Carriage Return (\r) – T-SQL New Line Char

Today, we will examine something very simple and very generic that can apply to hordes of programming languages. Let’s take a common question that is frequently discussed – What is difference between Line Feed (\n) and Carriage Return (\r)?

Prior to continuing with this article let us first look into few synonyms for LF and CR.

Line Feed – LF – \n – 0×0a – 10 (decimal)

Carriage Return – CR – \r – 0×0D – 13 (decimal)

Now that we have understood that we have two different options to get new line, the question that arises is – why is it so?

The reason is simple. Different operating systems have a different way of understanding new line. Mac only understands ‘\r’ as new line, while Unix and Linux understand ‘\n’ as new line character. Our favorite OS windows needs both the characters together to interpret as new line, which is ‘\r\n’. This is the reason why a file created in one OS does not open properly in another OS and makes it messy.

Now, let us see how we can create a new line in SQL Server. It is a very simple script yet very useful when we have to do run print something or generate scripts. I have illustrated two examples below that are very easy to understand. In the first example, there are no new line chars inserted and for the same, everything is displayed in a single line. However, in the second example, new line char is inserted and the lines are separated with a new line.

Example 1: No new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL SELECT SecondLine AS SL' )
GO

Example 2: With new line feed char

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' +@NewLineChar + 'SELECT SecondLine AS SL' )
GO


I hope my examples make things more clear to you all. Let me have your feedback on this article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

SQL SERVER – 2008 – Policy-Based Management – Create, Evaluate and Fix Policies

This article will cover the most spectacular feature of SQL 2008 – Policy-based management and how the configuration of SQL Server with policy-based management architecture can make a powerful difference. Policy based management is loaded with several advantages. It can help you implement various policies for reliable configuration of the system. It also provides additional administration assistance to DBAs and helps them effortlessly manage various tasks of SQL Server across the enterprise.

1 Introduction
2 Basics of Policy Management
3 Policy Management Terms
4 Practical Example of Policy Management
4.1 Exploring of Facets
4.2 Create a Condition
4.3 Create a Policy
4.4 Evaluate a Policy
4.5 Fix Non-complying Policy
5 Summary

Read complete article here.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/30/sql-server-2008-policy-based-management-create-evaluate-and-fix-policies/

SQL SERVER – Maximum Number of Index per Table

TechEd on Road Ahmedabad, June 20, 2009, was a huge success. This grand event saw over 200 attendees actively participating in the sessions. We had attendees traveling from far and wide, including Delhi, Mumbai, Jaipur, Kerala, Baroda, Himmatnagar, Rajkot, among other cities from India. This enthusiastic participation made the event truly grand. It was a moment of bliss for me as I had not anticipated such tremendous positive response!

Although the Official time to commence the event was at 1:45 PM we were really excited to see the attendees entering the hall before the official time. We were more than happy to quickly arrange a special session for those attendees who came in early to keep them engaged. They won loads of gifts while waiting for the official sessions to start. We had a brainstorming quiz session. I was happy to see that people in Ahmedabad are now taking keen interest in enhancing their knowledge.

One of the questions was – What is the maximum number of Index per table? I received lots of answers to this question but only two answers are correct. Let us now take a look at them.

For SQL Server 2005:
1 Clustered Index + 249 Nonclustered Index = 250 Index
http://msdn.microsoft.com/en-us/library/ms143432(SQL.90).aspx

For SQL Server 2008:
1 Clustered Index + 999 Nonclustered Index = 1000 Index
http://msdn.microsoft.com/en-us/library/ms143432.aspx

I would like to thank one of the attendees of Ahmedabad TechEd Kruti Kansara for the above comment.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/29/sql-server-maximum-number-of-index-per-table/

SQL SERVER – 2008 – Management Studio New Features

This article describes the top 5 features of SQL Server Management Studio 2008. With the release of SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.

SQL Server 2008 has been released for a year now. In SQL Server 2000, DBA had to use two different tools to maintain the database as well as the query database, specifically SQL Server Enterprise Manager and SQL Server Query Analyzer. With the release of SQL Server 2005 both of these tools are combined into one tool: SQL Server Management Studio. For a while DBA who were fan of Query Analyzer requested to bring it back but as they keep on using SQL Server Management Studio (SSMS) they realized that it was much more convenient if every task related to SQL Server could be accomplished using SSMS. With the release of SQL Server 2008 Microsoft has upgraded SSMS with many new features as well as added tons of new functionalities requested by DBAs for long time.

Let us go over a few of the important new features of the SSMS 2008. The list of SQL Server 2008 SSMS improvements is very long. I have selected my favorite 5 features and we will go over them.

1. IntelliSense for Query Editing
2. Multi Server Query
3. Query Editor Regions
4. Object Explorer Enhancements
5. Activity Monitors

IntelliSense for Query Editing

This is my favorite feature so far. If you have used Visual Studio before, this feature may not be new to you. Implementation of IntelliSense is not as advanced as in Visual Studio but it is still very useful and well implemented.

After implementing IntelliSense, DBAs and developers will not have to remember all the syntax or browse online references. IntelliSense offers a few additional features besides just completing the world. You can see those option from SSMS Menu >> Edit >> IntelliSense >> (See the available options)

There are a total of five options available in Edit Menu. I suggest to experiment with all of them while playing with IntelliSense.

1. List Members
2. Parameter Info
3. Quick Info
4. Complete Word
5. Refresh Local Cache

If your IntelliSense does not bring up recently created objects, try “Refresh Local Cache” as described above, or press CTRL + SHIFT + R.

IntelliSense is a new feature and it will take some time to get adjusted to it. If any developer does not like this option, it can be turned off from Menu >> Query >> (deselect) Enable IntelliSense.

MultiServer Query

Usually DBA don’t manage only one database; they have many servers to manage. There are cases when DBA has to check the status of all the servers. I have seen one of the DBA who used to manage 400 servers, writing query using XML_CMDSHELL where he wanted to find out what the status of fullback up on all the servers was. In one of the recent consultancy job, when I had to find out if all the three servers were upgraded with Services Packs (SP), I ran a query to find version information on all the three instance separately in three windows.

SSMS 2008 has a feature to run a query on different servers from one query editor window. First of all make sure that you registered all the servers under your registered server. Once they are registered Right Click on server group name and click New Query as shown in the image below.

Now in the opened query window run the following query (you can find it in the sample code for this article):

SELECT
SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductLevel') AS ProductLevel,
SERVERPROPERTY('ProductVersion') AS ProductVersion

Query above will give the result shown in the image below. Note that we have only three columns in the SELECT but our output contains four columns. The very first column is the “Server Name” and it is added by SQL Server to indentify rows belonging to a specific server.

If all of the above servers are registered with “central server” – the option which is right below it, other administrators can also register to those entire servers by simple registering one central server.

Query Editor Regions

This feature exists in many programming languages already but now it is newly introduced in SSMS 2008. The reason I am highlighting this feature is because there are cases when T-SQL code is longer than hundreds of lines and after a while it keeps on getting confusing.

The regions are defined by the following hierarchy:

1. From first GO command to next GO command.
2. Statements between BEGIN – END, BEGIN TRY – END TRY, BEGIN CATCH – END CATCH
3. Multiline statements

In the following code snippet I have reported an example which has five coding regions (you can find it in the sample code for this article). I have made small comment besides them describing their region type.

CREATE PROCEDURE MyTest -- Region Type 1
AS
BEGIN -- Region Type 2
SELECT 1
BEGIN -- Region Type 2
SELECT 2;
SELECT * -- Region Type 3
FROM sys.objects;
SELECT * -- Region Type 3
FROM sys.columns;
SELECT 3;
END
END
GO


Regions can be collapsed as well as expanded by clicking the small sign of ‘-’ or ‘+’ besides them. The following image shows a fully expanded region code snippet and a fully collapsed code snippet.

If there is any single-line statement, it does not qualify for a coding region.

Object Explorer Enhancements

Object Explorer Detail initially looks the same as the previous version but when right clicked on the bar with labels it reveals what it can do. This feature looks the same as the Vista OS folder option but when looked at how it is implemented for SQL Server data, it is really amazing. Object Explorer Detail view can be enabled by either going to Menu >> View >> Object Explorer Detail or pressing F7.

In Object Explorer Detail the new feature is Object Search. Enter any object name in the object search box and the searched result will be displayed in the same window as Object Explorer Detail.

Additionally, there are new wizards which help you perform several tasks, from policy management to disk monitoring. One cool thing is that everything displayed in the object explorer details screen can be right away copied and pasted to Excel without any formatting issue.

Activity Monitors

This feature is totally revamped in SSMS 2008. When opening “Activity Monitor” it shows a screen similar to the following image.

There are four graphs 1) % Processor Time, 2) Waiting Tasks, 3) Database I/O, 4) Batch Requests/Sec

Additionally, there are four tabs which also reflect some statistics of the above four graphs 1) Processes, 2) Resource Waits, 3) Data File I/O, 4) Recent Expensive Queries. Clicking on the tables will expand the tabs as shown in the following image.

All the four tabs provide very important information, however the one which I refer most is ‘Recent Expensive Queries’. Whenever I find my server running slow or having any performance related issues my first reaction is to open this tab and see which query is running slow. I usually look at the query with the highest number for Average Duration. The Recent Expensive Queries monitors only show queries which are in the SQL Server cache at that moment.

Summary

There are lots of new features of SSMS 2008. The ones I have listed today are the most prominent and my personal favorites.

Reference : Pinal Dave (http://blog.SQLAuthority.com), dotnetslackers

Original Post : http://blog.sqlauthority.com/2009/06/28/sql-server-2008-management-studio-new-features-2/

SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.

I had previously written two articles about an intriguing observation of triggers online.

SQL SERVER – Interesting Observation of Logon Trigger On All Servers

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

If you are wondering what made me write yet another article on logon trigger then let me tell you the story behind it. One of my readers encountered a situation where he dropped the database created in the above two articles and he was unable to logon to the system after that.

Let us recreate the scenario first and attempt to solve the problem.

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO
/* Dropping Database AuditDB */
/* Please note login to Server again will
Produce Errors */
USE master
GO
DROP DATABASE AuditDB
GO

After the database is dropped and we try to login again the following error will be displayed.

Logon failed for login ‘SQL\Pinal’ due to trigger execution.
Changed database context to ‘master’.
Changed language setting to us_english. (Microsoft SQL Server, Error: 17892)

This error could have been evaded if the Trigger was dropped prior to dropping database.

USE master
GO
DROP TRIGGER Tr_ServerLogon ON ALL SERVER
GO

Now, it will not be possible to logon to the database using SQL Server Management Studio. The only way to fix this problem is using DAC. Read more details about DAC Using a Dedicated Administrator Connection to Kill Currently Running Query. Now let us take a look at the example in our case where I am using windows authentication to logon to the system.

Connect SQL Server using sqlcmd and -A option, which will establish DAC. Running the following command on command prompt will allow you to login once again in the database. I am specifying argument -d master which will directly enable me to logon to master database when DAC is connected.

C:\Users\Pinal>sqlcmd -S LocalHost -d master -A
1> DROP TRIGGER Tr_ServerLogon ON ALL SERVER
2> GO

I hope my explanation on logon triggers, DAC, and sqlcmd is clear to everyone. Let me have your thoughts about my present article.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/27/sql-server-fix-error-17892-logon-failed-for-login-due-to-trigger-execution-changed-database-context-to-master/

SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution

Does the title of this post trigger your mind? If you all remember, a few days back I had written an article on my interesting observation regarding logon triggers. I would advise you to first read SQL SERVER – Interesting Observation of Logon Trigger On All Servers before continuing with this article further to have a complete idea of the subject.

The question I put forth in my previous article was – In single login why the trigger fires multiple times; it should be fired only once. I received numerous answers in thread as well as in my MVP private news group. Now, let us discuss the answer for the same.

The answer is – It happens because multiple SQL Server services are running as well as intellisense is turned on.

Let us verify the above answer.

First, run the following script to create database and logon Audit table.

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO


After that, disable all the other SQL Server Services as delineated in the image below using SQL Server Configuration Manager.

Next, try to logon to the system only one more time.

Check the audit table again to verify if there is a single entry for single login.

I would once again like to thank all of you for active participation and coming up with wonderful suggestions and answers. Let me have your opinion on this observation.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/26/sql-server-interesting-observation-of-logon-trigger-on-all-servers-solution/

SQLAuthority News – Authors Visit – K-MUG TechEd Trivandrum on June 27, 2009

K-MUG is organizing TechEd Trivandrum on 27th June, 2009. Not just this, they are launching an official PASS Chapter in Trivandrum. The Agenda of the event is here and if you are around Trivandrum do not miss the opportunity to be a part of this upcoming great event. If you are keen to know what this event holds in store for you then read about TechEd in Ahmedabad, which saw a huge number of attendees and was a grand success. Jacob Sebastian and Pinal Dave had presented two solid SQL Sessions and created lots of buzz about Microsoft.

Click here for Review of Pinal Dave

Click here for Review of Jacob Sebastian

I will be at TechEd and will present a session on ‘SQL Server Best Practices’. This session will mainly focus on SQL Server best practices that are not commonly known. There is a huge gamut of information which can drastically enhance the performance of a system, but these are not widely known to users. I will share with you all those brilliant tips through demo to help to develop clear idea.

Jacob Sebastian, my friend and a SQL Server MVP, will also be there to support this event. He will present a session on ‘Logical Query Execution Flow’ where he will discuss how a given T-SQL Query is processed and executed. A comprehensive understanding of the query execution flow will help you solve several common problems and will help you answer a number of baffling questions that you often come across. Believe me this session was very much applauded at TechEd in Ahmedabad. I highly recommend this session to everyone interested in technology.

Jacob Sebastian will also give away his book ‘The Art of XSD – SQL Server XML Schema Collections’ at K-Mug TechEd. This unique book deals with the subject of XSD, which is very crucial to understand if you want to explore the potential of XML. This topic is hardly well covered in any other book, which makes this book a must-have if you want to explore SQL Server Beyond Relational. Please read post by Jacob regarding how one can win his famous book here.

If any of you is interested in attending this event and meeting Jacob and me then do let me know. I will be staying an extra day at Trivandrum on Sunday, 28th June, so we can meet on that day as well.

Again, those of you who have requested for SQL Server Cheat Sheet, I will have one copy for all of you who will attend this event.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/25/sqlauthority-news-authors-visit-k-mug-teched-trivandrum-on-june-27-2009/

SQLAuthority News – Update on pinaldave.com and SQLAuthority.com

Problem: SQLAuthority.com site was not allowed in some browsers as pinaldave.com site was marked as malware or badware distributing third party site.

Status: SQLAuthority.com and pinaldave.com both the sites are safe now and there is no threat to your computer. Feel free to click on the links.

Since the last two mornings I have received over 200 emails querying about the error my sites were generating. I encountered countless questions and worst of all I was thrown verbal abuse for not getting my own site up right away and for being careless. I’m much relieved today as everything is back to normal and I forgive those who abused me. There are few explanations I have been demanded numerous times by my several regular readers. Many readers send me question on twitter demanding to know what actually happened.

Question : What is the current status of your site?
Answer : All well, no problem. Business is as usual.

Q: What exactly happened?
A: I host my blog on a supreme blog platform wordpress.com. All the images that are displayed in this blog I host at my other hosting facility where I host my website pinaldave.com. All the images, photos and code come from pinaldave.com.

This was done to facilitate cross posting any blog at anytime on any platform without any hassle with one mouse click.

My host where I hosted pinaldave.com got affected by some kind of virus and trojon. Because of the same reason, all the websites which were hosted on that server were adversely affected with malwares and badwares. My site saw this impact too and all the browsers and anti virus started to report it as unsafe site.

My blog site SQLAuthority.com contains lots of images and photos from pinaldave.com, so it was marked as a site containing links to malwares. Commutatively, both my sites were as marked as malicious sites and browsers started to prevent displaying them.

Q: How did you fix the problem?
A: I had to change my webhost to a new one and re-uploaded my site.

Q: Why did you change your webhost?
A: Yes, I tried to inform my webhost about this attack but to my utter disappointment they did not take this issue seriously. I tried to call them up multiple times and explained them the problem. For all my efforts, I received verbal abuse from their supervisor.

This is not the first time where I faced such grave issue with my previous host. This is the third major issue in the past two months. Previously, their SQL Server 2008 was down for two days straight so my site was also down for the same duration. Just a month ago, I faces problem with my email account; it stopped working for two full days. As my site was down for almost five days in the past 60 days and to top this I faced verbal abused by the supervisor, I decided to move on to a new host.

Q: Who was your previous host?
A: I do not want to talk about them any more. They used to host my site for long time. After I reported the problem they did not care to respond to me and did not fix the problem for over 24 hours.

Q: Are you angry with your previous host?
A: Not at all. Such things do happen so I’m chilled. However, when I was politely reporting a problem to them I did not expect to hear abusive language from their customer support. It came as a shock to me but now I’m out of it. After all, I have to move on.

Q: Who is your new host?
A: GoDaddy.com and they are doing great! Most of the people think of Danica Patrik when GoDaddy is mentioned, I think of Index De-Fragmentation Script of SQLFool.

Q: What’s next?
A: Please spread the words that everything is alright now.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/24/sqlauthority-news-update-on-pinaldave-com-and-sqlauthority-com/

SQL SERVER – 2005 – 2008 – Delete Duplicate Rows

I had previously penned down two popular snippets regarding deleting duplicate rows and counting duplicate rows. Today, we will examine another very quick code snippet where we will delete duplicate rows using CTE and ROW_NUMBER() feature of SQL Server 2005 and SQL Server 2008.

This method is improved over the earlier method as it not only uses CTE and ROW_NUMBER, but also demonstrates the power of CTE with DELETE statement. We will have a comprehensive discussion about it later in this article. For now, let us first create a sample table from which we will delete records.

/* Create Table with 7 entries - 3 are duplicate entries */
CREATE TABLE DuplicateRcordTable (Col1 INT, Col2 INT)
INSERT INTO DuplicateRcordTable
SELECT 1, 1
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 1 --duplicate
UNION ALL
SELECT 1, 2
UNION ALL
SELECT 1, 2 --duplicate
UNION ALL
SELECT 1, 3
UNION ALL
SELECT 1, 4
GO

The above table has total 7 records, out of which 3 are duplicate records. Once the duplicates are removed we will have only 4 records left.

/* It should give you 7 rows */
SELECT *
FROM DuplicateRcordTable
GO

The most interesting part of this is yet to come. We will use CTE that will re-generate the same table with additional column, which is row number. In our case, we have Col1 and Col2 and both the columns qualify as duplicate rows. It may be a different set of rows for each different query like this. Another point to note here is that once CTE is created DELETE statement can be run on it. We will put a condition here – when we receive more than one rows of record, we will remove the row which is not the first one. When DELETE command is executed over CTE it in fact deletes from the base table used in CTE.

/* Delete Duplicate records */
WITH CTE (COl1,Col2, DuplicateCount)
AS
(
SELECT COl1,Col2,
ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

It is apparent that after delete command has been run, we will have only 4 records, which is almost the same result which we would have got with DISTINCT, with this resultset. If we had more than 2 columns and we had to run unique on only two columns, our distinct might have not worked here . In this case, we would have to use above the mentioned method.

/* It should give you Distinct 4 records */
SELECT *
FROM DuplicateRcordTable
GO

This method is a breeze and we can use this for SQL Server version 2005 and the later versions.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

SQLAuthority News – TechEd on Road Ahmedabad June 20, 2009 – An Astounding Success

TechEd on Road Ahmedabad

In India, TechEd was held in Hyderabad in the month of May. You can read my TechEd summary article here. A similar event will be organized in 10 major cities in India.

Ahmedabad saw its first TechEd on Road and it was wholeheartedly welcomed by technology enthusiasts. The event was held at Rock regency, in the heart of Ahmedabad on June 20, 2009. We had attendees traveling over 500 miles to attend the event. We had attendees from Delhi, Mumbai, Jaipur, Kerala, Baroda, Himmatnagar, Rajkot, among other cities from India. It was a joyous and overwhelming experience for me to see them travel all the way from different parts of India to participate in TechEd event. Hats Off to you all!

TechEd event was attended by 200+ attendees. One of the reasons why this event had created lots of anticipation, which eventually lead to oversubscribed event, was two back to back SQL sessions by SQL Server MVPs. Pinal Dave and Jacob Sebastian; both are renowned for their technical sessions and their unique style of presentation.

Scroll down at the end of this article for photographs of the event.

Technical Sessions

The Official time to commence the event was at 1:45 PM but we had attendees pouring into the hall since 12:00 PM. We held a special session for attendees who came in early to keep them engaged. We provided them the opportunity to win loads of gifts while they waited for the official sessions to start. I must express special thanks to our event coordinator Dipen Shah, a bright young man who has just graduated as a Computer Engineering from a prestigious engineering collage for his excellent quiz master skills. It’s great to see that next generation has so much passion for technology.

The most awaited event started sharp at 1:45 PM with Keynote where in the beginning Dipen explained what is TechEd and why this event is held. Pinal Dave known for his distinctive humorous style, started the keynote from the history of Microsoft Windows and the history of SQL Server. It was a 15-mins session where everybody thoroughly enjoyed and learned the root of technology and how it evolved with changing times. All the attendees enthusiastically participated during the keynote by Pinal Dave, and lots of gifts were given away. Jacob Sebastian picked up the keynote by Pinal Dave and continued the session. Pinal explained the history while Jacob explained the future. He explained how Microsoft is doing in terms of beyond relations and on other technological fronts. The audience really enjoyed learning about how technology progressed and how is it going and growing.

Jacob’s remark “ SQL Server being now 21 years old and can get married” created a riot of laughter!

The first session was by Pinal Dave on SQL Server Best Practices.

The second session was by Jacob Sebastian on Understanding Query Execution Flow and Optimization Tips.

If you want to know more about the technical details for these two sessions, then do not wait for my blog article. Attend our next user group meeting, which will be announced soon. The same session will be delivered by these two experts on request. Here, I can only say that these two sessions were very well appreciated by the attendees and everybody was extremely happy to be a part of this event. The event lived up to everybody’s expectations!

Event Sponsors

I must thank all my sponsors for their support – Microsoft, PASS, Solid QualityMentors, Digicorp, Quest and Excellence Infonet. Ahmedabad is fast catching up as a technology hub and growing very fast. We got our registration full in less than 36 hours. We opened up stand by tickets and which were full by 100%.

When I expressed my situation to Rushabh Mehta of Solid Quality India and told him that the event is oversubscribed by 200% on the day prior to the event, he right away told me that he does not want anybody from the community to turn away from door disappointed and miss the wonderful opportunity to learn from this event. Rushabh promised to cover all additional costs to accommodate the additional attendees. Not only this, he sponsored SQL Server Cheat Sheets for all the attendees. Rushabh, special thanks to you! Because of your support we were able to accommodate everybody who wanted to attend this event.

Arya Events did a wonderful job of making this large scale event a very smooth flowing event. There were no queues and no claustrophobic feelings. We hope to have them for all of our future events. All the graphic designs and logistics were provided by Digicorp – a vibrant young company from Ahmedabad. USB drives were provided by Excellence Infonet.

Miscellaneous Details

• Pinal Dave got awarded for being the winner of T-SQL Challenge by Jacob Sebastian.
• New book of Jacob Sebastian – “The Art of XSD” was available in print.
• SQL Server Cheat Sheets were very much in demand.
• We ordered 50 more chairs while the event was going on, as we ran out of them again (this is third time in row – when we needed more chairs)
• .NET MVP Kaushal Parik attended and encouraged every participant.
• Four directors of Digicorp - Abhishek, Nachiket, Kuntal and Sanket were present at the event to give their support.
• Ritesh Shah of SQLHub performed really well and answered almost all the questions and won the highest number of gifts.
• Mahesh Dhola announced his next event.
• Kaushal Bhavsar Microsoft Student Partner was present throughout the event.

Giveaways

This event had so many giveaways that almost all the attendees got something. One of the attendees told me that he received 7 goodies today. Well, undoubtedly it was fun to share some of the nice things with all attendees.

• 18 T-shirts
• 3 Solid Quality Learning Polo Premium Shirts
• 9 USB drives
• 10 stress balls
• 3 technology books
• 33 high quality pens
• 300 SQL Server Cheat Sheets
• 200 Query Execution Flow Chart
• 200+ SQLAuthority.com and BeyondRelations.com Stickers

Photos

TechEd On Road Ahmedabad

Pinal Dave at TechEd on Road Ahmedabad

Pinal and Jacob

Pinal Dave from SQLAuthority.com

Jacob Sebastian from BeyondRelational.com

Pinal Dave at TechEd on Road Ahmedabad

Jacob Sebastian at TechEd on Road hmedabad

Pinal and Jacob

Dipen Shah (Microsoft Student Partner)

Pinal and Kaushal (MVP)

Pinal and Abhishek (Digicorp)

Pinal and Ritesh (SQLHub)

Pinal (SQLAuthority.com), Aryaman (Arya Events), Jacob (BeyondRelational.com)

Nupur and Pinal – SolidQ and SQLAuthority Crew

Please note: I have a few SQL Server stickers as well as SQL Server Cheat Sheets still left with me. If you want them then please send me email at pinal ‘at’ sqlauthority.com with your name, address and telephone number.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/22/sqlauthority-news-teched-on-road-ahmedabad-june-20-2009-an-astounding-success/

SQLAuthority News – Risk and Health Assessment Program for Microsoft SQL Server – Scoping Tool v1.1

This download package is intended for Microsoft Premier Customers Only. This package includes all of the scoping tools necessary to prepare and qualify your environment to receive a Risk and Health Assessment Program for Microsoft SQL Server. Extract the Scoping Tool zip package to the tools server that will be used during the RAP engagement. Refer to Instructions.txt in the Scoping Tool folder for detailed instructions on how to execute the tool and gather the results.

Phases of the Risk and Health Assessment Program for Microsoft SQL Server
Phase 1: Data Gathering
Phase 2: Data Analysis
Phase 3: Reporting and Knowledge Transfer

Key Benefits of the Assessment Approach
Maintain Maximum Efficiency
Reduce Support Costs
Maximize the Value of Your IT Investment
Improve Business Confidence

Download Risk and Health Assessment Utility

Reference : Pinal Dave (http://blog.sqlauthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/21/sqlauthority-news-risk-and-health-assessment-program-for-microsoft-sql-server-%E2%80%93-scoping-tool-v1-1/

SQL Server – Understanding Table Hints with Examples

Today we have a very interesting subject to look at. I tried to look for help online but have not found any other documentation besides what we have from the Book Online.

Let us try to understand what are the different kinds of hints available in SQL Server and how they are helpful.

What is a Hint?

Hints are options and strong suggestions specified for enforcement by the SQL Server query processor on DML statements. The hints override any execution plan the query optimizer might select for a query.

Before we continue to explore this subject, we need to consider one very important fact and say some words of caution. SQL Server Query optimizer is a very smart tool and it makes a best selection of execution plan. Suggesting hints to the Query Optimizer should be attempted when absolutely necessary and by experienced developers who know exactly what they are doing (or in development as a way to experiment and learn).

There are three different kinds of hints. Let us understand the basics of each of them separately.

Please continue reading article here.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Original Post :http://blog.sqlauthority.com/2009/06/20/sql-server-%E2%80%93understanding-table-hints-with-examples/

SQL SERVER – Why You Should Attend PASS Summit Unite 2009- Seattle

PASS Summit Unite 2009 – the premier event for SQL Server professionals – will be held in Seattle from November 2 to November 5. It is the largest and the most intensive Microsoft SQL Server conference in the world organized by SQL Server users for SQL Server users. This year marks the 10th Anniversary of PASS Community Summit, making the event even more special. Every year, this event sees a huge number of attendees, as apart from high quality technical sessions it provides unparalleled access to the Microsoft SQL Server development, SQL CAT, and Customer Service and Support teams.

PASS Summit is an ideal conference for people who want to expand their knowledge. Here, sessions are available for all levels of users, from novices to experts. Specialized sessions are organized for SQL Server users who manage and support SQL Server data platforms; design, architect, and implement database solutions; architect and develop SQL Server applications; and architect, develop, and administer business intelligence solutions. So, if you are a database administrator, application developer, business intelligence and IT professional or consultant you will benefit with the latest and comprehensive SQL Server knowledge available through highest-rated technical sessions and speakers. At PASS Summit, you can choose to become a volunteer, Summit Ambassador, or even a Speaker!

Why you should attend PASS Summit

There is not one or two but a number of reasons why you must be a part of PASS Summit. First, it is a good platform to learn the latest skills and strategies through over 160 expert-led technical sessions as well as pre/post-conference sessions, which will include every aspect of SQL Server. Here, you can sharpen your skills and stay up to date with the latest SQL Server topics and technologies. This knowledge will help you optimize SQL Server. It will also let you stay ahead of the curve as you will be among the first to learn about the latest advances, roadmaps, and plans for Microsoft SQL Server.

You will get to learn SQL Server best practices from thousands of SQL Server MVPs, Microsoft team members, authors, experts, and peers who will attend the event. You can even equip yourself with the strategies and skills to improve the ROI of your SQL Server environment such as virtualization technologies, performance tuning and scaling, better hardware utilization, simplified management, need for better business intelligences, to name a few. Further, you can take advantage of the group discount and enhance the knowledge and skills of the entire team; you can send 5 employees and save.

The three-day event will be marked by a lot of learning, sharing, and networking, which will help you grow both your knowledge and contacts. PASS Summit provides you a golden opportunity to build your network as well as identify and meet potential customers or employees. If you are a consultant, vendor, and are looking for better career opportunities, PASS Summit is the perfect platform to meet and show your skills to your potential new customers and employers. And if you are already employed at an organization, here you can get in touch with top-class SQL Server experts who can help you with any future needs. Moreover, breakfasts, lunches, and evening receptions, which are included with registration, are meant to provide more and more networking opportunities.

Apart from gaining knowledge and networking PASS Summit is a good platform to solve high-priority, mission-critical SQL Server issues through Microsoft “Ask the Experts” Lounge and the CSS First Aid Station where you can have direct access to Microsoft developers, service and support, and MVPs.

At PASS Summit, you will not just gain new ideas but will also be inspired from top professionals and experts. Learning new things about SQL Server, interacting with different kinds of professionals, and sharing issues and solutions will improve your understanding and turn you into a better SQL Server professional who can leverage and optimize SQL Server to improve business.

You can find the details of the sessions here:

• PASS Summit 2009 Agenda
• Agenda at a glance
• Pre/Post Conference Sessions
• Spotlight Sessions
• Program Sessions

And last and final reason why you should visit PASS – I will be there. Let us meet and talk SQL.

Reference : Pinal Dave (http://blog.sqlauthority.com)

Original Post : http://blog.sqlauthority.com/2009/06/19/sql-server-why-you-should-attend-pass-summit-unite-2009-seattle/