• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 281
  • Last Modified:

Link server

I have to update table from database which is on server A to other database which is on server B.
I have to use Link server for the same.
Can anyone explain me with details how to do the same using SQL server studio, as my some link servers are working and some are not.
0
RETAILREALM
Asked:
RETAILREALM
1 Solution
 
Alpesh PatelAssistant ConsultantCommented:
select * from NI21.Northwind.dbo.Products
0
 
JaseemKCommented:
-- I'm assuming you're using SQL 2008...
-- better to use T-SQL than the Management Studio.
-- On server B, list all servers defined on this local server:
sp_helpserver
go

-- if your server is listed but isn't working, drop and recreate it:
sp_dropserver
go

-- Now re-add it:
sp_addlinkedserver 'A', 'SQL Server'
go
sp_addlinkedsrvlogin 'A', 'false', NULL, 'sa', 'the_sa_password_here'
go

-- Now you can update the local table from the remote table:
UPDATE tblB
SET tblB.ColXYZ = a.ColABC
   FROM A.DBName.dbo.tblA a
   WHERE tblB.ColBCD = a.ColDEF
go
-- etc... Remember to qualify the remote table fully as above: ServerName.DatabaseName.OwnerName.TableName

HTH.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now