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.
RETAILREALMAsked:
Who is Participating?
 
JaseemKConnect With a Mentor Commented:
-- 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
 
Alpesh PatelAssistant ConsultantCommented:
select * from NI21.Northwind.dbo.Products
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.