Linked server in Ms SQL

Run below queries to add linked server

EXEC master.dbo.sp_addlinkedserver
@server = 'ServerName',
@provider = 'ProviderName',
@datasrc = 'DataSource',
@srvproduct=''
Example::
EXEC master.dbo.sp_addlinkedserver
@server = 'A-2011',
@provider = 'MSDASQL',
@datasrc = 'SERVER=A-2011;UID=sa;PWD=sa;',
@srvproduct=''
-----------------------------------------
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='ServerName',
@useself='False',
@locallogin=NULL,
@rmtuser='UserName',
@rmtpassword='Password'
Example::
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname='A-2011',
@useself='False',
@locallogin=NULL,
@rmtuser='sa',
@rmtpassword='sa'


sp_addlinkedserver is used to create a linked server.
sp_addlinkedsrvlogin is used to create login on the linked server.

Check in MS SQL............Validation

Check constraint..................
Create Table Books
(
BookID varchar(8) primary key
check (BookID LIKE 'B[0-9][0-9][0-9][0-9][0-9]'),
Title varchar(50) NOT NULL,
Author varchar(50) NOT NULL,
Publisher varchar(50) NULL,
Category varchar(10) NOT NULL check (Category in ('Tech','Busi','Arts')),
Price int NOT NULL CHECK (Price >=0),
NoBookInHand int NOT NULL check (NoBookInHand>=0)
);

In this table you can enter data where the following must be satisfied:-
BookID must be first character B and Rest five are number.
Category must in ('Tech','Busi','Arts')
Price is not negative
NoBookInHand is not negative.

Add Check Constraint:
ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (Condition......)

Drop Check Constraint:
ALTER TABLE TableName
DROP CONSTRAINT ConstraintName

Combine Multiple Records Comma Separated In One Column MSSQL 2005

In this example i am going to describe how to combine multiple records in a column in MS SQL into one record comma separated.

Here is the scenario

I have a table having Employees names and their respective Department names, now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below



And this is Data into table



I want output in following format
Department---------------FirstName
IT----------------------amiT,Emp1,Emp5
Admin-------------------Shobhit, Emp3,Emp7
and so on

To get this desired result we need to write below mentioned query
SELECT DISTINCT Department,
EmpNames = substring( ( SELECT ', ' + FirstName
FROM Employees e2
WHERE e2.Department = e1.Department FOR XML path(''), elements),2,500)
FROM Employees e1

And the output of this SQL Query would be