March 9, 2009 at 9:28 am
hey all
guys i created a linked server connection to a remote server ...the reason why i did this is that i want to create a shadow linked table...they are linked through primary key and every record added there the primary key of it should be added to my table...how should i do that?? how can i link my table to the key of the remote table please help
March 9, 2009 at 11:35 am
I didn't understand completely your question, but it seems to me you are trying to update a remote table on every update to the local table, using the primary key as condition.
This can be simply achieved with a triggers on the local table.
Is this what you're trying to do?
-- Gianluca Sartori
March 9, 2009 at 1:52 pm
its the oposite when the remote table is updated a record or deleted...i want that to be reflected on my table...if a record is deleted..deletion should occur..i thought about it and i can do a vbcode in my application that can do that ...is there any other way?? and if there is no other way there is no reason for the linked server
March 10, 2009 at 2:04 am
Ifyou have to update the local table (let's say table A) according to the changes in the remote table (table B), maybe you should do the opposite: link the server holding table A in the server holding table B and put a trigger to reflect the changes through the linked server.
If you don't need to do anything else in the link A-->B, maybe you don't need the linked server.
-- Gianluca Sartori
March 10, 2009 at 2:14 am
this can be an issue because i am not authorized to do anything with the remote database[:s] yesterday i was thinking about it ... maybe i can a loop in my application that checks item by item in the remote table and the local table...if exists a item found on my table not found on the remote table then i should add it with the default values...if the opposite occurs then i simply delete the record the only use of the linked server is that it makes querying possible...or do you think else wise?.... the main reason i am reluctant to do that because i think it will slow down my application
March 10, 2009 at 2:21 am
For sure checking the two tables for differences will slow down your application, especially if a remote table is involved. You could set up a SSIS package to propagate the changes from the remote server and schedule the execution every 5 minutes or less, depending on the time taken and on the locks in the application accessing the local table.
-- Gianluca Sartori
March 10, 2009 at 2:26 am
ok i didn't mention this ..i am a newbie in sql server i never heard of SSIS i am going to research it now ...do u think its going to help...thanks for your time
March 10, 2009 at 2:31 am
You'll find many resources on SSIS (Sql Server Integration Services) on this site as well. I think this could be a productive approach, but I don't know exactly your needs ad restrictions, so don't exclude other possibile solutions.
Regards
Gianluca
-- Gianluca Sartori
March 10, 2009 at 2:47 am
again thanks for your help Mr.Gianluca i am going to research it and see if it fits my needs
March 10, 2009 at 11:40 am
How many rows are in the tables? If it is too many, you may need to figure out a different approach -
But you may want to try this:
INSERT INTO LocalTable(...)
SELECT ...
FROM [LinkedServer].DB.dbo.RemoteTable rt
LEFT JOIN dbo.LocalTable lt ON
rt.PrimaryKey = lt.PrimaryKey
WHERE lt.PrimaryKey IS NULL
DELETE lt
FROM dbo.LocalTable lt ON
LEFT JOIN [LinkedServer].DB.dbo.RemoteTable rt
rt.PrimaryKey = lt.PrimaryKey
WHERE rt.PrimaryKey IS NULL
UPDATE lt SET
...
FROM dbo.LocalTable lt ON
INNER JOIN [LinkedServer].DB.dbo.RemoteTable rt
rt.PrimaryKey = lt.PrimaryKey
WHERE ...
HTH.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply