Windows 8/8.1: Bring back IE 10/11 to Metro Style mode


IE

After using Windows 8.1 preview for few days I noticed that my Internet Explorer is not opening in metro style mode even though I am clicking on the tile placed on my start screen which was behaving normal, just before few days. If you are facing the same problem then this post will be really helpful to you.Although I mentioned about Windows 8.1 which means I have IE 11 on my PC but, this works also for IE 10 on Windows 8 PCs.

First of all this is not a bug or issue, in fact this is its normal behavior. Now, you may ask why is this happening? OK, answer is quite simple but confusing. Setting a browser as default other than Internet Explorer led to this situation. Now this statement made half of the answer to question “How to bring back IE 10/11 to metro style mode ?” Let’s see detailed steps to do this.
Step-1:
Open Internet Explorer and click on menu ‘Tools’ (if you are unable to view the menu bar, press ‘Alt’ key from your keyboard or you can use shortcut ‘Alt+T’ ) and then click ”Internet Options’. A window pops out with different tabs.

Step-2:ie102

Go to ‘Programs’ tab. Click on ‘Make Internet Explorer the default browser’ as shown in the figure.

Step-3: ie104You’ll be presented with ‘Set Default Programs’ window containing list of programs installed on your PC. Choose ‘Internet Explorer’ from the list and click on ‘Set this program as default’. Finally click ‘OK’.

That’s it !

Thanks for reading.

Update (24 July 2013): Corrected incorrect information that was present in the post. I deeply regret for the same.

SQL Server: Delete/Update duplicate records


Problem:

My third article in Problem-Solution approach to keep it short and simple.

When you insert two records with same values for each of the columns in a table and then if you want to delete one of those two records then you may face problems like if you open the table data in SSMS and try to delete one of those rows by right clicking then it will not work. If you try to delete with a simple delete query then both the duplicate records will be deleted. You will face the same problem while updating those records also. Lets examine it using a simple example.

Example:

CREATE TABLE Demo (Id INT,Name VARCHAR(20));

INSERT INTO Demo VALUES(1,'TEST1');
INSERT INTO Demo VALUES(2,'TEST2');
INSERT INTO Demo VALUES(3,'TEST3');
INSERT INTO Demo VALUES(3,'TEST3');

Now, try to delete/update one of the record having Id=3.

The query for Update may be like-


UPDATE Demo SET Name='3Test' WHERE Id=3

The query for Delete may be like-


DELETE Demo WHERE Id=3

Solution:

Solution for Update


SET ROWCOUNT 1
UPDATE Demo SET Name='3Test' WHERE Id=3
SET ROWCOUNT 0

Solution for Delete


SET ROWCOUNT 1
DELETE FROM Demo WHERE Id=3
SET ROWCOUNT 0

Analysis:

According to MSDNSET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned.  So, the above query will affect only one record and this is what we needed.

[+] ADDED LATER [+]
[Thanks Manas Ranjan Dash for bringing this to notice]
As the MSDN link says “Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server”, lets try some other alternative solutions.
Solution 2:
Using TOP –>

DELETE TOP (1) FROM Demo WHERE Id=3 AND Name='Test3'

This query will delete 1 record and you can change it as required.
Solution 3:
Using Common Table Expression & Ranking Functions –>

;WITH CTE(Id,Name,SlNo)
AS
(
	SELECT Id,Name,ROW_NUMBER() OVER(ORDER BY Id) AS SlNo
	FROM Demo
)
DELETE FROM CTE WHERE SlNo > 1 AND Id=3 AND Name='Test3'

The above script will leave only a single record with Id=3 and Name=’Test3′.

Hopefully this article may be useful to you. Please put your comments and suggestions.

Thanks !!!

SQL Server: Stored Procedure for update with optional parameters


Problem:

One more article in Problem-Solution approach to keep it short and simple.

When we are writing a stored procedure to update different fields of a table for one or more records, we may write as the below script.

CREATE PROCEDURE [dbo].[UpdtEmployee]
(
    @EmpId INT,
    @EmpName VARCHAR(100)=NULL,
    @Address VARCHAR(200)=NULL,
    @EmpPhotoPath VARCHAR(100)=NULL,
    @MobileNo VARCHAR(14)=NULL
)
AS
BEGIN
    UPDATE dbo.Employee
    SET EmpName=@EmpName,
        Address=@Address,
        EmpPhotoPath=@EmpPhotoPath,
        MobileNo=@MobileNo
    WHERE EmpId=@EmpId
END

The problem with the above stored procedure is when we want to update only those fields/columns of the record for which corresponding parameter is not having NULL values then it will not yield the anticipated result. In this scenario this stored procedure will update all the fields with the values from corresponding parameters. Let’s take an example so that the problem can be understood clearly.

Example: Let’s assume that we have inserted values to all the fields of a record. Now we want to modify only the mobile number of that employee. If we call this procedure then it will make values of all other fields to NULL as we have declared the default value for all the parameters as NULL.

Solution:

Solution to this problem is to avoid updating those fields which have corresponding parameters with NULL values or, we can update existing data corresponding to those fields. We will go for the later one. Now, the modified script may look like


CREATE PROCEDURE [dbo].[UpdtEmployee]
(
    @EmpId INT,
    @EmpName VARCHAR(100)=NULL,
    @Address VARCHAR(200)=NULL,
    @EmpPhotoPath VARCHAR(100)=NULL,
    @MobileNo VARCHAR(14)=NULL
)
AS
BEGIN
    UPDATE dbo.Employee
    SET EmpName=ISNULL(@EmpName,EmpName),
        Address=ISNULL(@Address,Address),
        EmpPhotoPath=ISNULL(@EmpPhotoPath,EmpPhotoPath),
        MobileNo=ISNULL(@MobileNo,MobileNo)
    WHERE EmpId=@EmpId
END

Hopefully this article may be useful to you.

Thanks !!!

SQL Server: An optimal way to create procedure with multiple optional parameters


Problem:

I took the Problem-Solution approach to write this post to keep it short and simple.

Here the problem I am talking about is the scenario when you have to write a stored procedure in SQL Server with multiple search options/parameters then you may use some IF…ELSE .. statements to do it. Let’s take an short example.

Example:


CREATE PROCEDURE [dbo].[getEmployeeList]
(
   @DeptId INT=NULL,
   @OfficeId INT=NULL
)
AS
BEGIN
    IF(@DeptId IS NOT NULL AND @OfficeId IS NOT NULL)
        BEGIN
            SELECT *
            FROM dbo.Employees
            WHERE DeptId=@DeptId AND OfficeId=@OfficeId
        END
    ELSE IF(@DeptId IS NOT NULL AND @OfficeId IS NULL)
        BEGIN
            SELECT *
            FROM dbo.Employees
            WHERE DeptId=@DeptId
        END
    ELSE IF(@DeptId IS NULL AND @OfficeId IS NOT NULL)
        BEGIN
            SELECT *
            FROM dbo.Employees
            WHERE OfficeId=@OfficeId
         END
    ELSE
        BEGIN
            SELECT *
            FROM dbo.Employees
        END
END

Here we can have 4 combinations of search options.

Now, if we want add some more parameters like SectionId,DesignationId,CityId etc. to this stored procedure then it will be a tough task to manage it and may end-up with a 100’s lines of script.

Solution:

I found a solution for this problem by creating multiple conditions for a single WHERE… tag. Now, if I implement it to the previous example then the stored procedure will be like the below script-


CREATE PROCEDURE [dbo].[getEmployeeList]
(
    @DeptId INT=NULL,
    @OfficeId INT=NULL
)
AS
BEGIN
   SELECT *
   FROM dbo.Employees
   WHERE (@DeptId IS NULL OR DeptId=@DeptId)
   AND (@OfficeId IS NULL OR OfficeId=@OfficeId)
END

Just add conditions like these two to the WHERE part, as many as optional parameters you have in your stored procedure.

I hope it will be helpful to those who are looking a solution for the above said problem. Please share your thoughts and ideas regarding this post.

Thank you.