10 Database Articles I Enjoyed Reading in 2009

Just like others do, I often bookmark useful articles. Here’s a list of 10 database articles published this year and bookmarked by me. Hope you like reading them. Here’s the list:

Top 20+ MySQL Best Practices

5 Useful SQL Server Scripts

Which SQL data type to use when?

SQL Server Five methods converting rows to columns

Beyond SoundEx - Fuzzy Searching in MS SQL Server

Introduction to JOINs - A simplest Visual Explanation of Join

10 Essential SQL Tips for Developers

Will Oracle kill MySQL ? Why they Can’t and Why they Wouldn’t

Install SQL Server (Express) with your Application

Re-index your database

This is the last post of the year 2009. I look forward to another exciting year 2010 full of fun and learning. Happy New Year to all the readers!

Feedback:

10 Must Read SQL Server Reporting Services articles on SQLServerCurry - 2009

This post contains 10 Must Read articles on SQL Server BI that were published in the year 2009 on SQLServerCurry.

Overview of SQL Server Reporting Services (SSRS)

Create a SSRS Report based on a Cube and Deploy it

Using Report Builder 3.0 to create a Chart with XML Data

Programmatically Create Data Driven Subscriptions in SQL Server 2005/2008

Working with Report Models in SSRS - Overview – Part I

Working with Report Model in SSRS - Part II

Working With Report Model in SSRS – Part III

Creating a Drill Down Report with SQL Server 2008

Using Embedded Code in SQL Server Reporting services (SQL Server 2008)

Working with Report Definition Language (RDL) and Change it to add a column

Looking forward to an exciting year ahead :)

Feedback:

10 Must Read SQL Server Administration articles on SQLServerCurry - 2009

This post contains 10 Must Read articles that were published in the year 2009 on SQLServerCurry and contains SQL Server Administration scripts to perform some common administration tasks on your SQL Server database

Generic SQL Server Stored Procedure to display Table Names of any Database passed to it

Set a database to read only mode using SQL Server 2005/2008

Delete BackUp and Restore History for databases in SQL Server 2005/2008

How to do a Full and Differential Database Backup in SQL Server

SQL Server 2005 and 2008 Diagnostics Queries and Configuration Checklist

Find Out Tables in a Database Having Identity Columns

Enable and Disable All the Triggers in a SQL Server Database

Recompiling Stored Procedures in SQL Server – All, Few or One at a time

How to Create a SQL Server Agent Proxy Account

Programmatically Change the default path for SQL Server Database creation

To read some more articles on SQL Server Administration, check here

Feedback:

Assign a Database Role to Multiple Logins using SSMS

Here are the steps to assign a database role (eg: db_owner) to Multiple Logins:

1. Open SQL Server Management Studio (2005/2008). In the Object Explorer, after connecting to SQL Server, expand the Databases folder > Expand your Database Node (For demonstration purposes, I have chosen ‘Adventure Works’ database)

2. Now navigate to the Security > Roles > Database Roles folder. Double click on db_owner

3. In the Database Role Properties dialog box, click on the ‘Add’ button to open up the ‘Select Database User or Role’ dialog box and the enter the name of the user/role to add. You can even click on the Browse button to search the names.

Click OK and then OK again once you are done adding the users/roles to the Database login.

Feedback:

Find Out Memory Used by SQL Server Components

The sys.dm_os_memory_clerks Dynamic Management View returns the set of all memory clerks that are currently active in the instance of SQL Server 2005/2008.

Here’s how to use this view:

SELECT name, type,
SUM(single_pages_kb + multi_pages_kb) AS MemoryKB
FROM sys.dm_os_memory_clerks
GROUP BY name, type
ORDER BY MemoryKB desc

OUTPUT

Feedback:

Resolving the SQL Server Error “The request failed or the service did not respond in a timely fashion”

When you are unable to log into your SQL Server, one recommended solution is to see if the required services are running. To do so, you can go to Start > Microsoft SQL Server 2005/2008 > Configuration Tools > SQL Server Configuration Manager.

Select the ‘SQL Server Services’ node and you can see the services that are running. If you observe in the screenshot shown below, the SQL Server service (MSSQLSERVER) is not running

When you right click and attempt to start the service, you may encounter an error that says “The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details”

One of the solutions to resolve this error is to see if the default port of SQL Server i.e: 1433 is not being used by a different service. Click on the ‘Protocols for MSSQLServer’ node

On investigating, I found that the VIA protocol was using up port 1433.

I disabled the protocol by right clicking on it and the problem got solved

Feedback:

10 Must Read T-SQL articles on SQLServerCurry - 2009

This post contains 10 Must Read articles that were published in the year 2009 on SQLServerCurry and contains T-SQL scripts to perform some common tasks on your SQL Server database

List all the Stored Procedures of a Database and their Definitions using T-SQL in SQL Server 2005/2008

Select the Highest value in a Group/Category using SQL Server 2005/2008

Select the Highest and Second Highest value in a Group/Category using SQL Server 2005/2008

RANK() VS DENSE_RANK() With an Example using SQL Server 2005/2008

List All Tables with their Modification date in SQL Server

6 Common Uses of the undocumented Stored Procedure sp_MSforeachdb

8 Common Uses of the undocumented Stored Procedure sp_MSforeachtable

Delete Random Records From a Table Using SQL Server 2005/2008

Find Out The Parameters Of A Stored Procedure in SQL Server 2005/2008

Find the Missing Identity Numbers in SQL Server 2005/2008

To read some more articles on T-SQL, check here

I will also be publishing 10 Must Read article on SQL Administration and BI in the upcoming articles

Feedback:

Programmatically Enable Auditing on Logins in SQL Server

The xp_instance_regwrite extended stored procedure helps you alter registry entries. To enable both success and failure login auditing, use the following command:

USE master
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', _
N'Software\Microsoft\MSSQLServer\MSSQLServer',_
N'AuditLevel', REG_DWORD, 3

To disable login auditing

USE master
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', _
N'Software\Microsoft\MSSQLServer\MSSQLServer',_
N'AuditLevel', REG_DWORD, 0

Login auditing is important as it helps you identify the user who performed unwanted actions, when they occur.

Note: When I ran the query, I got the message “0 rows affected”. However when you open the registry, the entry is what is expected to be, after running the query.

Feedback:

Convert DateTime to VarChar in SQL Server

I was recently asked on the forums how to save DateTime as varchar with the time and without it. Although this requirement is simple, it still daunts many novice programmers. Here’s how to do so:

-- Convert DateTime to VarChar without Time
SELECT CONVERT(varchar(12), GETDATE(), 102)

-- Convert DateTime to VarChar with Time
SELECT CONVERT(varchar(20), GETDATE(), 20)

OUTPUT

You can also check some additional Date and Time Styles

Feedback:

How to Detect If Full-Text Search is Enabled on a SQL Server Database

To detect if the ‘Full Text Search’ feature is enabled on your database, use this query:

USE AdventureWorks
SELECT is_fulltext_enabled
FROM sys.databases
WHERE database_id = DB_ID()

If Full-Text Search is enabled, the query returns 1, else the query returns 0 if it is not

Here’s the output after running the query on a database which has Full-Text Search enabled

Feedback:

Retrieve a Report of all Schema Changes within a SQL Server 2008 Database

Some of the most obvious things are often overlooked!

During one of my discussions with a client, I was asked for a simple way to detect all schema changes (DDL Operations) in the SQL Server database and to generate a report out of it. These people were interested in only the DDL operation performed and who performed it. To detect changes, they were planning to create a trigger, detect changes and then write it to a log file.

I advised them to use the Database Standard Reports which does the job for us

Open SSMS 2008 > Right Click a Database > Report > Standard Reports > Schema Changes History

The report displays you a history of all committed DDL statement executions within the Database

Feedback:

SQL Data Services (SDS) New Functionality with SQL Server 2008 R2

Previously when connecting to SQL Azure with SQL Server Management Studio, we had to cancel the default connection to database engine and later choose the New Query option. Refer to post Connecting to SQL Data Services (SDS) with SQL Server Management Studio (SSMS)

Not anymore. With SQL Server 2008 R2 (Nov CTP) we can connect to the database engine and see the list of objects (databases, tables etc) in the object explorer for SQL Azure.

The figure shows the list of databases, tables within a database and a new user ‘Smita’ added to NewDB database.

For adding a Login and a User, we need to specify the T-SQL Statements as follows:

CREATE LOGIN Smita
    WITH PASSWORD = 'Pa$$w0rd'
GO
CREATE USER Smita
    FOR LOGIN Smita
    WITH DEFAULT_SCHEMA = MySchema
GO

As the user is with the default schema, the table created automatically gets MySchema as schema name

CREATE TABLE Names
(id int IDENTITY ,
[Name] nvarchar(50))

If we issue the command

SELECT * FROM Names

by any other user whose default schema is not MySchema, we get error as follows:

Hence the query needs to be given as:

SELECT * FROM MySchema.Names  

so as to avoid errors.

Feedback:

Simple way to Swap the value of a Bit field in SQL Server

A developer by mistake had set the Bit fields to the wrong value for a bunch of rows. So instead of setting them to ‘1’, he had set it to ‘0’. Here’s a simple way to fix the error by swapping the Bit field values:

We will use the Products table of the Northwind database for our example. The Products table has a Discontinued Column which has the datatype ‘Bit’. We will swap the Bit field values of all Products with CategoryID=2

Let us first see the Original values

SELECT ProductID, ProductName, Discontinued from Products
WHERE CategoryID=2

Now let us swap the Bit values using the Bitwise Exclusive OR (^) operator

UPDATE Products
SET Discontinued = Discontinued ^ 1
WHERE CategoryID = 2

Now after running the same select query, you will get the following output

Observe that all the Bit Values have been swapped using the ^ operator

Feedback:

How to Display DateTime Formats in Different Languages using SQL Server

SET LANGUAGE can be used to specify the language environment for the current session. You can use the sys.syslanguages to first determine the languages present in an instance of SQL Server

SELECT * FROM sys.syslanguages

Once you know the different languages, use SET LANGUAGE to display the DateTime in the language you desire as shown below. You can specify either the alias of the language (Spanish, German) or the name(us_english) as the argument. The query shown below displays the Day Name in different languages:

SET LANGUAGE Spanish
SELECT DATENAME(dw, GETDATE()) AS 'DayName'

SET LANGUAGE German
SELECT DATENAME(dw, GETDATE()) AS 'DayName'

SET LANGUAGE us_english
SELECT DATENAME(dw, GETDATE()) AS 'DayName'
GO

OUTPUT

Feedback:

How to Get the Day Name From a Date using SQL Server

A lot of users have confusions over how to get a Day Name from a Date in SQL Server. I have seen developers writing custom functions to achieve this simple requirement. The solution lies in the DATENAME function

I will take an example of the Northwind database;

SELECT OrderID, CustomerID, OrderDate,
DATENAME(dw, OrderDate) as DayName
From Orders

OUTPUT

Observe how we are using the DATENAME function specifying ‘dw’ or ‘weekday’ as the datepart. Simple, wasn’t it!

It helps to check the SQL Server Books Online to go through the built-in functions. It will benefit you in the long run!

Feedback:

Using Report Builder 3.0 to create a Chart with XML Data

In my previous article on Report Builder 3.0, we discussed how report parts can be published. In this article, we will create a chart with XML data without connecting to any database.

1. Start Report Builder 3.0 > Select Report > Chart wizard and click Create

2. Select ‘Create a dataset’ and click Next > from Data source connection click New tab

3. Change the selection type to XML

and click Ok

4. Paste following query in the query designer and click Next

<Query>
ElementPath>Root /Rating {@GlobalRating (Integer)} /NM {@Name} </ElementPath>
<XmlData>
<Root>
<Rating GlobalRating="300">
<NM Name="Priti Zinta" />
</Rating>
<Rating GlobalRating="500">
<NM Name="Aishwarya Rai" />
</Rating>
<Rating GlobalRating="650">
<NM Name="Madhuri Dixit" />
</Rating>
<Rating GlobalRating="750">
<NM Name="Katrina Kaif" />
</Rating>
<Rating GlobalRating="450">
<NM Name="Priyanka Chopra" />
</Rating>
</Root>
</XmlData>
</Query>

5. If you execute the query, you will get the following results:

6. Select Pie chart > double click on ‘GlobalRating’ which will automatically go to values. Drag and Drop name in categories area and click Next. Select ‘Mahogany’ as the style and click Finish.

7. The chart looks as follows after increasing the size, when you run the report.

8. Let us give chart titles, labels and other details to the chart. Enter chart title as “Global Percent for film stars”. Right click on the chart area and set data labels on. Again select the data labels and go to the properties. We will display film star name along with the percentage for rating. Enter the following expression in property for label :

= "#LEGENDTEXT" & vbcrlf & " #PERCENT{P0}"

9. The result is as follows:

You will also observe that the tooltip shows the sum of global rating, if pointed to any percent.

Feedback: