Error 8672 in MS SQL 2008 while using Merge statement

This is one of the weird error you get in MS SQL 2008 if you have Merge statement in your query.

Below is the complete exception message which you may receive. 

The MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows. [SQLSTATE 42000] (Error 8672).

Interesting thing about the above exception is you will get this exception even if you don’t have duplicate rows are there during the join operation. When I got this message I was checking for the duplicate rows first as the error message was highlighting that.

Any way Microsoft informed that this is a bug in SQL Server 2008. In order to resolve this issue you have to install Cumulative Update 6 for SQL Server 2008 Service Pack 1. You can download the same from Microsoft Download centre. Here is the link for “Cumulative Update 6 for SQL Server 2008 Service Pack 1”.

Microsoft created a KB article to address this issue. If you are interested to know more about this issue please refer Microsoft KB article.

I hope after installing the above updates your error is disappeared and the same query is running without any issues.

Not able to see Attach Debugger in Visual Studio for remote debugging in Azure

This is one of the common issue developer faces when they try to debug the azure webiste. In order to debug the Azure website from your local machine you should attach the debugger to your website. This “Attach Debugger” option will be visible in your server explorer. Lets us see how you can reach there first.
In order to debug the Azure website remotely first you have to open Server Explorer from the View menu. Once you click on the Server Explorer just expand the Windows Azure section. If you are not logged in to Azure account It may prompt you to login. Once you connect to your Windows Azure account you will be able to see all your websites which is already hosted in your Azure account.

How to find the files deployed in Windows Azure from Visual Studio.

While deploying the web application in Windows Azure you might be wondering where I can find these files and is there any way I can modify these files? Nothing to worry about your deployed files, we can see those files and even you can edit those files from Visual Studio.
Today we will see how to view the deployed website files in visual Studio and how to edit those files form Visual Studio.

Disadvantage of enabling Session State in all ASP.NET pages

Did you even think of disabling the ASP.NET session to improve the performance of your web page? Or Do you think enabling session in all the web page has any performance impact to your ASP.NET web application. All your such questions will be answered here today.
As you are aware session data is stored in the server if you use inProc mode. So if you have huge data in session all your server resource will be utilized. So the first thing is make sure whatever you are writing into the session is really required to be stored in session and clear the session as soon as you use the data from the session.

Attempt by security transparent method 'System.Web.Http.GlobalConfiguration.get_Configuration()' to access security critical type 'System.Web.Http.HttpConfiguration' failed.

This is one of the common error you may experience while using WEB API. This error normally occurs if you are using older version web API.
So the simple solution to resolve this issue is upgrade your WEB API.
Easiest option to upgrade your WEB API is by using NuGet package manager.

TransactionScope in ASP.NET

This is one of the good feature which will help you when you have to do multiple database operations and you wanted to make sure that all the transaction has to be either committed or roll backed as a single unit of work.
TransactionScope is exactly same as the one we use in database level. But sometimes we may not use transaction at database level if the data/table we are operating is completely different and there is no connections.
Couple of important points you have to remember while using TransactionScope is, TransactionScope class cannot be inherited, also this is part of the System.Transactions which means if you wanted to use the class TransactionScope then first you have to refer to the assembly System.Transactions.

Filtered Index in MS SQL

What is filtered index? What is the benefit of using filtered index? Does filtered index really improves the performance? All these queries will be answered in this article.
Lets start with what is filtered index? A kind of interview question.
Filtered index is an optimized non clustered index. Filtered index is more suitable for the queries which select the data from a well defined subset. Important thing you have to remember about the filtered index is it uses the filter predicate to index only a portion of rows in a table. This is what I have mentioned above that “filtered index is suitable for the queries which select the data from a well defined subset”.
Does the filtered index improve the performance?

Connect LocalDB using Visual Studio

Today let me answer few interview question and answer about local DB. We are going to cover, What is LocalDB? How to start LocalDB? How to stop LocalDB? How to connect LocalDB from visual Studio? How to create tables in LocalDB using Visual Studio.
Lets start with what is Local DB?
In earlier version Visual Studio installation SQL express edition gets installed automatically. People were really confused with this database and was having difficult time in connecting and managing this database. So from Visual Studio 2012 onwards Microsoft decided to have LocalDB engine in place of SQL Server express. This is a very light weight database with a minimum set of files required to create a database and it will directly tied up with the project it is attached to.
Lets see how to start LocalDB.

How to add additional disk in Microsoft Azure Virtual Machine

Today lets see how to add additional disk in your windows azure virtual machine. If you are a cloud architect then this will be one of the very common activity you may do periodically.
Lets start with step by step.
As usual first you have to login to your Azure account. Once you login directly go to the Virtual machine section and select the machine on which you wanted to add the additional disk space.

How to use OUTPUT Clause in MS SQL / What is the use of OUTPUT Clause?

Today we will learn some SQL. Have you ever used OUTPUT clause in MS SQL? What exactly us the use of OUTPUT clause in MS SQL? What is the advantage of using OUTPUT clause in SQL? All these questions are answered in this article.

What is OUTPUT clause in SQL?

OUTPUT clause gives you the information about each row impacted by running INSERT or UPDATE or DELETE or MERGE SQL statement. Normally in applications we use this information to return to the user in a meaningful manner to show the result of submitting the data.

What you can do is, you may save this information in a temporary table or in a permanent table itself for future use. You can use this information for auditing purpose also because it has got complete information about the sol statement which you executed row by row.

The main disadvantage of this OUTPUT clause is it returns the same information even if your SQL execution throws error or it is rolled back. So you need to make sure that such information should not be used for any auditing purpose or to pass the information to client.

Now lets see what kind of information we gets when we use OUTPUT clause.

First we will create a sample table.

CREATE TABLE [dbo].[tblBill](

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL

)

GO

Lets query the table and see the values we have inserted in this table.

Now we will delete one row with the OUTPUT clause and see the result and also the syntax to use output clause.

  delete from [DemoAzure].[dbo].[tblBill] 

  OUTPUT DELETED.*

  where travel = 150

As you can see above OUTPUT clause should be before where clause.

Once you execute this statement it will return the complete row which got deleted like a select statement. This is the reason I mentioned earlier that we can directly insert this values into a table for future use or for auditing purpose.

Lets see the result set now.

Lets see a sample code to insert the value in a table variable.

DECLARE @tblBillVar table( 

    [BillDate] [datetime] NULL,

    [Expense] [numeric](18, 0) NULL,

    [travel] [numeric](18, 0) NULL);

INSERT INTO [dbo].[tblBill] ([BillDate],[Expense],[travel]) 

OUTPUT INSERTED.[BillDate], INSERTED.[Expense], INSERTED.[travel]

        INTO @tblBillVar

VALUES ('2015-01-06 00:00:00.000',875,475)

select * from @tblBillVar

Once you execute above query you will find that the inserted values will be displayed in the query analyzer.

Similar to table variable you can have your own table as well.

I hope you are now clear about the OUTPUT clause in MS SQL.

GROUPING SETS in MS SQL

I have been doing some reporting work today. In that report I have to show some trending. In fact I have to show average salary based on the city and department. I was thinking what will be the easiest option to display in that manner, only thing is I don’t want to write huge SQL query to show this data.  Nothing to worry now, you have an option in MS SQL that is nothing by grouping set.
Today we will see how we can use GROUPING SET in SQL query and when exactly we can use this query.
First we will create a table with the name tblStatistics.

Peek Definition VS Go To Definition in Visual Studio

In Visual Studio 2013 Microsoft added a very useful feature named peek definition. You will be amazed to see this feature because now you can change the code in another page without navigating away from the current screen you are in.
Suppose you are debugging in your code and you reached to a function where you find an error. Earlier you have to go to the .CS page where you function is defined and have to change the code. In those case we just right click over the function and use Go to Definition option to navigate to the exact function.

GROUP BY clause to get comma separated values in MS SQL

Today we will see how to get the comma separated values from SQL table. This may require during the project development to send the report or to send an email to multiple people.
 
We will create the table first.

CREATE TABLE [dbo].[StatusReport](

    [StudentID] [int] NULL,

    [Status] [nchar](10) NULL,

    [Email] [varchar](50) NULL

)

How to find Quarter Name and Month Name from the Datetime in MS SQL

Recently I was developing one Material Management System and got a requirement to get the details quarterly with the Month name in a report.
Since it is a simple query I thought I will share you he same as it may might be useful.
Lets create the table first.

create table Items

(

ItemName varchar(500),

uom char(200),

Quantity float(50),

Price numeric(5,2),

Bill_Date datetime

)

What is KUDU - Windows Azure Web Sites analysis tool

When I got to know about this KUDU I was thinking who named this KUDU! You also might be thinking about this. But here we are not going to discuss that…we will see what is the use of KUDU and how we can use this in our Azure website.
What is KUDU —> KUDU is a very user friendly troubleshooting and analysis tools for Windows Azure Web Sites.
Now we will see how to access KUDU console of your azure website. It is a very simple method.