Monday, July 11, 2011

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.

 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')
    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'
    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''')
    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' ;



No comments:

Post a Comment