January 27, 2011 at 11:54 am
I have table a, b in serverAB and table c, d in ServerCD.
Columns in table a are a.key and a.date (serverAB)
Columns in table b are b.id (serverAB)
Columns in table c are c.id (serverCD)
Columns in table d are d.id and d.date (serverCD)
What I need to do is replace b.id(serverAB) with a.key(serverAB) values. I need to do lookup in d table (serverCD) based on b.id = d.id, then get the d.date. With d.date, I need to do another lookup in table a (ServerAB), based on d.date = a.date, I need to get the a.key to update b.id = a.key.
Can someone point me how I can do this type of update in MS SQL Server 2008 where lookup and update are in two different servers?
January 27, 2011 at 12:26 pm
On ServerAB, configure a linked server to ServerCD. Then once your linked server is configured properly you just write your query on ServerAB, when you reference the data on ServerCD you use a 4 part naming convention [LinkedServerCDName].[DatabaseName].[dbo].[tablename] in your Join to get the data. Other than that you write your query as though everything was local
January 27, 2011 at 12:38 pm
How do you check if the servers are setup as linkedServers?
January 27, 2011 at 12:46 pm
select * from master.sys.servers where is_linked = 1
will give you a list of the linked servers currently on your server.
January 27, 2011 at 12:58 pm
Can someone provide a SQL Server syntax to do this kind of update? Please really urgent.
I can't link the server. Can I use openrowset function? Helpful if someone can provide a syntax or alternative solution.
January 27, 2011 at 1:18 pm
If I am understanding your request correctly, if you were running this from server AB you would run something like
update b
set b.id = a.key
from dbnamea.dbo.b b
join OPENROWSET('SQLNCLI', 'Server=servercd;Trusted_Connection=yes;', 'SELECT id, date from dbnameb..d') d on b.id = d.id
join dbname.dbo.a a on d.date = a.date
I don't see your database names anywhere so I made some up, but hopefully this will give you the idea of the syntax anyway.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply