CASCADE in SQL Server (Foreign & Primary Key Constraints)

We have several tables in an SQL database referencing each other with foreign key constraints. If we need to delete a row in one of these tables as well as all other rows that reference it, then we have two options. The first option is to execute an explicit DELETE statement for each table that contains referencing rows and then finish by deleting the referenced row (this order is important if we don’t want to violate any foreign key constraints). The other option is to declare our foreign keys as ON DELETE CASCADE. This clause tells the database to automatically delete the referencing row if the row it references is deleted.

Select * from dbo.ProductDetails
Select * from dbo.Products

Suspect Query for MS SQL 2005

How To Repair A Suspect Database In MSSQL 2005:

Issue
You have a database in MSSQL that is tagged as (suspect) and you are unable to connect to the database.

Possible Causes
The database could have become corrupted.
There is not enough space available for the SQL Server to recover the database during startup.
The database cannot be opened due to inaccessible files or insufficient memory or disk space.
The database files are being held by operating system, third party backup software etc.
There was an unexpected SQL Server Shutdown, power failure or a hardware failure.

Get Client IP Address in SQL Server..

Execute the below function and call this function through a Stored Procedure. It will return the IP address of the client system.

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
    DECLARE @IP_Address varchar(255);

    SELECT @IP_Address = client_net_address
    FROM sys.dm_exec_connections
    WHERE Session_id = @@SPID;

    Return @IP_Address;
END


Source

What is NAS, SAN, DAS ?

NAS stands for Network Attached Storage. It differs from traditional, directly attached storage in that, in NAS, the operating system and other software on the NAS product are dedicated solely to data storage.

SAN stands for Storage Area Network. A SAN is a network designed to attach storage hardware and software to servers. SANs generally come in two forms: as a network primarily dedicated to transferring data between computer systems and storage systems, or as a complete system that includes all of the storage elements and computer systems within the same network.

DAS stands for Directly Attached Storage. DAS is generally used to differentiate between storage systems directly attached to a server or workstation and NAS and SAN setups.


Source

How to fix orphaned SQL Server users?

The problem is that the user in the database is an "orphan". This means that there is no login id or password associated with the user. This is true even if there is a login id that matches the user, since there is a GUID (called a SID in Microsoft-speak) that has to match as well.

This used to be a pain to fix, but currently (SQL Server 2000, SP3) there is a stored procedure that does the heavy lifting.

All of these instructions should be done as a database admin, with the restored database selected.

First, make sure that this is the problem. This will lists the orphaned users:

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'


Source : SQL BLOG

Create and Configure Virtual Directories in IIS 7.0

To create a virtual directory by using IIS Manager

1. In IIS Manager, expand the local computer and the Sites folder, and then find the Web site that you want to add a virtual directory for. Use the following steps:

o For , right-click the site or folder where you want to create the virtual directory, and then click Add Virtual Directory.

o For Windows Server 2008, right-click the site or folder where you want to create the virtual directory, click Manage Web Site, and then click Add Virtual Directory.

2. In the Add Virtual Directory dialog box, specify the following information:

o Alias. Type a name for the virtual directory. Choose a short name that is easy to type, because the user types this name to access the Web site.

o Physical Path. Type or browse to the physical directory that contains the virtual directory. You can select an existing folder or create a new one to contain the content for the virtual directory. 

3. To provide credentials to connect to a UNC path, click the Connect as button.

4. Click OK.

List of Run Commands in Windows 7 & 8

TO DOWNLOAD THE COMMANDS CLICK THE LINK - DOWNLOAD