System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances

I. When I try to run ASP.net application with SQL Server database an error shown as below:

Server Error in '/' Application.
--------------------------------------------------------------------------------
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.

Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.  

II. When I try to connect to a SQL Server database from visual studio server explorer an error shown as below:  

---------------------------

Microsoft Visual Studio
---------------------------
Generating user instances in SQL Server is disabled. Use sp_configure 'user instances enabled' to generate user instances.
---------------------------
OK
---------------------------

Solution
This error is due to SQL Server Express has disabled user instance genereation. Simply apply the below steps to enable user instance generation.

1. Open SQL Server 2005 Management Studio Express.

2. Connect to the default instance of that server (eg: Computer Name\SQLEXPRESS)

3. Open a new query window and use master database

4. Execute sp_configure as below:
exec sp_configure 'user instances enabled', 1

--Configuration option 'user instances enabled' changed from 0 to 1. Run the RECONFIGURE statement to install.

5. Execute RECONFIGURE as:
RECONFIGURE

--Command(s) completed successfully.

6. Go to services and restart the SQL Server Instance currently running.

Note: Sample connection string format in web.config should be as below :

connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=

|DataDirectory|\DBName.mdf;Integrated Security=True;User Instance=True;Initial Catalog=DBName;"

User Instances
User instance is a new feature supported by Microsoft SQL Server 2005 Express Edition, which is only available when using the .NET Framework Data Provider for SQL Server (SqlClient). A user instance is a separate instance of the SQL Server Express Database Engine that is generated by a parent instance. User instances allow users who are not administrators on their local computers to attach and connect to SQL Server Express databases. To generate user instances, a parent instance of SQL Server Express must be running. User instances are enabled by default when SQL Server Express is installed, and they can be explicitly enabled or disabled by a system administrator executing the sp_configure system stored procedure on the parent instance.

-- Enable user instances.
sp_configure 'user instances enabled', '1'

-- Disable user instances.
sp_configure 'user instances enabled', '0'

Last N rows from SQL Server Table

Retrieving last 5 rows from a table

SELECT TOP (SELECT Count(*) FROM Person) * FROM Person
Except
SELECT TOP (SELECT Count(*)-5 FROM Person) * FROM person

OR

SELECT TOP (SELECT Count(*) FROM Person) * FROM Person
WHERE PersonName NOT IN(
SELECT TOP (SELECT Count(*)- 5 FROM Person) PersonName FROM person
)

Types of Join in SQL Server

Join
By using joins, we can retrieve data from two or more tables based on logical relationships between the tables. Joins indicate how SQL Server should use data from one table to select the rows in another table. Sql server joins are used to combine result data from two or more tables.
There are three types of sql server joins.
1. Inner Join
2. Outer Join
3. Cross Joins

1. Inner Join
Inner joins use a comparison operator to match rows from two tables based on the values in common columns from each table. Inner joins return rows only when there is at least one row from both tables that matches the join condition. Eg:Retrieving all rows where the student identification number is the same in both the students and courses tables.

2. Outer join
Inner joins eliminate the rows that do not match with a row from the other table. Outer joins, however, return all rows from at least one of the tables or views mentioned in the FROM clause.
There are there 3 types of outer joins.

a. Left outer join
All rows are retrieved from the left table referenced with a left outer join. If matching records are found then it will display data of right table with left table data otherwise put a null values instead of right table data.

b.Right outer join
All rows are retrieved from the right table referenced in a right outer join. If matching records are found then it will display data of left table with right table data otherwise put a null value instead of left table data.

c.Full outer join
All rows from both tables are returned in a full outer join.This joins are combination of both left outer join and right outer join.

3. Cross join
A cross join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Self-join
A table can be joined to itself in a self-join.

Find the second largest salary in the Employee table

SQL Statement To find the second largest salary from the Employee table

Finding result in 4 ways :

SELECT TOP 1 Salary FROM
(SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) Temp
ORDER BY Salary ASC

SELECT TOP 1 Salary FROM Employee
WHERE Salary NOT IN (SELECT TOP 1 Salary FROM Employee ORDER BY Salary DESC)ORDER BY Salary DESC

SELECT Salary FROM Employee A
WHERE 2 = (SELECT count(*) FROM Employee B WHERE A.Salary <= B.Salary)

SELECT MAX(Salary) FROM Employee
WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee)

Primary key and Unique key difference

Primary key and unique are Entity integrity constraints.

Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist and no null values are entered.

Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Difference
1. Unique key can be null, but Primariy key can't be null.
2. Primariy key can be refrenced to other table as FK.
3. We can have multiple Unique key in a table, but Primariy key is one and only one.
4. Primariy key itself is a unique key.

Difference between DELETE and TRUNCATE

1. DELETE is a DML command. TRUNCATE is a DDL command.

2. After DELETE we can rollback the records. After TRUNCATE we cannot rollback the records.

3. We can use WHERE Clause with DELETE Command. We can't use WHERE Clause with TRUNCATE command. TRUNCATE delete all rows from the table.

4. After using DELETE Command the memory occupied not released untill the user gives COMMIT. After using TRUNCATE Command the memory realeased immediately.

5. TRUNCATE do not check constraints. DELETE checks constraints.
6. TRUNCATE is faster than DELETE and uses Less transaction log space. DELETE removes rows one at a time and records each deleted row in the transaction log. TRUNCATE TABLE removes the data by deallocating all data pages used to store the table data and records only the page deallocations in the transaction log.

7. DELETE can activate trigger. TRUNCATE cannot activate a trigger because the operation does not log individual row deletions.

8. TRUNCATE reset the counter used by an identity column. DELETE retain the identity counter.

9. TRUNCATE cannot be used with tables referenced by foreign keys.

Difference between UNION and UNION ALL

The UNION command is used to select related information from two or more tables, like the JOIN statement. Union combines the result sets of two or more queries into a single result set, which includes all rows that belong to all queries in the union. To use Union, the number and the order of the columns and data type of the columns selected must be the same in all queries.

 

Difference
The UNION command only selects distinct values.
The UNION ALL command is equal to the UNION command, but UNION ALL selects all alues.

Stored Procedure and User-Defined function, Difference

Stored Procedure
A stored procedure is a set of Transact-SQL statements that has been compiled into a single execution plan and stored in the database. We can create applications that execute the stored procedures and process the results.
Stored procedures return data in four ways:
1. Output parameters, which can return either data or a cursor variable.
2. Return codes, which are always an integer value.
3. A result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure.
4. A global cursor that can be referenced outside the stored procedure.

User-Defined Functions
Functions are subroutines made up of one or more Transact-SQL statements that can be used to encapsulate code for reuse. A user-defined function takes zero or more input parameters and returns either a scalar value or a table.

Difference Between Stored procedure and User-Defined Function
1. A Function return rowsets, tables, VARCHARs, INTs, and other data types. While a Stored procedure can only return an INT value.
2. Function always return a single value to the caller, while
Stored procedures do not.
3. When Transact-SQL encounters an error the function stops, while Transact-SQL will ignore an error in a Stored procedure and proceed to the next statement(provided Err handling).
4. A Stored procedure can be used in an XML FOR clause, a Function can't be.
5. Result of the function can be used within a SQL staement. A result set or retun value of the stored procedurecannot be used within a SQL statement.

Dynamically number or rank rows in SQL Server using SELECT statement

By using a SELECT statement we can dynamically number or rank rows. This method is the only possible solution and which is faster than the procedural solution. Row numbering or ranking is a typical procedural issue. Typical solutions are based on loops, cursors and temporary tables. But this technique is based on an auto join. The chosen relationship is typically "is greater than.". Count how many times each element of a particular set of data fulfills the relationship "is greater than" when the set is compared to itself.

In SQL Server 2000

SELECT SerialNo = count(*), A.FirstName FROM Employee A, Employee B
WHERE A.FirstName >= B.FirstName
GROUP BY A.FirstName
ORDER BY SerialNo

Result :SerialNo FirstName
1 Abhilash
2 Aby
3 Admin
4 Anand
5 Aneesh
6 Arun
7 Justin
8 Neethu
9 Roshin
10 Suresh

In the above sample, 'FirstName' column of 'Employee' table is selected with a dynamic number. Here the relationship used is 'greater than or equal to'. The technique is counting the number of times the 'FirstName' of 'A' is greater than or equal to the 'FirstName' in 'B'. If duplicte exist in 'FirstName' use relationship 'A.FirstName + A.LastName >= B.FirstName + B.LastName'

 

In SQL Server 2005
Use ranking functions that are provided as a new feature in SQL Server 2005. Ranking functions return a ranking value for each row in a partition. Depending on the Rank function used, some rows might receive the same value as other rows.
Transact-SQL provides the following ranking functions:

• RANK
• DENSE_RANK
• ROW_NUMBER
• NTILE

SELECT RANK() OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Employee A ORDER BY SerialNo

SELECT DENSE_RANK() OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Employee A ORDER BY SerialNo

 
SELECT ROW_NUMBER() OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Employee A ORDER BY SerialNo

SELECT NTILE(10) OVER (ORDER BY A.Firstname) AS SerialNo, A.Firstname FROM Users A ORDER BY SerialNo

Here Argument to NTITLE is a positive integer constant that specifies the number of buckets into which each partition must be divided.

Change password for Login in SQL Server

Use system stored procedure 'sp_password' to change password. This procedure is used to add or modify a password for a Microsoft Sql Server login. Take a new query window and execute this procedure.Syntax :
sp_password 'Old Password','new password','login name'

Sample :
1. If login is currently with a blank password you can add a password for it. Adding a password for login 'sa'.
sp_password NULL,'Sql2005','sa'2. If login is having a password already then you can change password. Changing password for login 'sa'.
sp_password 'Sql2005','amthlk','sa'
Errors
Possible errors you can see when you try to change password are :-
1. Password validation failed. The password does not meet policy requirements because it is not complex enough.2. Password validation failed. The password does not meet policy requirements because it is too short.
When you create a SQL Server login, the server will validate the password against the password policy of the local machine. To view or modify the password policy on your lacal machine, take 'Administrative Tools' in 'Control Panel'. You can see 'Local Security Policy' in 'Administrative Tools'. Open this by clicking. You can see 'Password Policy' under 'Account Policy'.
Changing password policy or using a password that meet the password policy will clear error.

You can Use CHECK_POLICY option to disable password policy validation. Use ALTER LOGIN to configure policy application.

The following SQL statement will change password of login to a new password without knowing old password. It requires ALTER ANY LOGIN permission to execute.

ALTER LOGIN sa WITH PASSWORD = 'sql2005', CHECK_POLICY = OFF

Changing login password from old password to new one.
ALTER LOGIN sa WITH PASSWORD = 'amthlk' OLD_PASSWORD = 'sql2005';

To retrieve data stored in varbinary data type column

Varbinary data type can store Variable-length binary data with maximum size of 8,000 bytes. The storage size is the actual length of the data entered + 2 bytes. Column of this type can be used to store data such as images, sounds, video, Office documents, compressed data and other non-alphanumeric data.

Sample:To get password stored in SQL server table.
Table 'Users' have a field 'Password' with data type 'varbinary(50)'.
Run the following query to get password from the 'Password' field of the table 'Users' .

SELECT UserID, FirstName, LoginName, Convert(nvarchar(50),Password) FROM Users