Thursday, December 31, 2009

Vote for Service Packs

Service Pack 4 for SQL Server 2005 is due about now, but there has been no word from Microsoft. If you are running servers on SQL Server 2005, vote for Service Pack 4. Also, vote for Server Pack 2 and demand the level of support and service we deserve for our platform. Vote here:

Saturday, December 26, 2009

BUG: Connecting to SSAS from Visio 2010

You can connect to SQL Server Analysis Services (SSAS) from Visio to generate certain reports. In Visio 2007, you have the option got Microsoft SQL Server Analysis Services.


However, in Visio 2010, this is the same dialog box.


In this, you can see that SSAS is missing

Thursday, November 26, 2009

Introduction to SQL Azure

SQL Azure is still at early stages. This article is to give an introduction the SQL Azure.

Friday, November 20, 2009

Could not find stored procedure 'sp_filestream_configure'

Filestream is a new feature that came along with SQL Server 2008. You need to enable FileStream before using it.

If you google, syntax for enabling FileStream is EXEC sp_filestream_configure @enable_level = 3;

However, you will end-up with Could not find stored procedure 'sp_filestream_configure' error.

Above syntax if for CTPs not for RTM!. So the correct syntax follows.

USE master;

EXEC sys.sp_configure N'filestream access level', N'2'




Wednesday, November 18, 2009

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Following error is occurring while trying to access report server or report manager in SQL Server Reporting Service 2005.

An ASP.NET setting has been detected that does not apply in Integrated managed pipeline mode

Reporting Services in SQL Server 2005 runs as on IIS 7.0. After configuring Reporting Service, two virtual directories will be created by default their Application pool is DefaultAppPool.

Every Application pool has Pipeline mode and there are two types of Pipeline modes namely, Integrated and Classic.

If the Pipeline mode of the Application Poll attached to virtual servers is Integrated you will get the above error.

You need to change the Integrated to Classic as shown below in IIS.


You do not have to restart neither IIS nor SQL Server Reporting Services.

There is another method. You need to create new application pool with Classic and attached that Application pool to the both virtual servers from Reporting Server Configuration Tool.

Friday, November 13, 2009

Spelling Mistake in Back Up Database Task of SSIS

In the SSIS task there are Maintenance Plan Tasks. Under the Maintenance Plan Tasks, there is a task called Back Up Database Task to backup databases. After drag and dropping the task to the control flow, press F4 to get properties. There is a property called TaskAllowsDatabaseSelection which is mis-spelled as shown in the following image.


Monday, November 2, 2009

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

Last week Sentrigo brought a security hole in SQL Server to my attention. The issue is simply that SQL Server stores passwords in clear text in memory, so it’s quite possible to sniff the memory to retrieve passwords of other users. That in itself doesn’t sound dangerous at first glance because you have to have system admin rights in the database to do this. However, there are two basic situations where this could be horrendous.

Because most users have the same password, once you gain access to this password, you’ve gained access to all the boxes that account is on. This works on only native SQL passwords, not on Windows passwords. This is because when you use a Windows account to log into SQL, you don’t give it a password; you only pass in your security token, and there’s no password to steal. With SQL passwords, however, you pass in the SQL username and password, and this combination is stored in clear text in memory.

read more

Friday, October 30, 2009

Connecting to a Database without Entering Parameters Every time from SSMS

If you are using SSMS regularly, you know that you have supply database name every time. You can avoid this by supplying a default database for a user. However this is not recommended because in case that database is dropped or renamed, user will not be able to login to SQL Server and will receive this error.

Cannot open user default database. Login failed.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 4064)

SQL Server Management Studio is executed from a exe called SSMS which resides by default at  "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

For this you can supply server name and database name as following.

"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe" -S Dinesh-PC -D AdventureWorks2008

You can create a short cut with above values and when ever you click the short cut SSMS will database as AdventureWorks2008. However, problem with this is that, Object Explorer will not load objects unless you use conventional connect dialog box.

Delete Old Backup Files

If you have used SQL Server 2000, you know that in SQL Server 2000, deleting previous backup files are easy. In the SQL Server 2000 , the Specify Backup Disk Directory you simple have to specify the period and the extension as shown in following figure.


Where is this option in SQL Server 2005? Well, this is FAQ in many forums and therefore, thought of writing this blog post.

In SQL Server 2005, if you drag and drop the Back Up Database Task, you will get the options to create backup file. However, there won’t be any place to enter remove backup option as in SQL Server 2000.


Because of this, many users are using scripting mechanisms to delete them. However, if you want to delete files inside subfolders, the script will become complex.

In the tool box of Maintenance Plan Tasks there is another task called Maintenance Cleanup Task which can be used to delete old backup as shown in following image.


After configuring this task, you need to connect the task to the backup task as shown in following image.


Thursday, October 15, 2009

Changing Parameters for Select Top and Edit Top

In SQL Server 2008, Object Explorer of the SQ Server Management Studio, there are couple of options which are highlighted in the following image.


In SQL Server 2005, there was an option named Open Table, which will open entire table. If that table has lot of rows and columns it will take quite lot of time and resources.

However, in SQL Server 2008, Select Top 1000 Rows will list first 1000 rows and Edit Top 200 Rows will give you the option of editing only first 200 rows.

Problem is how to edit these numbers in case of a need.


In the option menu item of the SQL Server Management Studio, you can change those values as shown in the above diagram. If you want to get all the all the rows, you need to type 0 at the desired place.

Also, this values will be immediately available upon clicking the ok button thus you don’t have to restart SQL Server Management Studio.

Friday, October 9, 2009

Error when modifying table from SQL Server Management Studio in SQL Server 2008

In SQL Server 2008, when you change table from SQL Server management studio, if that change needs table to be dropped and re-created, it will return and error and change will not be affected. Error is shown in following image.


This will effect operations like changing int column property to identity and changing varchar column to int etc.. In case of table recreation, it can take lot of time if there is lot of records in the tables.

However, there is an option to allow these changes to occur.

Select Options from the Tools menu. In that dialog go to Designers option and de-select highlighted option. By default this option is selected.


Monday, June 29, 2009

Country’s ‘Most Valuable Professionals’ dedicated to serving local IT community

This article is about Sri Lankan MVP and my views are also taken into this. Read the full article at

Monday, June 1, 2009

Change Tracking in SQL Server 2008

SQL Server 2008 includes  several inbuilt features for Auditing – namely Change Data Capture (CDC) and Auditing and Change Tracking - this article we are going to discuss  Change Tracking.

more at

Friday, May 29, 2009

Two Books on SQL Server 2008

I had the privilege of becoming a contributing editor for two SQL Server 2008 books published by Syngress. Those books are The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design  and The Real MCTS SQL Server 2008 Exam 70-432 Prep Kit: Database Implementation and Maintenance.

I was involved around seven books to date and this is the first time one of my books are sold in Amazon. 

Working with Sinhala Letter in SQL Server

Being a Sri Lankan and SQL Server professional, I would love to see Sinhala font in SQL Server.

In SQL Server 2005 there is a matching collation for Sinhala called Indic_General_90_BIN. In SQL Server 2008,  there is a collation called Indic_General_100_BIN




      NAME NVARCHAR(50) COLLATE Indic_General_90_BIN


INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අඹ')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කොස්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නාරං')           

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'දං')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'ඇපල්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  (N'කෙසෙල්')

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අන්නාසි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'ලාවලු') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'කැකිරි') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'රඹුටන්')     

INSERT  INTO [Fruits] ( [NAME] )VALUES  ( N'දෙලුම්') 

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'නෙල්ලි')     

INSERT  INTO [Fruits] ( [NAME] ) VALUES  ( N'අලි පේර')


You can order them by the Sinhala alphabet. 



It also works with normal searching operations such as = and like.

SELECT * FROM Fruits WHERE NAME = N'රඹුටන්'






Wednesday, May 27, 2009

Restoring a Database with Symmetric Encryption

Encryption was introduced into SQL Server with SQL Server 2005. This series of articles will tell you how to implement Encryption to protect your valuable data.

This blog is to how to restore a database to another server.

1. Backup the database on the [SOURCE] server.

2. Backup the service master key on the [SOURCE] server.

     ENCRYPTION BY PASSWORD = 'pa$$w0rd'

3. Restore the service master key on the [DESTINATION] server.



4. Restore the database on the [DESTINATION] server.

Thursday, May 14, 2009

Collation, Danish_Norwegian Vs Latin1_General

Collation is a clause that can be applied to a database definition or a column definition to define the collation, or to a character string expression to apply a collation cast.  We all think that Collation is effected for Sorting and Case Sensitive data.

 CREATE TABLE t ( c char(2) )








--Result 1


--Result 2


Following are the two result sets.

a a
B Aa
Za B
Aa Za

You can see that in the in the both result sets only difference is placement of Aa.

If you run following two statements, things will be quite annoying.

--Result 3


* FROM t WHERE c LIKE 'A%' COLLATE Danish_Norwegian_CI_AS

--Result 4


* FROM t WHERE c LIKE 'A%' COLLATE Latin1_General_CI_AS

Result 3 will return 2 rows, a and A and Result 4 will return a, A and Aa. Which means that Aa is missing in Danish_Norwegian collation. Above results were obtained from SQL Server 2005 SP3 and these results are same with SQL Server 2008 SP1.

Wednesday, May 13, 2009

Error when SSRS Report Running from a .Net Report Viewer

When report viewer report added to the .Net application and report path is set to a deployed report to the report server following error message will occur.

The permissions granted to user '<DOMAIN>\IUSR_<DOMAIN>' are insufficient for performing this operation. (rsAccessDenied)

  1. Open Internet Explorer.
  2. Type the following in the Address bar:
    http:// application-tier/Reports/Pages/Folder.aspx 
    You can find the name of the report server by opening Team Explorer, expanding the Reports node, and viewing the properties of a report.
  3. Click the Properties tab and then click New Role Assignment.
  4. In Group or User Name, and add the Windows logon name for the person you want to add to this group.
  5. In Role, select Content Manager, and then click OK.

Sunday, May 10, 2009

Report Builder is not Launching

In SQL Server Reporting Services (SSRS) 2005 clicking the Report Builder option in Report Manager does not launch Report Builder Tool

You need dotnet framework 2.0 in your client machine to launch Report Builder. Verify whether you have installed dotnet framework 2.0. If it is already installed check the application log from the event viewer and verify whether there are any errors relevant to the Report Builder.

Tuesday, April 21, 2009

Error When Configuring SSRS Database

Error is occurring when trying to configure SSRS Database Setup from the Reporting Service Configuration Manager. Following is the error you get.

The database version (C.0.8.40) does not match your reporting services installation.  You must upgrade your reporting services database.
Couldn't generate the upgrade script.  There is no upgrade script available for this version.


Error is occurring when it can't match with the database version of your reporting service installation. This is happening when you install you components in following order.

1. Install SQL Server 2005 Database Services.

2. Install SQL Server 2005 Service Pack 3.

3. Install SQL Server Reporting Services.

Because of this, SQL Server Report Services and Database Services versions are mismatching. Therefore, you need to install SQL Server Service Pack 3again so that both versions are equal.

SQL Server Reporting Server (SSRS) Service is Failing to Start

After a server reboots the SQL Server Reporting Server (SSRS) service is failing to start giving following two errors in the event log.

1. The SQL Server Reporting Services (MSSQLSERVER) service failed to start due to the following error:

The service did not respond to the start or control request in a timely fashion.

2. Timeout (30000 milliseconds) waiting for the SQL Server Reporting Services (MSSQLSERVER) service to connect.

This issue may occur if the service times out before it starts successfully. This issue is more likely to occur if your computer is heavily loaded.

To resolve this issue, increase the time-out value for service startup process. When you increase this value, the Microsoft ISA Server Storage service has more time to load when the computer starts. To increase the service startup time, create the following registry entry:

Subkey: HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control

Name: ServicesPipeTimeout


Data: The number of milliseconds that you want to give the services to start in

Typically, a data value of 35,000 is sufficient to keep the service from timing out. However, you can reduce or increase this value according to your specific startup requirements. For example, to use a time-out value of 60 seconds, assign a data value of 60,000 to the ServicesPipeTimeout registry entry. A larger data value does not decrease your computer's performance. To create this registry entry, follow these steps:

1. Click Start, click Run, type regedit, and then click OK.

2. Locate and then click the following registry subkey:


3. Right-click Control, point to New, and then click DWORD Value.

4. In the New Value #1 box, type ServicesPipeTimeout, and then press ENTER.

5. Right-click ServicesPipeTimeout, and then click Modify.

6. Click Decimal, type the number of milliseconds that you want to wait until the service times out, and then click OK.

For example, to wait 60 seconds before the service times out, type 60000.

7. Quit Registry Editor, and then restart the computer.

Thursday, April 16, 2009

OUTPUT in SQL Server 2005

This small video describes how to use Output command in SQL Server 2005.

Monday, April 13, 2009

How Send the SSRS Report From SSIS?

This is a requirement when you need to send a SSRS report in Excel, PDF format to different users from SSIS after performing a data load.

First you need to create subscription to the report. You can create a SSRS report subscription from Report manager. At the report subscription you can mention the report format and the email address of the recipient. When you create a schedule for the SSRS report, SQL Server Agent Job will be created.

From the SSIS, by using sp_start_job and passing the relevant job name you can execute the SSRS report subscription.

Saturday, April 11, 2009

How to uninstall SQL Server 2005 manually

Sometimes, you may find that, though you can't see any SQL Server 2005 instance in the programs list and when try to install the SQL Server 2005, it gives messages saying that there is SQL Server instance or some components like SQL Server Management Studio. Reason for this is existence of corrupted SQL Server instance and you need to remove the SQL Server instance manually.

At a command prompt, run the following command:
"%ProgramFiles%\Microsoft SQL Server\90\Setup Bootstrap\ARPWrapper.exe /Remove"

Uninstall the SQL Server components one at a time until all the SQL Server components are uninstalled.

Note Add or Remove Programs also runs the ARPWrapper.exe program by using the /Remove option. However, the reference to the ARPWrapper.exe program may have been deleted. Make sure that all the services (if exists) relevant to this SQL Server instance is stopped.

Wednesday, April 8, 2009

SQL Server 2008 SP1 Download Available

You can download SQL 2008 SP1 here.  If you get a 404 error, come back and try it again.  Seems that the download servers aren’t all synced up.

The April 2009 update to the SQL Server 2008 Feature Packs is also available today. In this release few bugs were fixed.

Tuesday, April 7, 2009

SQL Heart Beat

There are plenty of tools to monitor your SQL Server Database engine. However, there are hardly any tools to measure performance of SQL Server Analysis Service (SSAS) database.

To fill this gap in the industry company named SQL Solutions who delivers top of the line database performance tools and specialized consulting services for the Microsoft SQL Server platform, has come with a FREE tool called SQL Heart Beat.

Basic features of SQL Heart beat are,

  • Monitor server wait categories
  • Monitor IO activity
  • Check your current Cache Hits ratio
  • Analyze your I/O system performance
  • Get details about active processes
  • Display locks and deadlocks

Following image shows how you can measure/views these from the SQL Heart beat tool.


Also, you have the option of measuring these performance of multiple SSAS servers from a single instance of a SQL Heart beat.

You can download this tool and experience the benefits.

Wednesday, April 1, 2009

Using Merge Command in SQL Server 2008

This video will show you how to use Merge Command in SQL Server 2008.

Tuesday, March 31, 2009

How to alter a User Defined Data Type?


The only way to do it is to create a new User Define Data Type (UDDT), and change out all existing column to that UDDT, then you can drop the original one, and recreate it and change out the change you made previously. The problem is that you can drop the UDDT if it is in use.(PN: this is same with SQL Server 2008)

In case of the UDDT is in use you need to follow these steps.

1. Allocate different data type for the fields which are using UDDT. Rather than allocating arbitrary data type it is better to allocate data type of the UDDT, so that there won’t be any issue with the existing data.

2.Drop Stored Procedures which are using UDDT. You can drop them by using following T-SQL. However, you need to get the scripts of those SPs to create them later.

DECLARE @dropsps VARCHAR(8000)
SET @dropsps = ''
SELECT  @dropsps = @dropsps + ',' +
FROM    sys.syscomments
WHERE   TEXT LIKE '%latitude%'
        AND OBJECT_NAME(id) LIKE 'usp%'
SET @dropsps = 'DROP PROC ' + RIGHT(@dropsps, LEN(@a) - 1)
EXEC ( @dropsps


3. Drop the UDDT

4. Create new UDDT

5. Allocate new UDDT to the fields which had previous UDDT.

6. Create Dropped Stored Procedures

You can use following script and you can change the first variable value according to your requirement. This script was tested for few data types.



DECLARE @udtschema VARCHAR(150)

DECLARE @newudtschema VARCHAR(150)

DECLARE @newudtDataType VARCHAR(150)

DECLARE @newudtDataSize smallint

DECLARE @OtherParameter VARCHAR(50)

SET @udt = 'Name' -- Existing UDDT

SET @udtschema = 'dbo' -- Schema of the UDDT

SET @newudtDataType = 'varchar' -- Data type for te new UDDT

SET @newudtDataSize = 500 -- Lenght of the new UDDT

SET @newudtschema = 'dbo' -- Schema of the new UDDT

SET @OtherParameter = ' NULL' -- Other parameters like NULL , NOT NULL

DECLARE @Datatype VARCHAR(50),

@Datasize SMALLINT

DECLARE @varcharDataType VARCHAR(50)

DECLARE @Schemaname VARCHAR(50),

@TableName VARCHAR(50),

@FiledName VARCHAR(50)



Schemaname VARCHAR(50),

TableName VARCHAR(50),

FiledName VARCHAR(50)



@Datatype = Data_type,

@Datasize = character_maximum_length


WHERE Domain_name = @udt

SET @varcharDataType = @Datatype

IF @DataType Like '%char%'


AND ( @newudtDataType <> 'varchar(max)'

OR @newudtDataType <> 'nvarchar(max)'



SET @varcharDataType = @varcharDataType + '('

+ CAST(@Datasize AS VARCHAR(50)) + ')'


INSERT INTO #udtflds





WHERE Domain_name = @udt


DECLARE alter_cursor CURSOR

FOR SELECT Schemaname,



FROM #udtflds

OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName



SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName

+ ' ALTER COLUMN ' + @FiledName + ' ' + @varcharDataType

EXECUTE ( @exec


FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName


CLOSE alter_cursor

SET @exec = 'DROP TYPE [' + @udtschema + '].[' + @udt + ']'

EXEC ( @exec


SET @varcharDataType = @newudtDataType

IF @newudtDataType Like '%char%'

AND @newudtDataSize IS NOT NULL

AND ( @newudtDataType <> 'varchar(max)'

OR @newudtDataType <> 'nvarchar(max)'



SET @varcharDataType = @varcharDataType + '('

+ CAST(@newudtDataSize AS VARCHAR(50)) + ')'


SET @exec = 'CREATE TYPE [' + @newudtschema + '].[' + @udt + '] FROM '

+ @varcharDataType + ' ' + @OtherParameter

EXEC ( @exec


OPEN alter_cursor

FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName



SET @exec = 'Alter Table ' + @Schemaname + '.' + @TableName

+ ' ALTER COLUMN ' + @FiledName + ' ' + '[' + @newudtschema

+ '].[' + @udt + ']'

EXECUTE ( @exec


FETCH NEXT FROM alter_cursor INTO @Schemaname, @TableName, @FiledName


CLOSE alter_cursor

DEALLOCATE alter_cursor


FROM #udtflds

DROP TABLE #udtflds

Thursday, March 19, 2009

Application Name & SQL Server Profiler

SQL Server Profiler is a tool which you can use to gather information about what are the events happening at the SQL Server.  Following is the sample which you will get from the Profiler Trace file.


If you pay attention to the Application Name Column (3rd Column form your left hand side), You can see there are entries. For the First four rows, you can see a SQL Prompt and next four lines you will see Microsoft SQL Server Management Studio. This is due to the fact that those applications are using this particular instance of SQL server. If you look closer, you will see that next 3 lines has a entry name .Net SqlClient Data Provider.

Obvious question is what is this application. Well, this is because Application Name is not specified by the application. You have to set it from the client application by adding another parameter to the connection string.

string connectionstring = "Data Source=.; Integrated Security=SSPI; Initial Catalog=DB;Application Name=MyApplication";

NB: Whenever the Application Name is not specified it will use default application Name, .Net SqlClient Data Provider.

After if you analyze the Profiler trace again you will see the correct application name.


This very important option as this will allow you to analyze your Profiler Trace for application wise. Also, you can use filter option for Application Name in Profiler to filter by application.

Database Implications if IBM Acquires Sun

Reported or rumored merger discussions between IBM and Sun are generating huge amounts of discussion today (some links below). Here are some quick thoughts around the subject of how the IBM/Sun deal — if it happens — might affect the database management system industry.

  • IBM is already serious about supporting multiple database management systems. DB2 on open systems is IBM’s flagship DBMS. But DB2 on mainframes and at least one flavor of Informix seem to be getting maintained and enhanced fairly seriously as well. And IBM has further DBMS products as well (e.g., DB/2 on the AS/400). There’s little reason to think IBM would orphan MySQL or any other DBMS product.
  • IBM is very open-source-friendly. For a company that grew up for decades on proprietary software — and still is a huge software products vendor — IBM is very serious about open source. If you doubt that, I have two words for you: “Linux” and “Eclipse”.
  • MySQL might finally get its industrial-strength act together. IBM is good at database management and good at open source. MySQL becoming a no-apologies transactional DBMS would obviously put pressure on Ingres, PostgreSQL, and EnterpriseDB, although there surely would be lots of happy talk about the open source DBMS market being validated, lifting all the vendors and so on. Also, a better MySQL could be bad news for Microsoft SQL Server too.
  • Sun has a lot DBMS partnerships right now. Obviously, Sun owns MySQL, and has partnerships with MySQL storage engine vendors such as Infobright and Kickfire. Sun also has a substantial partnership with Greenplum, and a Barneyesque* one with ParAccel. And of course Sun has strong working relationships with major database vendors such as Oracle and Sybase. What’s more, on a case-by-case basis, Sun may cooperate in the field with yet other DBMS sellers. E.g., I’ve confirmed at least one instance of a Sun sales rep recommending a Kognitio DBMS.
  • IBM partners with outside DBMS vendors too. You’d think IBM’s gazillion DBMS product lines would be enough. But nooooo. I frequently hear rumblings of IBM’s hardware or services operations working with other DBMS products as well. (This is, of course, actually to their credit.)
  • Short-term, there probably would be little effect on partnerships. Greenplum runs on Sun’s Thumper/Thor line of boxes. DB2 doesn’t, and certainly isn’t optimized for same. In the short term, to sell Thors, Sun would presumably continue to sell Greenplum.
  • Longer-term, there could be a DBMS rationalization. DB2, Informix, MySQL + storage engines, and big independent vendors such as Oracle and Sybase would surely always get attention. That’s a lot. There might not be room for much mind share for many database products and vendors beyond that list.

*A Barney partnership is one in which two or more vendors get on stage and do a song and dance about how much they love each other, with little substance beyond that.

Related links


Tuesday, March 17, 2009

Multiple CTEs in a Single T-SQL Statement

What is CTE

From BOL,

Common Table Expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

However, in BOL there is no example, how to write a single T-SQL statement with multiple CTEs. hence I have seen lot of queries about this in many forums. Following is the format you should use.




Syntax for CTEA


CTEB (ID,Col2)



Syntax for CTEB





Notice that two CTEs are separated by , and for the second CTE you SHOULD NOT use WITH statement.

Here is an example, where you want to compare sales values between year 2003 and 2004 for each product. 

WITH Prod2003 (ProductNumber,Amount)
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2003
GROUP BY Prod.ProductNumber),
Prod2004 (ProductNumber,Amount)
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2004
GROUP BY Prod.ProductNumber

SELECT Prod2003.ProductNumber,
Prod2003.Amount Sales2003,
Prod2004.Amount Sales2004 ,
Prod2004.Amount - Prod2003.Amount SalesIncrement
FROM Prod2003
INNER JOIN   Prod2004
ON Prod2003.ProductNumber = Prod2004.ProductNumber

Output for the above query is displayed in following image.


Thursday, March 12, 2009

Start Data Collector Set of Reliability & Performance Monitor from C#

Reliability & Performance Monitor a.k.a perfmon is a valuable tool to have for a Database Administrator, as it provides various counters to measure your system with defined time interval.

Data Collector Sets (Similar to Counter Logs in Windows 2003 and XP or before versions)  will give you the opportunity to collect these data to file so that it can be analyzed later.


In the Data Collect Set, you need to start the defined Data Collector Set. Problem here is if you want to analyze something soon after you do it, there will be a time delay. For example, if you want to measure % of Processor soon after the execution of a stored procedure. Normally, you need to execute the stored procedure and manually start the Data Collector Set which lead to some delay. Instead, you can include both the executing the Stored Procedure and starting the Data Collector Sets inside a C# or application.

So the next question is, how to start Data Collector Sets from C#. Though you have perfmon with previous versions of OS you can start Data Collector Sets from C# only with Vista and Windows 2008.

First, add PLALibrary to the reference from the %windir%\System32\PLA.dll and include using PlaLibrary; at the top of your code. Following code will start, Data Collector Set named Page Split.

private void Start_PLA()
            IDataCollectorSet cs = new DataCollectorSetClass();
            string name = "Page Split";
            cs.Query(name, null);

Similarly, there are many functions available with PLALibrary for other operations like, Create and Stop Data Collector Sets.

Friday, March 6, 2009

Integrating perfmon and profiler

perfmon and profiler are valuable tools for DBAs to monitor your SQL Server instance. In SQL Server 2005 you have the option of integrating these two tools. Following video show how to integrate them.

Video1 Video2 Video3 Video4 Video5 Video6

This is my second video and it took me more than just pain to get this video done. Initially I couldn’t get this video for one avi file. Then I did the video for 3 videos and found a simple tool to integrate them. After integrating them, final file size was 500+ MB, which was not possible to upload. Then found another tool to split them. After all manage to find to split into six avi files.

As said before, I am not a flashy presenter and please forgive any Language errors. Please send your comments.

Friday, February 27, 2009

Two Books from SQL Server Central

SQL Server Central, most popular web site on SQL Server, has released two books called Best of SQLServerCentral, Volume 5 and Best of SQLServerCentral, Volume 6. Two articles of mine, The OUPUT Command (Vol 5) and SSAS Database Backup (Vol 6) are in those books.



Sunday, February 22, 2009

Visio & SSAS

Do you know that you can connect to a SSAS database from Microsoft Visio. Yes from Visio. Most people don’t. If you are a one of them falling to don’t category, my latest article is for you.

Tuesday, February 17, 2009

Warren Buffets Advice for 2009

We begin this New Year with dampened enthusiasm and dented optimism. Our happiness is diluted and our peace is threatened by the financial illness that has infected our families, organizations and nations. Everyone is desperate to find a remedy that will cure their financial illness and help them recover their financial health. They expect the financial experts to provide them with remedies, forgetting the fact that it is these experts who created this financial mess.

Every new year, I adopt a couple of old maxims as my beacons to guide my future. This self-prescribed therapy has ensured that with each passing year, I grow wiser and not older. This year, I invite you to tap into the financial wisdom of our elders along with me, and become financially wiser.

* Hard work: All hard work bring a profit, but mere talk leads only to poverty.

* Laziness: A sleeping lobster is carried away by the water current.

* Earnings: Never depend on a single source of income. [ At least make your Investments get you second earning ]

* Spending: If you buy things you don't need, you'll soon sell things you need.

* Savings: Don't save what is left after spending; Spend what is left after saving.

* Borrowings: The borrower becomes the lender's slave.

* Accounting: It's no use carrying an umbrella, if your shoes are leaking.

* Auditing: Beware of little expenses; A small leak can sink a large ship.

* Risk-taking: Never test the depth of the river with both feet. [ Have an alternate plan ready ]

* Investment: Don't put all your eggs in one basket.

I'm certain that those who have already been practicing these principles remain financially healthy. I'm equally confident that

those who resolve to start practicing these principles will quickly regain their financial health.

Let us become wiser and lead a happy, healthy, prosperous and peaceful life.

Saturday, February 14, 2009

Debugging SQL Server Stored Procedures

If you are a developer, it is needless to stress the importance of debugging you complex codes. In Visual Studio, you have the luxury of debugging you codes with fancy features. Now you can integrate SQL Server Stored Procedures to Visual Studios to debug those. See the step by step information from the following six videos.

Video1 Video2 Video3 Video4 Video5 Video6

Thursday, February 12, 2009

Running SQL Profiler without sysadmin Privileges

In SQL Server 2000, if you wish to run SQL Profiler you need grant sysadmin permission to the user. This was not encouraging for the system administrators.  In SQL Server 2005, you have the option of granting permission only for SQL Profiler, so that the users should not be a member of sysadmin group.

USE master


In case you need to revoke the permission, you granting permission only for SQL Profiler.

USE master


Wednesday, February 4, 2009

SQL Server BI Comes into Fashion

Apranga Group, the leading fashion retailer in the Baltic States, has contracted Microsoft partner New Vision to implement its business intelligence solution based on ProfitBase and Microsoft SQL Server.

Apranga Group currently operates 71 stores in Lithuania, 21 in Latvia and seven in Estonia. The company was experiencing a challenge in selecting a single standardized business intelligence (BI) platform. A short implementation time was a top priority, as Apranga had a very strict deadline. The ability to modify and extend the deployed BI solution easily was another important requirement.

Following an extensive evaluation process, Apranga Group contracted Microsoft partner New Vision to implement its business intelligence solution which uses Microsoft SQL Server. This BI solution is based on Association for Retail Technology Standards (ARTS) templates from ProfitBase. The templates enabled Apranga to kick-start the BI solution rapidly, saving time on analysis and implementation, minimizing risks and reducing costs. New Vision’s competence in Microsoft Dynamics NAV combined with data connectors from ProfitBase saved a lot of effort loading a data warehouse. Integrating historic legacy data sources was easy and the customer had an Online Analytical Processing (OLAP) system finished in only two and a half months.

Following the implementation of the BI solution, Apranga Group is able to analyze sales and inventory in a single BI system using SQL Server Reporting Services and Office Excel Pivot Tables tools. Previously the same data was analyzed by several different reporting and BI systems.

The standardized BI solution at Apranga Group now covers all stores and chains it operates and contains nine years of historic data. The solution has become a central point of information for many employees. The company is planning to extend the BI solution with new functionality according to their business needs.

This article first appeared in the spring 2009 edition of Retailspeak magazine.

Tuesday, February 3, 2009

Accessing Cubes from Excel 2007

Microsoft Excel can be used to access SSAS cubes databases. Latest article by me written on is to explorer features of Excel to connect to SSAS cubes.

Monday, February 2, 2009

Book on SQL Server Replication

Two articles of mine on replication DDL Replication in SQL Server 2000 and 2005 and Peer-to-Peer Transactional Replication in SQL Server 2005 are selected for a SQL Server Replication book. This book is published by E-COM and book is available for sale. This book is in Russian and i would like to thank Alexander Gladchenko  for giving me this opportunity. I will get a copy of this book though I can’t read it :).

dbfriend Video Center

I have started creating on SQL Server and those are published at To start with, I have created a short video on how to display SSRS tables with different row colors.

I am not a flashy presenter and these video may not be in super quality. However, I am trying to improve and sorry for any inconvenience.

Friday, January 23, 2009

Microsoft To Cut 5,000 Jobs As Profits Fall 11%

Total sales at the Microsoft were up a marginal 1.6% year over year in the second quarter, to $16.63 billion, but Microsoft's net income slumped 11% during the period, to $4.17 billion. Earnings per share fell 6% to 47 cents.

Revenues from sales of the Windows operating system dropped 8%, to $3.98 billion. In its report, filed Thursday with the Securities and Exchange Commission, Microsoft blamed the decline on "PC market weakness and a continued shift to lower-priced Netbook PCs." Netbooks are inexpensive, low-powered laptops used mostly for e-mail and Web surfing. Many models are not capable of running Vista, the current version of Windows.

But Windows' problems can't be blamed entirely on the recession. Many consumers and businesses have rejected Vista , launched in January 2007, because of its steep hardware requirements, intrusive security measures, and incompatibility with older software.

By contrast, Apple earlier this week reported that sales of its Macintosh computers rose 9% in the most recent quarter, despite the economic slump. Worsening the comparison with Apple, sales of Microsoft's Zune MP3 player plunged 54% in the last quarter, while iPod sales increased 3%.

Microsoft's quarterly report revealed trouble in other key segments. Office sales to businesses rose 7%, but sales of the desktop productivity package to consumers were off 23%. Office has been hit by competition from low-cost or free alternatives, such as IBM's Lotus Symphony suite.

Online advertising sales increased 7% to $664 million, but overall revenue at Microsoft's Internet unit were flat. Meanwhile, the group posted a $471 million operating loss, nearly double the loss from a year ago. Microsoft is hoping to catch Google in the search market but efforts to date, including last year's $1.2 billion acquisition of Fast Search & Transfer, have borne little fruit.

On the upside, Microsoft said sales of server software and related tools increased 15%, to $3.74 billion, on the strength of corporate demand for Windows Server 2008 and SQL Server 2008.

Microsoft is taking drastic steps to deal with the malaise. The company on Thursday announced that it would cut 5,000 jobs across the board in departments ranging from IT to human resources. 1,400 of those positions will be eliminated by the end of the month, the company said.

Microsoft, which didn't offer future guidance, also said it would curtail spending on travel, marketing and other areas with an eye to saving $1.5 billion annually. It's also eliminating merit raises.

Source :

Tuesday, January 20, 2009

Writing From Windows Live Writer

This is my first post here from Windows Live Writer and if you wish you can download it from

Cumulative Update 3 For SQL Server 2008 is Released

Microsoft has released Cumulative Update 3 For SQL Server 2008. The fixes included in Cumulative Update are listed here. You can go here to request the Cumulative Update from Microsoft.

Monday, January 19, 2009

Display only given number of records per page in SQL Server Reporting Services 2005 (SSRS)

In SQL Server Reporting Services 2005 (SSRS), how to display only given number of records per page?
If you want to display only 20 rows per report page, it is not easy task as number of rows will depend on the font size and other printer defaults. Apart from this problem, if there are rows with lengthy text it tends to move to another line which will upset the number of rows in your report. So it is necessary to add some coding to get this feature to your reports.
Let us say, you want to have only 20 rows per page, add a group to the report with following expression.

Then select page break at the end option for the group.

Friday, January 16, 2009

Error when Inserting for Replication Stopped Databases

Question: There is a database which was enabled for replication before. After replication was when trying to insert records following error is raised. Invalid object name ‘dbo.MSmerge_contents’.
Answer: When replication is enabled, additional columns, triggers are created to your replicated articles. When you stop the replication, there are times that newly added tables are not removed. Things will get worse only partial objects are removed. For example, triggers are not dropped while tables are dropped. When trigger try to insert records to the relevant tables, it will find that tables are missing and error is raised.
To overcome this you can run sp_removedbreplication @dbname = 'dbname' and it will remove that replicated related objects from the database.

Thursday, January 15, 2009

Why Features are Decreased in SP3?

This sounds bit strange for me. Have I missed anything? let me know. This is the scenario.
I have two SQL Server database servers where I have a database with exactly similar schema. This I can assure and I checked this twice if not more. One SQL Server has SP2 applied and SP3 has applied to other. We all expect SP3 to work smarter.
Now I need to Create a view with two table and these two table have a foreign key between them. When I add two tables to the view following image shows the how the behavior in both SP2 and SP3.

In SP2 database server, it shows relation between two relational keys which is the correct way. In the SP3 database server, link has made between the two columns named ID instead of two relational keys. In SP3, I have to drop this wrong relation and then create the right one again.
Both Views were created from one SQL Server Management Studio which drop the issue of version mismatches of SQL Server Management Studio. Only thing I can see is the service packs difference and all the other configurations are same.

How to Unzip a File in SSIS?

For SSIS packages, most of the times you will get zip, rar or tar etc files to extract them during the extraction operation in SSIS. Let us see how we can do this from SSIS.
You can use Execute Process Task in Control Flow task. Drag and drop an Execute Process task to the control flow and configure as following image.

In this, you need to perform three configurations.
1. Executable: This is the path of the application you are going to use. This specific example has used Winrar.
2. Arguments: in this you need to supply the arguments to extract the zipped files. In the particular example e stands for Extract files to current directory. Then the full path name of the zipped file. –o+ is a parameter to say overwrite files if existing. This parameter will hide the conformation dialog which will come in case of an existing file.
3. Working Directory: This is the current directory for the process. In the given example test1.rar will be extract to the directory given in the Working Directory attribute.
However, Most of the time there will be several zip file to extract and path may not be defined this clearly. For this you can you for each container and include process task inside it like following image.

In the for each loop container, enumerator is Foreach File Enumerator which is the default enumerator. In the Execute process task all the configuration are same as above but with in expressions, arguments were set to "e " +@[User::currentFile] + " -o+"
Download the sample SSIS package

Checkpoints in SSIS

SQL Server Integration Services a.k.a. SSIS of SQL Server 2005 has a new feature called check points where you can restart the SSIS package from the point of failure. Check this new article on SSIS checkpoint.

Tuesday, January 13, 2009

Convert Numeric Values into Words

This is very common question that you can find in SSRS forums as many users needs to display numeric data in words. Although there is a function in Crystal Reports, there is no function in SSRS. Nevertheless, you can do this in SSRS by writing your own function.
Lets us do this by an example using the following query:
SELECT Sales.SalesPerson.SalesPersonID AS SalesPerson,SUM(Sales.SalesOrderDetail.OrderQty * Sales.SalesOrderDetail.UnitPrice) AS AmountFROM Sales.SalesOrderDetailINNER JOIN Sales.SalesOrderHeader ON Sales.SalesOrderDetail.SalesOrderID = Sales.SalesOrderHeader.SalesOrderIDINNER JOIN Sales.SalesPerson ON Sales.SalesOrderHeader.SalesPersonID = Sales.SalesPerson.SalesPersonIDGROUP BY Sales.SalesPerson.SalesPersonID
Follow the below steps to create a report.
1. Create a SSRS project.
2. Add new report the project.
3. Add a data source in which database is pointed to adventureworks.
4. Create a data set with the above query.
5. Drag and drop a table to layout from the toolbox and drag and drop the fields to the table.The next step is to create a function to convert numeric values into words.
Go to the Report menu option and select report properties. Select the Code tab in that dialog box and copy and paste the following code:
SHARED suffixes AS String() =
_{"Thousand ", "Million ", "Billion ", "Trillion ",
_"Quadrillion ", "Quintillion ", "Sextillion "}
SHARED units AS String() = _
{"","One ", "Two ", "Three ", "Four ", "Five ",
_ "Six ", "Seven ", "Eight ", "Nine "}

SHARED tens AS String() =
_{"Twenty ", "Thirty ", "Forty ", "Fifty ", "Sixty ",
_"Seventy ", "Eighty ", "Ninety "}

SHARED digits AS String() =
_{"Ten ","Eleven ", "Twelve ", "Thirteen ", "Fourteen ",
_"Fifteen ", "Sixteen ", "Seventeen ", "Eighteen ", "Nineteen"}

_System.Text.RegularExpressions.Regex("^-?d+(.d{2})?$", _System.Text.RegularExpressions.RegexOptions.None)
PUBLIC Function ExpandPrice(Price AS Double,
_Optional pSeparator AS String = ".")
_AS String
Dim pPrice As StringpPrice = FORMAT(Price,"##############.00")
Dim temp AS New System.Text.StringBuilder()
If Not expr.IsMatch(pPrice) Then
' temp.Append(pPrice) or whatever you want to do here
Dim parts AS String() = pPrice.Split(pSeparator)
Dim dollars AS String = parts(0)
Dim cents AS String = parts(1)
If CDbl(dollars) > 1 Then
temp.Append(ExpandIntegerNumber(dollars) & "Dollars ")
If CInt(cents) > 0 Then
temp.Append("And ")
End If
ElseIf CDbl(dollars) = 0 Then
temp.Append(ExpandIntegerNumber(dollars) & "Zero Dollars ")
If CInt(cents) >= 0 Then
temp.Append("And ")
End If
ElseIf CDbl(dollars) = 1 Then
temp.Append(ExpandIntegerNumber(dollars) & "Dollar " )
End If
If CDbl(cents) > 1 Then

temp.Append(ExpandIntegerNumber(cents) & "Cents")
ElseIf CDbl(cents) = 0 Then
temp.Append(ExpandIntegerNumber(cents) & "Zero Cents ")
ElseIf CDbl(cents) = 1 Then
temp.Append(ExpandIntegerNumber(cents) & "Cent " )
End If
End If
RETURN temp.ToString()
End Function

Function ExpandIntegerNumber(pNumberStr AS String) AS String
Dim temp2 AS New System.Text.StringBuilder()
Dim number AS String =
_StrDup(3 - Len(pNumberStr) Mod 3, "0") & pNumberStr
Dim i AS Integer, j AS Integer = -1
Dim numPart AS String
For i = Len(number) - 2 To 1 Step -3
numPart = Mid(number, i, 3)
If Clng(numPart > 0) Then
If j > -1 Then
End If
End If
j += 1
RETURN temp2.ToString()
End Function

Function GetNumberUnder1000Str(pNumber AS String) AS String
Dim temp1 AS New System.Text.StringBuilder()
If Len(pNumber) = 3 Then
If CLng(Left(pNumber, 1)) > 0 Then
temp1.Append(GetNumberUnder100Str(Left(pNumber, 1)) & "Hundred ")
End If
End If
temp1.Append(GetNumberUnder100Str(Right("0" & pNumber, 2)))
RETURN temp1.ToString()
End Function

Function GetNumberUnder100Str(pNumber AS String) AS String
If pNumber > 19 Then
RETURN tens(Left(pNumber, 1) - 2) & units(Right(pNumber, 1))
ElseIF pNumber >= 10 and pNumber <= 19 Then
RETURN digits(Right(pNumber, 1))
RETURN units(Right(pNumber, 1))
End If
End Function

The above code is adopted from web site in the code snippet.Next, you have to call this function in your table. You need to enter the following function call at the column in which you need to have your number in word.


Finally you will see following screen.