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.

Monday, January 12, 2009

SQL Server 2008 Community Launch, Kandy, Sri Lanka

We did it. SQL Server 2008 Community launch in Kandy, Sri Lanka was successful beyond our expectation. Success was due to two reasons. One, number of participants and two, their enthusiasm.
Around 80 developers, software engineers, tech leads, IT lectures, students participated in the event even though it was a Poyaday. Most encouraging thing is that their participation in the sessions.
We started on time but couldn't finish on time mainly due to the several reasons but most of the participants were there till the end.
We were invited to do regular presentations in Kandy and we are looking into the possibilities of conducting frequent sessions in future by joining hands with other two communities, dotnetforum and ITPro.
On personal note. this is he first session I did outside the city of Colombo.
We need to thank few people and organizations who supported us immensely. First of all, Microsoft who developed fantastic database SQL Server 2008 and Microsoft Sri Lanka and Wela for giving us the financial help to held the event. Red-gate and Rachel Hawley, Marketing Communities who sponsored valuable gift items to distribute. Red-Gate does not need a second invitation to support and they kept that trend again. ESoft donated 30% Microsoft Exam Class vouchers and they ensured that every one was rewarded for their precious participation. Dr. Sandagirigama, Mr. Akanka Maillawa and other support stuff at Peradeniya university who helped us by providing EOF Perera hall and made sure that everything was in right order. Last but not least, the participants. Without these people event was not a reality. They came to Peradeniya university despite the poyaday and hope they got your time worth.
Event highlights in images will be published in shortly.
Next we need to plan for the Colombo event. Few things will change and will update when the things are get finalized.

Tuesday, January 6, 2009

Is Your Job Safe?

New year, 2009 begun with major hiccups in the world economy. As economists predict worse is yet to come. Bernard Baumohl, chief economist at the Economic Outlook Group said "the job market will continue to deteriorate for the next couple of months,"

Baumohl expects monthly job loses of 550,000 to 600,000, but "it may very well exceed 600,000 too because of the severity and speed this economy is sinking into a recession," he said.Baumohl predicts unemployment rate will be 8.5% by December 2009. Baumohl said the jobless rate will peak close to 10% by the end of next year or early 2010.

This is a quick exercise to do a status check on the "safety" of your job. The questionnaire is in no way complete. The focus is to make you think beyond the "job responsibilities" outlined in your offer letter.

Monday, January 5, 2009

SQL Server 2008 Community Launch - Kandy

We, SQL Server MVPs are organizing Community Launch of SQL Server 2008, next week in Kandy with the support of Microsoft Sri Lanka, Red-gate and SQLServerUniverse. SQL Server MVPs (Dinesh Priyankara, Gogula and my self) and Preethi (One of experienced DBA with vast knowledge) will be delivering; What's new in SQL Server 2008. If you are in Kandy, join with us.
We have planned to do a another launch in Colombo, on January 24th.

Friday, January 2, 2009

How to create a Link Server for mySQL in SQL Server Management Studio (SSMS) in SQL

1. Download the MySQL ODBC driver from mysql site.
2. Install MySQL ODBC driver on Server where SQL Server installed.
3. Creating Linked server at SSMS
i. Expand Server Objects Node
ii. Right click Linked Servers and select New Linked Server
iii. Default page will be General Page,
iv. At Link Server , type the name of your link server
v. Select Other data source option
vi. At Provider select Microsoft OLE DB Provider for ODBC Drivers
vii. At Product Name type any label
viii. If you want to link MySQL with a DSN type the name of the previously created at the Data Source
Driver={MySQL ODBC 3.51 driver};Server=localhost; DataBase=DBNAME; Root=3306;UID = root;PWD = password
Make sure that you don't have spaces before ;

ix. At the Security Page, Map a login to the Remote User and provide the Remote Users’ password
x. At the Server Options page, set RPC and RPC Out to true

4. Modify Properties of the MSDASQL Provider
i. Expand Providers Node
ii. Right Click MSDASQL and Select Properties
iii. Enable
a. Nested Queries
b. Level zero only

c. Allow inprocess
d. Supports “Like” Operator

5. Modify settings in SQL Server Surface Area Configuration (SSSAC)
i. Enable OPENROWSET and OPENDATASOURSE support from SSSAC for features
ii. Enable Local and Remote connections via TCP/IP and named Pipes from SSSAC for Services

6. Restart SQL Server and SQL Server Agent Services
7. Accessing Data from MySQL
You can access MySQL linked server data from simple Select command as well as by using OPENQUERY method


Note the triple dot notation. That tells the SQL SERVER to use database owner and default catalog.

SELECT * FROM OPENQUERY(LINKEDLUCISION, 'select * from mydatabase.tablename')

Thursday, January 1, 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 the UDDT
3. Create new UDDT
4. Allocate new UDDT to the fields which had previous UDDT.
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
AND Domain_schema = @udtschema
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
AND Domain_schema = @udtschema
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

Happy New Year

Happy New Year to all of you

Happy New Year

In this new year, may you, relax more spontaneously, feel less caution, have fever worries, greater laughter than ever before.Wish you a happy and peaceful new year!