How to Query Linked Server in SQL Server --- CRUD operation on LinkedServer

In this post I will show how to perform CRUD opertions on linked server in sql server

For this i have created a simpel DB named "TestDB" which contains one table "Employee" and 2 columns (ID (int, pk), name varchar(1000)

First need to add link server where the above database is created . You can create link server as mentioned in msdn.
http://msdn.microsoft.com/en-us/library/ff772782.aspx

 Now I will explain CRUD Opertion one bye one

• Create / Insert
  there are two ways to do
  INSERT INTO [otherPC\SQLInstace2008].TestDB.dbo.Employee(Name) VALUES( 'hafiz')
  or
  insert openquery ([otherPC\SQLInstace2008], 'select [name] from TestDB.dbo.Employee')VALUES('suleman')


• Retervie / SelectSELECT *  FROM [otherPC\SQLInstace2008].TestDB.dbo.Employee
  SELECT *FROM OPENQUERY([otherPC\SQLInstace2008], 'SELECT * FROM  TestDB..Employee')
  SELECT *FROM OPENQUERY([otherPC\SQLInstace2008], 'SELECT * FROM  TestDB.dbo.Employee')


• Update
  update openquery ([otherPC\SQLInstace2008], 'SELECT * FROM  TestDB.dbo.Employee where name = ''suleman'' ') SET name = 'hafiz suleman'
  OR
  UPDATE [otherPC\SQLInstace2008].TestDB.dbo.Employee SET name = 'muhammad suleman' WHERE name = 'hafiz'


• Delete
  delete openquery ([otherPC\SQLInstace2008], 'select * from TestDB.dbo.Employee where name = ''muhammad suleman''')
  OR
  DELETE FROM [otherPC\SQLInstace2008].TestDB.dbo.Employee WHERE name='hafiz suleman'

If you found any error like this

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "SERVERNAME.REDMOND.CORP.MICROSOFT.COM" was unable to begin a distributed transaction.

then use followign statement to fix the issue.

EXEC sp_serveroption @server = 'YourServerName',@optname = 'remote proc transaction promotion', @optvalue = 'false' ;